SQL error 2

[elziko]

I'm using the following line to add a field to a table (.mdb):

conmdb.Execute ("ALTER TABLE tblReportData ADD COLUMN " & FieldName & " String&quot

I use this many times in a loop but on certain times it falls over. Namely when FieldName="TIME" or FieldNAme="BIT". I can not see ANY reason why this is happening:

Syntax error in field definition

Am I using illegal words that Access reserves for someting else? I dont seem to find any evidence of this though. I can call a field "TIME" in the access tabel design window!

I've entered the error code -2147217900 on the MSDN site with no luck.

Any tips?

elziko

 

[bosully]

This is one of the 'features' of MS Access: it lets you break every rule of data-base design at the front end and then fixes it up without telling you. It's only when you're coming at your database from somewhere other than the Access interface that you encounter the problems you have here.

You are right in assuming that the words TIME and BIT are illegal but not with Access which just renames them in the background. They are SQL reserved keywords with specific functions and meaning, not sure what at the moment, but as such you can't use them in this way.

Just re-name your fields and you're away.

Brendan.

 

[elziko]

Yeah, I had a sneaky suspicion this was the case. I'm not against MS but this is, indeed, the most stupid thing I have come across!

It doesn't seem to be obviously documented either.>:-<

Many thanks,

elziko

 

[strongm]

As I said in my answer to the other instance of this question, you don't need to rename the columns at all. TIME and BIT are not illegal column names per se. Its just that the Jet's SQL parser needs to be told they are column names. Just add square brackets as delimiters to achieve this:

conmdb.Execute (&quot;ALTER TABLE tblReportData ADD COLUMN [&quot; & FieldName & &quot;] String&quot

 

[elziko]

Yeah thanks! Silly me for posting twice, but I did see your reply too add I'm glad I could keep my field names.

Thanks to you both.