Thursday, April 06, 2006

ADO.NET OLEDB Syntax Errors

I have a large MS Access Sql Script that creates my Access database. I need to run it programatically to create databases on the fly. So I've created a C# application that reads each line of the file and applies the SQL against the database. However, whenever I use the OleDb data provider I get syntax errors. The following SQL for example causes a 'CREATE TABLE' syntax error.

CREATE TABLE Action (
Id INTEGER NOT NULL,
ActionTypeId INTEGER NOT NULL,
Name TEXT(50) NOT NULL,
Description TEXT(255) NULL,
ParentId INTEGER NULL,
SortOrder TEXT(255) NULL,
Tooltip TEXT(255) NULL,
RequiredLicense INTEGER NULL,
OptionalLicense INTEGER NULL,
TableItem TEXT(50) NULL,
SystemPrefEnabled TEXT(255) NULL,
PRIMARY KEY (Id)
)

If I paste this SQL into Access it runs sucessfully. Hmm... I cut down the statement to create a table with only the first column, still fails. So I change the name 'Action' to 'tblAction' and the statement executes without error and creates my table with the name 'tblAction'. So it would seem that 'Action' is some kind of important or reserved word. But why does it work from a SQL window in MS Access? I do not know.


I was about to give up on this and change careers, when I thought I would try connecting to my access database through ODBC. Well, it's been a while since I've used ODBC, but the syntax is pretty much the same so the change is easy. I found a good connection string at www.connectionstrings.com and voila! My entire SQL script runs without error. What!?

I would dig into this and understand why my SQL runs in some cases and not others, but alas, I am already late on this deliverable...

No comments:

Post a Comment