ADD COLUMN using variable for column name

[cyork]

Hi,

I want to do the following:

dbs.execute "ALTER TABLE example " _
& "ADD COLUMN " & Variable & " TEXT;"

I get a syntax error. Any suggestions?

Thanks,

Chris

 

[RickSpr]

This looks valid for a Jet database, provided "Variable" contains a valid name. Is this, in fact, Jet? (If not, TEXT may not be a valid datatype.) What's in Variable? (Check it in the Debug window.) Rick Sprague

 

[cyork]

I know that TEXT is valid because the statement worked when I put an actual string in it.

The variable contains one of several CMSA (Consolidated Metropolitan Statistical Area) names. e.g.
"Denver-Boulder-Greeley, CO".

Any thoughts?

Thanks,

Chris

 

[RickSpr]

In the example you give, "Denver-Boulder-Greeley, CO", this is not a valid column name. It might work if you enclosed it in square brackets--I'm not sure. Try this:

dbs.execute "ALTER TABLE example " _
& "ADD COLUMN [" & Variable & "] TEXT;" Rick Sprague

 

[cyork]

Thanks a lot Rick - I can't believe I missed that!

Chris