ACC2000: How to Use an Append Query to Set Initial Value of an AutoNumber Field (209696)

MORE INFORMATION

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

1. Copy the design of the original table in which you want to set the starting AutoNumber value to a new table.
2. Change the AutoNumber field in the new table to a Number field with a FieldSize property of Long Integer.
3. 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.
4. 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.

NOTE: 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:

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.

1. Open the sample database Northwind.mdb.
2. In the Database window, click the Employees table, and then on the Edit menu, click Copy.
3. On the Edit menu, click Paste.
4. In the Paste Table As dialog box, type EmployeesTemp in the Table Name box, and then click Structure Only. Click OK.
5. Open the EmployeesTemp table in Design view.
6. Change the data type of the EmployeeID field to Number, and set its FieldSize property to Long Integer.
7. Save the EmployeesTemp table and close it.
8. 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

9. Create a new query in Design view based on the EmployeesTemp table.
10. Add all fields from the table to the query grid by double-clicking the * in the EmployeesTemp field list.
11. On the Query menu, click Append Query.
12. Select Employees in the Append To Table Name box, and then click OK.
13. 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.
14. Close the query and do not save it.
15. Delete the EmployeesTemp table, and then delete the newly appended record from the Employees table.
16. Open the Employees table in Datasheet view and add a new record. Note that the Employee ID field starts numbering at 100.