This article applies only to a Microsoft Access database (.mdb).
Overview: Changing Initial Value of an AutoNumber FieldIn 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.
Example: Changing the Initial Value of an AutoNumber Field to 100The following example uses the sample database Northwind.mdb to set a new starting value for an AutoNumber field in the Employees table:
CAUTION: 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 new record: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 message:
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.
Article ID: 209696 - Last Review: Jun 24, 2004 - Revision: 1