asked on How do I use a variable in ALTER TABLE statement
I need to ADD a FIELD to a table using ALTER TABLE ADD COLUMN. I need to use a variable for the Field Name (COLUMN).
I have code that checks if a FIELD exists (this works). If the FIELD does NOT exist I need to create it. My code has assigned a name to a variable (sFieldName).
I assign "Task10Assigned" to sFieldName (this name changes often). I searched my table and find that this field name does NOT exist so I try to create the FIELD using:
Dim sFieldName as String
sFieldName="Task10Assigned
myDB.Execute ('ALTER TABLE tblHardwareInfoMerged ADD COLUMN ['" & sFieldName & "'] TEXT(25)') but it says OBJECT REQUIRED.
IF I hard code a column name instead of the variable it works!!
myDB.Execute ("ALTER TABLE tblHardwareInfoMerged ADD COLUMN [Task10Assigned] TEXT(25)")
What am I doing wrong?
I have code that checks if a FIELD exists (this works). If the FIELD does NOT exist I need to create it. My code has assigned a name to a variable (sFieldName).
I assign "Task10Assigned" to sFieldName (this name changes often). I searched my table and find that this field name does NOT exist so I try to create the FIELD using:
Dim sFieldName as String
sFieldName="Task10Assigned
myDB.Execute ('ALTER TABLE tblHardwareInfoMerged ADD COLUMN ['" & sFieldName & "'] TEXT(25)') but it says OBJECT REQUIRED.
IF I hard code a column name instead of the variable it works!!
myDB.Execute ("ALTER TABLE tblHardwareInfoMerged ADD COLUMN [Task10Assigned] TEXT(25)")
What am I doing wrong?
Microsoft AccessVBA
Last Comment
juricta
Pawan's suggested solution may work but you really need to think through how you should handle your data properly.
just thinking if you got different Hardware Info which probably has different number of Task Assigned. How should you handle that? In short, I think you should consider to normalise your tables for better data handling.
frequently altering your table is just not a good idea for me.
just thinking if you got different Hardware Info which probably has different number of Task Assigned. How should you handle that? In short, I think you should consider to normalise your tables for better data handling.
frequently altering your table is just not a good idea for me.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
PS I agree with Ryan about the table/database design.
ASKER
I have to give full points to Norie. Your code worked with not issues first time. When I ran Pawan's code I got the same error I initially hade (Object required). Thank you all for the help
You are dynamic column name
. For that we need dynamic sql. Pls try above.