We help IT Professionals succeed at work.

How do I use a variable in ALTER TABLE statement

juricta asked
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
        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?
Watch Question

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

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

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

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


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