Microsoft KB Archive/275084

= ACC2002: Using the &quot;Create Table by Entering Data&quot; Option Starts Primary Key Field at 22 Instead of at 1 =

Article ID: 275084

Article Last Modified on 10/20/2003

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



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.

Additional query words: twentytwo twenty-two twenty two prb auto number

Keywords: kbnofix kbprb KB275084

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.