Syntax for AUTOINCREMENT column for MS Access and ODBC

1
votes

Using the ODBC JET 4.0 driver with an older (Access 2003 or earlier) MS Access database in C#. The following work:

CREATE TABLE [FunkyTable] (FunkyCol INTEGER);
CREATE TABLE [FunkyTable] (FunkyCol INTEGER PRIMARY KEY);

But all of the following fails with an error

ERROR [42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in CREATE TABLE statement

CREATE TABLE [FunkyTable] (FunkyCol INTEGER AUTOINCREMENT);
CREATE TABLE [FunkyTable] (FunkyCol INTEGER AUTOINCREMENT(1,1) PRIMARY KEY);
CREATE TABLE [FunkyTable] (FunkyCol INTEGER AUTO_INCREMENT);
CREATE TABLE [FunkyTable] (FunkyCol INTEGER AUTO_INCREMENT(1,1) PRIMARY KEY);
CREATE TABLE [FunkyTable] (FunkyCol INTEGER IDENTITY);
CREATE TABLE [FunkyTable] (FunkyCol INTEGER IDENTITY(1,1) PRIMARY KEY);

What is the correct syntax?

2
sqlms-accessodbc

2 Answers

1
votes

AUTOINCREMENT is a field type based on Long Integer, so INTEGER AUTOINCREMENT amounts to asking for 2 field types. However, Access only lets you designate 1 type per field.

Include PRIMARY KEY so the field will operate as a proper Access autonumber. (Without PRIMARY KEY, the table would accept duplicate FunkyCol values.)

CREATE TABLE [FunkyTable] (FunkyCol AUTOINCREMENT PRIMARY KEY);
1
votes

I think this is what you are looking for:

CREATE TABLE [FunkyTable] (FunkyCol COUNTER);

Found the details in bottom example of this MS documentation: https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/create-table-statement-microsoft-access-sql