ALTER TABLE with local variable

[ineedahelp]

Can anyone help me with the code for an ALTER TABLE commmand? I have a list box that lists all tables in database. The user chooses. I next want to add a column to this table. I might also want to delete a column from this table. what I have for code so far is:

Private Sub lstTableList_Af terUpdate()
Dim tblClientTable As String
tblClientTable = Forms!frmDailyC lient!lstTableL ist
Debug.Print tblClientTable
mAnswer = MsgBox("Add a field for LS Data?", vbYesNo)
If mAnswer = 6 Then
ALTER TABLE " & tblClientTable & " ADD COLUMN LSRate Long;"
End If
Me!lstFieldList .Visible = True
Me!lstFieldList .SetFocus
Me!lstTableList .Visible = False
End Sub

I am guessing that it doesn't like my variable. Is it because I have dim it as a string? Do I need to set is as "currentdb" ? Thanks again for any help!!

[NeoPa]

If mAnswer = 6 Then
ALTER TABLE " & tblClientTable & " ADD COLUMN LSRate Long;"
End If

The Dim is OK but you can't execute SQL code as if it were VB as in line quoted above.

[ineedahelp]

Can you tell me what I need to do to make this work? Thank you in advance!

[NeoPa]

Not really - I don't have much experience amending table definitions via SQL.
I can say that to execute SQL code you can use :-

DoCmd.RunSQL(SQLStatement:=???, UseTransaction:=???)

I only ever use direct SQL for SELECT, UNION and Pass-Through queries.

[MMcCarthy]

You have to create a TableDef variable

 
Private Sub lstTableList_AfterUpdate()
Dim db As Database 
Dim tbl As TableDef 
Dim fld As Field 
Dim tblClientTable As String
 
	tblClientTable = Forms!frmDailyClient!lstTableList
	Debug.Print tblClientTable
	mAnswer = MsgBox("Add a field for LS Data?", vbYesNo)
	If mAnswer = 6 Then
 
		' Start by opening the database 
		Set db = CurrentDb() 
		' Create a tabledef object 
		Set tbl = db.TableDefs(tblClientTable)
 
		' Create field; set its properties; add it to the tabledef 
		Set fld = tbl.CreateField("LSRate", dbLong) 
		tbl.Fields.Append fld 
 
	End If
 
	Me!lstFieldList.Visible = True
	Me!lstFieldList.SetFocus
	Me!lstTableList.Visible = False
 
	fld.close
	set fld = nothing
	tbl.close
	set tbl = nothing
	set db = nothing
 
End Sub