The Linux Page

MS-Access: INSERT INTO syntax error

Today I was creating a report and created a table with a column named 'Group'. It looked like it worked just fine so I moved on with it.

Then, at the point I wanted to insert data with a simple INSERT INTO ... statement, it broke. The statement would generate a syntax error. Yes. The simple answer is that GROUP is viewed as a keyword and thus when used as a field name it needs to be escaped (i.e. written between backward quotes: `...`). Not liking the need to escape a field name each time I use it, I just renamed the field which is even better.

Now that error most certainly makes sense, but the problem with MS-Access is that it does not tell you where in the statement the error occurs. That makes it hard to debug at times!

Another gotcha, name wise, they say that any name with letters, underscores, digits will work (and most any characters except period (.) square brackets ([]) and backward quotes (`) for obvious reasons, it is used in other places to escape the name of a field!) So, you would think that a field name such as Integer1 or Long2 would be fine. They are not. Somehow, MS-Access detects the INTEGER and LONG keywords on those. I guess Group3 would not work either.

So, I changed this:

INSERT INTO MyTable (Group, ...) VALUES ('a', ...)

with the following:

INSERT INTO MyTable (MyGroup, ...) VALUES ('a', ...)

and it worked just fine.

Got a problem with INSERT INTO but you don't get an error? Check this post instead.