Search code examples
ms-accessautonumber

How to create an AutoNumber field value in Access?

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?

Solution

  • 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.