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?
jurictaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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.
NorieAnalyst 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NorieAnalyst Assistant Commented:
PS I agree with Ryan about the table/database design.
jurictaAuthor 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.