By using an append query, you can change the starting value of an
AutoNumber field in a table to a number other than 1.
Microsoft Access always numbers AutoNumber fields beginning with the number 1. If the table has data in it already, the starting value of the autonumber will be higher than the highest value already in the table. You cannot edit an AutoNumber field or change its starting value.
Overview: Changing Initial Value of an AutoNumber Field
In order to force Microsoft Access to number an AutoNumber field with
a number you choose, follow these general steps (a more detailed example is shown later in this article):
- Copy the design of the original table in which you want to set the
starting AutoNumber value to a new table.
- Change the AutoNumber field in the new table to a Number field with a
FieldSize property of Long Integer.
- Add a record to the new table, and set the Number field to a value that is one less than the starting number that you want for your original table. For example, if you want the AutoNumber field to start at 100, type 99 in the Number field of the new table.
- Use an append query to add this new record to your original table.
This action forces Microsoft Access to number any new AutoNumber
fields with your number plus 1.
: Do not compact the database before you add a new record to the
original table. If you do, Microsoft Access will reset the AutoNumber
field value to the number 1.
Example: Changing the Initial Value of an AutoNumber Field to 100
The following example uses the sample database Northwind.mdb to set a new starting value for an AutoNumber field
in the Employees table:
: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.
- Open the sample database Northwind.mdb.
- In the Database window, click the Employees table, and then on the Edit menu, click Copy.
- On the Edit menu, click Paste.
- In the Paste Table As dialog box, type EmployeesTemp in the Table Name
box, and then click Structure Only. Click OK.
- Open the EmployeesTemp table in Design view.
- Change the data type of the EmployeeID field to Number, and set its
FieldSize property to Long Integer.
- Save the EmployeesTemp table and close it.
- Open the EmployeesTemp table in Datasheet view and add the following
Employee ID: 99
Last Name: Type your last name
First Name: Type your first name
Birth Date: Type your birth date
- Create a new query in Design view based on the EmployeesTemp table.
- Add all fields from the table to the query grid by double-clicking the * in the EmployeesTemp field list.
- On the Query menu, click Append Query.
- Select Employees in the Append To Table Name box, and then click OK.
- On the Query menu, click Run. Microsoft Access displays the following
You are about to append 1 row(s).
Click Yes to append the record to the Employees table.
- Close the query and do not save it.
- Delete the EmployeesTemp table, and then delete the newly appended
record from the Employees table.
- Open the Employees table in Datasheet view and add a new record. Note that the Employee ID field starts numbering at 100.
For more information about AutoNumber fields, click Microsoft Access Help
on the Help
menu, type starting valueAutoNumber
in the Office Assistant or the Answer Wizard, and then click Search
to view the topic.
Article ID: 209696 - Last Review: June 24, 2004 - Revision: 2.0
- Microsoft Access 2000 Standard Edition