Link to home
Start Free TrialLog in
Avatar of juricta
jurictaFlag for United States of America

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
        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?
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

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.
Avatar of Ryan Chong
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.
Avatar of Norie

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Norie

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


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