AUTOINCREMENT does not work on OleDbCommand

I'm doing MS Ac开发者_JAVA技巧cess database file using OleDb. Here is the snippet:

OleDbCommand command = oleDbConnection.CreateCommand();

command.CommandText =
    "CREATE TABLE MyTable (" +
    "[Count] LONG NOT NULL PRIMARY KEY AUTOINCREMENT, " +
    "[TimeAndDateTested] TIMESTAMP NOT NULL, " +
    "[SerialNumber] VARCHAR(14) NOT NULL, " +
    "[TestResult] BIT NOT NULL)";                    

command.ExecuteNonQuery();

Do you know what's wrong? Thanks.

In Access 2003, this statement will create the table structure I think you want. Notice I changed the name of the first field to Kount because Count is a reserved word in Access. You could enclose the name in square brackets to avoid ambiguity, but I prefer to just avoid using reserved words as object names. TIMESTAMP is recognized as a synonym for DATETIME. VARCHAR is recognized as a synonym for TEXT. BIT will get you a field type which Access calls Yes/No.

CREATE TABLE MyTable2 (
Kount COUNTER CONSTRAINT pkey PRIMARY KEY,
TimeAndDateTested TIMESTAMP NOT NULL,
SerialNumber VARCHAR(14) NOT NULL,
TestResult BIT NOT NULL);

I assigned a primary key constraint to Kount, and named the constraint as "pkey". Not Null and unique are implied by the primary key constraint, so you needn't specify them separately.

I change:

"[Count] LONG NOT NULL PRIMARY KEY AUTOINCREMENT, " +

with:

"[Count] IDENTITY NOT NULL PRIMARY KEY, " +

and it worked.

When using DDL against MS Access, you want to use COUNTER to specify an auto-incrementing integer Field.