473,326 Members | 2,012 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

ALTER TABLE with local variable

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
4 4664
NeoPa
32,556 Expert Mod 16PB
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
Can you tell me what I need to do to make this work? Thank you in advance!
Oct 25 '06 #3
NeoPa
32,556 Expert Mod 16PB
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
14,534 Expert Mod 8TB
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

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

Similar topics

2
by: Dylan Nicholson | last post by:
Seems that Oracle 9.2 (using MS ODBC driver) requires extra parentheses when adding multiple columns to a table: ALTER TABLE MyTable ADD (MyColumn1 VARCHAR(255), MyColumn2 VARCHAR(255)) vs ...
1
by: Stephen Miller | last post by:
Is it possible to ALTER a temporary table in TSQL (SQL2000)? The following TSQL reports a syntax error at the ALTER TABLE line: DECLARE @Test TABLE( NOT NULL , NOT NULL ) ALTER TABLE...
3
by: Paul Sampson | last post by:
Hi, I'm trying to run the ALTER TABLE command using a dynamic string for the table, like so: DECLARE @TableName CHAR SET @TableName = 'Customers' ALTER TABLE @TableName ADD ...blah
2
by: Kevin Haugen | last post by:
I need to create a script to disable all triggers and constraints in my database. It appears as though I cannot use a local variable for the table name in the ALTER TABLE statement (e.g. ALTER...
3
by: Klemens | last post by:
Hi, I get an implicit -101 on an alter table statement on changing the length of one column ALTER TABLE XTRADE.LIEFERANT ALTER COLUMN ZENTRALE_NUMMER SET DATA TYPE VARCHAR(13) DB21034E The...
10
by: BuddhaBuddy | last post by:
Platform is DB2/NT 7.2.9 The table was created like this: CREATE TABLE MYTEST ( MYTESTOID bigint not null primary key, FK_OTHEROID bigint not null references other, FK_ANOTHEROID bigint not...
3
by: Kimi | last post by:
Hi, I'm trying to change a column on a DB2 table so that it has a default value but I cannot get it to work. Does anyone have any experience in this area. I'm trying with this statement: ...
2
by: RamaKrishna Narla | last post by:
In MS SQL Server, I have the following tables with some data in it. create table table1 ( column1 varchar(32), column2 int not null, column10 varchar(255), ..... primary key (column1,...
1
by: islandtalker | last post by:
Hi, In a stored procedure (MS SQL) I want to update a table to Add columns to it, and then update those new columns with data from a variable. The problem is that, after updating the table I...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.