Access sql for create table with autonumber

Question

I am trying to create a table with a column whose AutoIncrement is Yes. Here is my query not sure what's wrong in it

CREATE TABLE testallcols(SOCycle Text(3),   AutoKey integer AUTOINCREMENT  NOT NULL, SOData LongBinary   NOT NULL)

How do I get my AutoKey column to be an auto increment integer?

Grapheme

Try removing the Integer part and just use AutoKey AUTOINCREMENT. – Everetteeverglade

May I ask how do I set AllowZeroLength attribute while writing the create table query? – Grapheme

Access DDL can not set the AllowZeroLength attribute directly. It could be done with check constraint, but it's easier to use the DAO object model to set it. That could be a useful new question. – Colorless

I heard that DAO doesnot work on 64 bit so I am not allowed to go to DAO technology :( Any other alternates? – Grapheme

Answer 1

AUTOINCREMENT and integer are two different datatypes as far as Access DDL is concerned. Use only AUTOINCREMENT. And to make it function correctly as an autonumber, include the PRIMARY KEY constraint.

This one works without error when tested with ADO/OleDb in Access 2010:

CREATE TABLE testallcols(SOCycle Text(3), AutoKey AUTOINCREMENT PRIMARY KEY, SOData LongBinary NOT NULL)

Colorless

Answer 2

CREATE TABLE Tblcontacts ( contactid AUTOINCREMENT(101,1) PRIMARY KEY , firstname CHAR (60), lastname CHAR (60), email VARCHAR (75) );

Tolkan

How does this any value to the question that the existing accepted answer provides? It's not even for the table the question is for. – Ironhanded