By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,806 Members | 1,465 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,806 IT Pros & Developers. It's quick & easy.

ALTER TABLE with local variable

P: 98
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_AfterUpdate()
Dim tblClientTable As String
tblClientTable = Forms!frmDailyClient!lstTableList
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!!
Oct 25 '06 #1
Share this Question
Share on Google+
4 Replies


NeoPa
Expert Mod 15k+
P: 31,489
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.
Oct 25 '06 #2

P: 98
Can you tell me what I need to do to make this work? Thank you in advance!
Oct 25 '06 #3

NeoPa
Expert Mod 15k+
P: 31,489
Not really - I don't have much experience amending table definitions via SQL.
I can say that to execute SQL code you can use :-
Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL(SQLStatement:=???, UseTransaction:=???)
I only ever use direct SQL for SELECT, UNION and Pass-Through queries.
Oct 25 '06 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
You have to create a TableDef variable

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub lstTableList_AfterUpdate()
  3. Dim db As Database 
  4. Dim tbl As TableDef 
  5. Dim fld As Field 
  6. Dim tblClientTable As String
  7.  
  8.     tblClientTable = Forms!frmDailyClient!lstTableList
  9.     Debug.Print tblClientTable
  10.     mAnswer = MsgBox("Add a field for LS Data?", vbYesNo)
  11.     If mAnswer = 6 Then
  12.  
  13.         ' Start by opening the database 
  14.         Set db = CurrentDb() 
  15.         ' Create a tabledef object 
  16.         Set tbl = db.TableDefs(tblClientTable)
  17.  
  18.         ' Create field; set its properties; add it to the tabledef 
  19.         Set fld = tbl.CreateField("LSRate", dbLong) 
  20.         tbl.Fields.Append fld 
  21.  
  22.     End If
  23.  
  24.     Me!lstFieldList.Visible = True
  25.     Me!lstFieldList.SetFocus
  26.     Me!lstTableList.Visible = False
  27.  
  28.     fld.close
  29.     set fld = nothing
  30.     tbl.close
  31.     set tbl = nothing
  32.     set db = nothing
  33.  
  34. End Sub
  35.  
  36.  
Oct 26 '06 #5

Post your reply

Sign in to post your reply or Sign up for a free account.