How do you create an MS Access table in C# programmatically?

Question

command.CommandText = "CREATE TABLE MyTable (" +
                "[Count] INT NOT NULL AUTO_INCREMENT PRIMARY KEY ," +
                "[TimeAndDate] TIMESTAMP NOT NULL ," +
                "[SerialNumber] VARCHAR( 14 ) NOT NULL ," +
                "[Result] BOOL NOT NULL ," +
                "UNIQUE ([TimeAndDate]))";

command.ExecuteNonQuery();

Code above flags syntax error exception, can you help me correct the query string? Thank you.

reiniero · Accepted Answer · 2011-08-03 06:06:23Z

3

I'd suggest pasting the resulting query text into an Access query; it will tell you where the error is.

On my Access XP, pasting the resulting SQL gave a syntax error on AUTO_INCREMENT; it should be AUTOINCREMENT (see Access 2007 SQL data types) and be specified as a data type, not a constraint. BOOL also gave an error => use BIT

Result which worked:

CREATE TABLE MyTable (
               [Count] AUTOINCREMENT NOT NULL PRIMARY KEY ,
               [TimeAndDate] TIMESTAMP NOT NULL ,
               [SerialNumber] VARCHAR( 14 ) NOT NULL ,
               [Result] BIT NOT NULL ,
               UNIQUE ([TimeAndDate]));

answered Aug 3, 2011 at 6:06

reiniero

4286 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

LEMUEL ADANE Over a year ago

How can you do that in Access? Where did you paste the SQL statement?

reiniero Over a year ago

Create a new query; View menu shows Design, Datasheet and SQL (or something of the kind). Choose SQL. Alternatively, you could paste it in a suitable command in the VBA/code editor (Alt-F11) immediate window (Ctrl-G), e.g. currentdb.Execute("bla") (where bla is the sql statement). For newer versions of access you might need to set a reference to the dao library though...

Klaus Byskov Pedersen · Accepted Answer · 2011-01-24 11:18:11Z

2

You need an ending parenthesis ).

answered Jan 24, 2011 at 11:18

Klaus Byskov Pedersen

122k31 gold badges192 silver badges223 bronze badges

Comments

GvS · Accepted Answer · 2011-01-24 11:26:06Z

1

Just after MyTable you use an open bracket "(", that you do not close.

To create tables in Access, I normally use ADOX, this prevents this kind of syntax errors.

edited Jan 24, 2011 at 11:26

answered Jan 24, 2011 at 11:18

GvS

52.5k16 gold badges104 silver badges139 bronze badges

Collectives™ on Stack Overflow

How do you create an MS Access table in C# programmatically?

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related