How to reset an AutoNumber field value in Access

This
step-by-step article describes how to reset an AutoNumber
field value in Access. The AutoNumber field value in Access
does not automatically reset when you delete some rows or all rows in a table.
To reset the AutoNumber field value and to refresh the
AutoNumber value in the referenced table, you must manually
perform some tasks.

Note You must back up your database before you perform the steps that
follow.

Reset an AutoNumber field in a single table

To reset an AutoNumber field value, you can use
either Method 1 or Method 2.

Method 1

You can reset an AutoNumber field
value so it corresponds with one of the fields in the table. To do this in
Microsoft Office Access 2003 and in earlier versions, follow
these steps:

  1. Delete
    the AutoNumber field from the main table.

    Make note
    of the AutoNumber field name.

  2. Click Queries on the left pane.
    Double-click Create query in Design view on right
    pane.
  3. In the Show Table dialog box, select the
    main table. Click Add and then click
    Close.
  4. Double-click the required fields in the table view of the
    main table to select the fields.
  5. Select the required Sort order.
  6. On the Query menu, click
    Make-Table Query. Type the new table name in the Table
    Name
    text box and then click OK.
  7. On the Query menu, click
    Run.
  8. A dialog box appears with the text that follows:
    You are about to append # row(s) into a new table. Click
    Yes to insert the rows.
  9. On the File menu, click
    Close. Click No to close the
    Make-Table Query window.
  10. Click Tables on the left pane. Right-click
    the new table and then click Design View.
  11. In the Design view for the table, add an
    AutoNumber field with the same field name that you deleted in
    step 1. Add this AutoNumber field to the new table and then
    save the table.
  12. Close the Design view window.
  13. Rename the main table name. Rename the new table name to
    the main table name.

To do this in
Microsoft Office Access 2007, follow
these steps:

  1. Delete the
    AutoNumber field from the main table.

    Make note of
    the AutoNumber field name.

  2. Click the Create tab, and then click
    Query Design in the Other group.
  3. In the Show Table dialog box, select the
    main table. Click Add, and then click
    Close.
  4. Double-click the required fields in the table view of the
    main table to select the fields.
  5. Select the required Sort order.
  6. On the Design tab, click Make
    Table
    in the Query Type group. Type the new table
    name in the Table Name box, and then click
    OK.
  7. On the Design tab, click
    Run in the Results group.
  8. The following message appears:

    You are about to paste # row(s) into a new table.

    Click
    Yes to insert the rows.

  9. Close the query.
  10. Right-click the new table, and then click
    Design View.
  11. In the Design view for the table, add an
    AutoNumber field that has the same field name that you deleted in
    step 1. Add this AutoNumber field to the new table, and then
    save the table.
  12. Close the Design view window.
  13. Rename the main table name. Rename the new table name to
    the main table name.

Method 2

Alternatively, to reset an AutoNumber
field value by using Method 2, follow these steps:

  1. In
    Access 2003 and in earlier versions, delete the
    AutoNumber field from the main table.

    Make note of
    the AutoNumber field name.

  2. Copy the structure of the main table and then create a new
    table.
  3. Click Queries on the left pane. Click
    Create query in Design view on right pane.
  4. In the Show Table dialog box, select the
    main table. Click Add and then click
    Close.
  5. To select the fields, double-click the required fields. Do
    this for all the fields except for the AutoNumber field in the
    Table view of the main table.
  6. On the Query menu, click Append
    Query
    .

    This changes the query type.

  7. From the Table Name list, select the new
    table that you created in step 2. Click OK.
  8. On the Query menu, click
    Run.
  9. A dialog box appears with the text that follows:
    You are about to paste # row(s). Click Yes
    to insert the rows.
  10. On the File menu, click
    Close. Click No to close the Append
    Query
    window.
  11. Click Tables on the left pane. Right-click
    the new table and then click Design View.
  12. In the Design view for the table, add an
    AutoNumber field with the same field name that you deleted in
    step 1. Add this AutoNumber field to the new table and then
    save the table.
  13. Close the Design view window.
  14. Rename the main table name. Rename the new table name to
    the main table name.

In Access 2007, follow these steps:

  1. Delete the AutoNumber
    field from the main table.

    Make note of the
    AutoNumber field name.

  2. Copy the structure of the main table, and then create a new
    table.
  3. Click the Create tab, and then click
    Query Design in the Other group.
  4. In the Show Table dialog box, select the
    main table. Click Add, and then click
    Close.
  5. To select the fields, double-click the required fields. Do
    this for all the fields except for the AutoNumber field in the
    Table view of the main table.
  6. On the Design tab, click
    Append in the Query Type group. This changes
    the query type.
  7. From the Table Name list, select the new
    table that you created in step 2, and then click OK.
  8. On the Design tab, click
    Run in the Results group.
  9. The following message appears:

    You are about to append # row(s) into a new table.

    Click
    Yes to insert the rows.

  10. Close the query.
  11. Right-click the new table, and then click
    Design View.
  12. In the Design view for the table, add an
    AutoNumber field that has the same field name that you deleted in
    step 1. Add this AutoNumber field to the new table, and then
    save the table.
  13. Close the Design view window.
  14. Rename the main table name. Rename the new table name to
    the main table name.

Reset an AutoNumber field in a table with referenced tables

The steps that follow describe how to reset the
AutoNumber field for a table that has one referenced table. If
you have more than one referenced table, you must follow these steps for each
referenced table.

  1. Remove the relationship between the tables.
  2. Set the AutoNumber field of the main table
    to a Number data type. Remove the primary key.
  3. Create a new field of AutoNumber data type
    in the main table. Save the table.
  4. Create a new field of Number data type in the referenced table. Save the table.
  5. To create an update query that updates the new field in
    the referenced table to the new AutoNumber field of the main
    table, follow these steps:

    1. In
      Access 2003 and in earlier versions, click
      Queries on the left pane. Click Create query in Design
      view
      on right pane.

      This creates your new query.

    2. In the Show Table dialog box, select
      the main table and the referenced table. Click Add to add the
      main table and the referenced table. Click Close.
    3. Click the field in the main table that was previously
      linked to the referenced table. Use the drag-and-drop feature to put the field
      on the previously linked field of the referenced table.

      This creates
      the join between the tables that is based on the original linking fields.

    4. On the Query menu, click
      Update Query.
    5. Double-click the new field from the referenced table to
      add it to the field list.
    6. In the Update To field, type
      Main TableName.New AutoNumber field to update the
      new field values in the referenced table.
    7. On the Query menu, click
      Run.
    8. A dialog box appears with the text that follows:
      You are about to update # row(s). Click Yes
      to update the rows.
    9. On the File menu, click
      Close. Click No to close the Update
      Query
      window.

    In Access 2007, follow these steps:

    1. Click the Create tab,
      and then click Query Design in the Other
      group. This creates the new query.
    2. In the Show Table dialog box, select
      the main table and the referenced table. Click Add to add the
      main table and the referenced table. Click Close.
    3. Click the field in the main table that was previously
      linked to the referenced table. Use the drag-and-drop feature to put the field
      on the previously linked field of the referenced table.

      This creates
      the join between the tables that is based on the original linking fields.

    4. On the Design tab, click
      Update in the Query Type group. This changes
      the query type.
    5. Double-click the new field from the referenced table to
      add it to the field list.
    6. In the Update To field, type
      Main TableName.New AutoNumber field to update the
      new field values in the referenced table.
    7. On the Design tab, click
      Run in the Results group.
    8. The following message appears:

      You are about to update # row(s).

      Click Yes
      to update the rows.

    9. Close the query.
  6. Delete the original linking field from the main table and
    the referenced table.
  7. Rename the new AutoNumber field to the
    original name.
  8. Re-create the primary key and the relationship between the
    tables.

This procedure resets your AutoNumber field and
then updates the referenced table with the correct key values.

You can also reset the AutoNumber field if
you compact the database. However, this may not always work successfully with
Access 2002 or later. You may have more success if you compact the database
with Access 2000 and Jet 4.0 version of the Microsoft Jet Database
Engine.

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

287756

(http://support.microsoft.com/kb/287756/
)

AutoNumber field is not reset after you compact an Access database

For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

209696

(http://support.microsoft.com/kb/209696/
)

How to use an append query to set initial value of an AutoNumber field

94821

(http://support.microsoft.com/kb/94821/
)

Use append query to set initial value of AutoNumber field

202121

(http://support.microsoft.com/kb/202121/
)

Cannot change default seed and increment value in UI

Article ID: 812718 – Last Review: April 15, 2013 – Revision: 7.4


Applies to
  • Microsoft Office Access 2007
  • Microsoft Office Access 2003
  • Microsoft Access 2002 Standard Edition
  • Microsoft Access 2000 Standard Edition
kbsysadmin kbhowtomaster kbhowto KB812718

Follow this link:
How to reset an AutoNumber field value in Access

Comments are closed.

Recent Comments

    Archives

    Categories