Try using COUNTER.
This example creates a table with an AutoNumber, and sets it as primary key:
Dim cmd As New ADODB.Command
Dim strSql As String
cmd.ActiveConnection = CurrentProject.AccessConnection
strSql = "CREATE TABLE tblDdlContractor " & _
"(ContractorID COUNTER CONSTRAINT PrimaryKey PRIMARY KEY, " & _
"Surname TEXT(30) WITH COMP NOT NULL, " & _
"FirstName TEXT(20) WITH COMP, " & _
"Inactive YESNO, " & _
"HourlyFee CURRENCY DEFAULT 0, " & _
"PenaltyRate DOUBLE, " & _
"BirthDate DATE, " & _
"Notes MEMO, " & _
"CONSTRAINT FullName UNIQUE (Surname, FirstName));"
cmd.CommandText = strSql
cmd.Execute
