How to create an AutoNumber field value in Access?

ms-access autonumber
Gopal picture Gopal · Jul 2, 2009 · Viewed 44.8k times · Source

I'm trying the following:

CREATE TABLE Table1
(
    RecordNo autonumber, --error here!
    PersonId varchar(50),
    ...
)

But, there is an error.
How can I build the correct query in Access?

Answer

Eugene Yokota picture Eugene Yokota · Jul 2, 2009

According to SQL Auto Increment a Field:

CREATE TABLE Persons
(
P_Id PRIMARY KEY AUTOINCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

The MS Access uses the AUTOINCREMENT keyword to perform an auto-increment feature.

By default, the starting value for AUTOINCREMENT is 1, and it will increment by 1 for each new record.

To specify that the "P_Id" column should start at value 10 and increment by 5, change the autoincrement to AUTOINCREMENT(10,5).

Synonyms for AUTOINCREMENT include COUNTER and IDENTITY. Using IDENTITY the makes a lot of sense because it matched the @IDENTITY variable which returns the last used autonumber value.

Related questions

Using Autonumbering in Access - INSERT statements

I'm having trouble running an INSERT statement where there's an autonumber as the PK field. I have an Auto-incrementing long as the Primary Key, and then 4 fields of type double; and yet Access (using ADO) seems to want five values …

Read More
Can I edit AutoNumber Column in Access?

I've lost my data in Access base, and I've manage to bring them back but when I copy the values in the table with the AutoNumber Column it increments the numbers. Is there Any way to change it to int …

Read More
How to reset an Access table's AutoNumber field? (it didn't start from 1)

I have a INSERT INTO ... SELECT statement that copies data from one table to another. The thing though is, the AutoNumber column value in the second table started from the last number in the first one. Meaning the count of …

Read More