How do I use a variable in ALTER TABLE statement

juricta
juricta used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
myDB.Execute ('exec ( ' ALTER TABLE tblHardwareInfoMerged ADD COLUMN ['" & sFieldName & "'] TEXT(25)')')

You are dynamic column name
. For that we need dynamic sql. Pls try above.
Ryan ChongSoftware Team Lead

Commented:
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.
Analyst Assistant
Commented:
You need to set myDB to reference the database you want the ALTER TABLE statement to run on.

Also, there should be no single quotes around the new field name.

Try this.
Set MYDB = CurrentDb

myDB.Execute "ALTER TABLE tblHardwareInfoMerged ADD COLUMN [" & sFieldName & "] TEXT(25)"

Open in new window

NorieAnalyst Assistant

Commented:
PS I agree with Ryan about the table/database design.

Author

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial