Thursday, November 26, 2009

Reserved names in Access

In all projects I work, we use Postgresql as database, therefore I'm used to this kind of SQL syntax
INSERT INTO "TableName"("FieldOne", "FieldTwo", "FieldN") VALUES('Value1', 'Value2', 'ValueN');
but yesterday I had to implement and use a local database made in access and access it using ADO, all was OK until I've encountered a superficial error, I had to insert a new record but one of the fields has the name Date, now here's where the error comes, Date is a reserved word!! therefore I got a lot of errors and didn't knew where the problem came from until I've surrounded each field name between brackets like "[" and "]" in this case the field name is not treated as reserved word even though it is
-- the error
INSERT INTO aTable(FieldOne, FieldTwo, Date) VALUES('x', 'x', #2009-25-12 14:14:00#);

-- after figuring out the problem here is the new insert script
INSERT INTO aTable([FieldOne], [FieldTwo], [Date]) VALUES('x', 'x', #2009-25-12 14:14:00#);
So in order to play safe, put each field name between "[" and "]" and you will know that this won't be the error when you get sql syntax error :).
Postgre, on the other hand, has a much better syntax because, each table name or field name must be between double quotes and functions or reserved words are not.
If you know any other error like the one I've mentioned above, feel free to comment.

No comments:

Post a Comment

Blogroll(General programming and Delphi feeds)