ACC2002: Using the "Create Table by Entering Data" Option Starts Primary Key Field at 22 Instead of at 1 (275084)


The information in this article applies to:

  • Microsoft Access 2002
This article was previously published under Q275084
Novice: Requires knowledge of the user interface on single-user computers.

This article applies only to a Microsoft Access database (.mdb).

SYMPTOMS

If you use the Create table by entering data option to create a table, the primary key field begins with a value of 22 instead of the expected value of 1.

CAUSE

You see this behavior when you save the table without entering any data, and you then click Yes when you are prompted to create a primary key.

RESOLUTION

If you have not entered much data, you can delete the existing records, compact the database, and then enter the records again. Compacting the database when the table does not contain any records resets the primary key value to 1.

If you have already entered a lot of data, use the following steps to create a new table and to move the existing data into the new table with a new primary key starting at 1.

IMPORTANT: The following steps create a new primary key. You should not follow these steps if you have used the primary key in a relationship to another table.

  1. In the Database window, select the table that contains the primary key field that starts at 22.
  2. On the Edit menu, click Copy.
  3. On the Edit menu, click Paste.
  4. In the Table Name box, type a name for a new table. Under Paste Options, click Structure Only, and then click OK.
  5. In the Database window, click Queries under Objects, and then double-click Create query by using wizard.
  6. Click the arrow in the Tables/Queries box, and then click the table that has the primary key that starts at 22. Move all fields except ID, the primary key field, to the Selected Fields list. Click Next.
  7. Click Modify the query design, and then click Finish.
  8. On the Query menu, click Append Query.
  9. Click the arrow in the Table Name box, click the table that you created in step 4, and then click OK.
  10. On the Query menu, click Run. Click Yes when you are prompted to append the records.
After you confirm that the new table contains the correct records, you can delete the original table.

MORE INFORMATION

When Access creates the table after you click the Create table by entering data option, Access adds 21 blank records to the recordset before any data is entered to create the table in Datasheet view. When the primary key field is added, it takes into account the 21 records that were entered, and then starts with 22. You do not experience this behavior if you add any data to the table before you save it.

Steps to Reproduce the Behavior

  1. Open the sample database, Northwind.mdb.
  2. In the Database window, click Tables under Objects , and then double-click Create table by entering data.
  3. Save the table.
  4. When you are prompted, click Yes to create a primary key.
  5. Type a value in the Field1 column.
Note that the AutoNumber field starts with the value 22.
Modification Type:MajorLast Reviewed:10/20/2003
Keywords:kbnofix kbprb KB275084
©2003 Microsoft Corporation. All rights reserved.