You are currently offline, waiting for your internet to reconnect

ACC: Use Append Query to Set Initial Value of AutoNumber Field

This article was previously published under Q94821
This article has been archived. It is offered "as is" and will no longer be updated.
Moderate: Requires basic macro, coding, and interoperability skills.

By using an append query, you can change the starting value of anAutoNumber field in a table (or Counter field in Microsoft Access 1.x and2.0) to a number other than 1.
By design, Microsoft Access always numbers AutoNumber fields beginningwith the number 1. You cannot edit an AutoNumber field or change itsstarting value.

However, you can force Microsoft Access to number an AutoNumber field witha number you choose by following these general steps:
  • 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 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.
NOTE: Do not compact the database before you add a new record to theoriginal table. If you do, Microsoft Access will reset the AutoNumberfield value to the number 1.

The following example uses the sample database Northwind.mdb (or NWIND.MDBin version 1.x or 2.0) to set a new starting value for an AutoNumber fieldin the Employees table:

CAUTION: Following the steps in this example will modify the sampledatabase Northwind.mdb(or NWIND.MDB in version 1.x or 2.0). You maywant to back up the Northwind.mdb (or NWIND.MDB) file and perform thesesteps on a copy of the database.
  1. Open the sample database Northwind.mdb (or NWIND.MDB in version 1.x or 2.0).
  2. Select the Employees table, and 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 the Structure Only option. 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.

    NOTE: In Microsoft Access 1.x and 2.0, the Employee ID field contains a space.
  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 QBE grid.
  11. On the Query menu, click Append Query (or Append in Microsoft Access 1.x, 2.0, and 7.0).
  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:

    In Microsoft Access 7.0 and 97:
    You are about to append 1 row(s).
    In Microsoft Access 1.x and 2.0:
    1 Row(s) will be appended.
    Click Yes (or OK) 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.
For more information about AutoNumber fields, type "starting valueAutoNumber" in the Office Assistant, click Search, and then click to view"Change the starting value of an incrementing AutoNumber field."
appending starting initial

Article ID: 94821 - Last Review: 12/04/2015 09:21:49 - Revision: 2.1

Microsoft Access 1.0 Standard Edition, Microsoft Access 1.1 Standard Edition, Microsoft Access 2.0 Standard Edition, Microsoft Access 95 Standard Edition, Microsoft Access 97 Standard Edition

  • kbnosurvey kbarchive kbinfo kbusage KB94821