Geek Answers Handbook

How to create an AutoNumber field value in Access?

I am trying to do the following:

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

But there is a mistake.
How to create the correct request in Access?

+6
ms-access
source share
4 answers

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) )

MS Access uses the AUTOINCREMENT keyword to automatically increase the feature.

By default, the initial value for AUTOINCREMENT is 1, and this will be an increase of 1 for each new record.

To indicate that the column " P_Id " should begin with a value of 10 and an increment of 5, change the auto increment to AUTOINCREMENT(10,5) .

Synonyms for AUTOINCREMENT include COUNTER and IDENTITY . Using IDENTITY is important because it matches the @IDENTITY variable, which returns the last used autonumber value.

+5
source share

Order may be important

 CREATE TABLE Persons ( pkObject AUTOINCREMENT PRIMARY KEY)

If I try PRIMARY KEY AUTOINCREMENT as suggested, it will be upset (MSAccess 2010).

+3
source share

Method 1:

  • Open table in view mode
  • Create a field called "ID" or any other field that will automatically increment
  • Put "AutoNumber" in DataType

Method 2:

  • Create a new table
  • Close the table and save it.
  • When it asks if you want the primary key click ok
  • Open the table in the Design view
  • Edit the new field for any name you like
+1
source share

When using the ancient DAO 3.60 and Jet 4.0 with Access 2003 files, Eugene Yokota's syntax did not work. I found that the COUNTER keyword does the trick:

 CREATE TABLE tablename(id COUNTER, Name Text (30))

Thanks to this post: http://www.vbforums.com/showthread.php?234335

+1
source share

More articles:


All Articles