Microsoft KB Archive/884185

= You may receive an error message when you try to insert a new record in a table that contains an Autonumber field in Access 2007 or in Access 2003 =

Article ID: 884185

Article Last Modified on 6/25/2007

-

APPLIES TO


 * Microsoft Office Access 2007
 * Microsoft Office Access 2003

-



SYMPTOMS
When you try to insert a new record in a table that has an Autonumber field, you may receive the following error message:

The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

Note The table may not have any relationships or any indexes.

This problem occurs in Microsoft Office Access 2007 or in Microsoft Office Access 2003.



CAUSE
This problem may occur when the following conditions are true:
 * The Access database was compacted.
 * The table contains an Autonumber field that is not correctly reseeded.
 * You install Microsoft Jet 4.0 Database Engine Service Pack 8 (SP8).
 * You append data from linked table 1 to linked table 2 by using the Current Database option instead of the Another Database option. Then you delete the record from linked table 1 and reappend the same record from linked table 2 by using the Current Database option.



Access 2007
Re-create the Append queries in the dbFrontEnd.accdb database. To do this, click Append in the Query Type group on the Design tab, and then click Another Database.

Access 2003
Re-create the Append queries in the dbFrontEnd.mdb database. To do this, click Append Query on the Query menu in query design view, and then click Another database.



WORKAROUND
To work around this problem, you must compact the database and then reset the Autonumber field seed. To compact the database, follow these steps:  Start Access. Open the Access database.

Note If you see the Security Warning dialog box, click Open. Access 2007  Click the Microsoft Office Button, point to Manage, and then click Compact and Repair Database.

Access 2003  On the Tools menu, click Database Utilities, and then click Compact and Repair Database.</li></ul> </li> If you see the Security Warning dialog box, click Open.</li></ol>

To reset the Autonumber field seed, use one of the following methods.

Open the database that has the table (back-end database) in Access 2007

 * 1) On the Create tab, click Query Design in the Other group.
 * 2) In the Show Table dialog box, click Close.
 * 3) On the Design tab, click SQL view in the Results group.
 * 4) Type the following in the Query1 window:

ALTER TABLE  ALTER COLUMN   COUNTER( ,1);

Note is a placeholder for the name of the table. is a placeholder for the name of the Autonumber field. is a placeholder for the current maximum value in the field plus 1.
 * 1) On the Design tab, click Run in the Results group.

Access 2003

 * 1) In the Database window, click Queries under Objects.
 * 2) Click New, click Design View, and then click OK.
 * 3) In the Show Table dialog box, click Close.
 * 4) On the Query menu, click SQL Specific, and then click Data Definition.
 * 5) In the Data Definition Query window, type the following:

ALTER TABLE  ALTER COLUMN   COUNTER( ,1);

Note The placeholder  represents the name of the table. The placeholder  represents the name of the Autonumber field. The placeholder  represents the current maximum value in the field plus 1.
 * 1) On the Query menu, click Run.

Method 2: Run Visual Basic for Applications code
<ol> Access 2007  On the Create tab, click the down arrow under Macro, and then click Module.</li></ul>

Access 2003  In the Database window, click Modules under Objects, and then click New.</li></ul> </li>  Paste the following code in the Visual Basic Editor. Sub ResetAuto

Dim iMaxID As Long Dim sqlFixID As String

iMaxID = DMax(&quot;<AutonumberFieldName>&quot;, &quot; represents the name of the Autonumber field. The placeholder   represents the name of the table. </li> On the Run menu, click Run Sub/UserForm.</li></ol>

Note You must close the table before you use either method. You do not have to save the query or the module after you successfully use either method.

<div class="status_section">

STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the &quot;Applies to&quot; section.

<div class="moreinformation_section">

Steps to reproduce the behavior in Access 2003
<ol> Create two new blank databases, and name them dbBE1.mdb and dbBE2.mdb.</li> In the dbBE1.mdb database, create a new table that is named Table1 that contains the following two fields:  Field1: Autonumber (Primary Key)</li> Field2: Text</li></ul> </li> Add the following six records to Table1. </li> In the dbBE2.mdb database, create a new table that is named tblArchive that contains the following two fields:  <li>Field1: Number</li> <li>Field2: Text</li></ul> </li> <li>Create a new blank database and name it dbFrontEnd.mdb.</li> <li>In the dbFrontEnd.mdb database, create a new link table to the Table1 table in the dbBE1.mdb database.</li> <li>In the dbFrontEnd.mdb database, create another new link table to the tblArchive table in the dbBE2.mdb database.</li> <li>In the dbFrontEnd.mdb database, create a new append query based on Table1 in design view. <ol style="list-style-type: lower-alpha;"> <li>Add all fields from Table1 to the design grid.</li> <li>On the Query menu in query design view click Append query.</li> <li>In the Append To box, type tblArchive as the table name.</li> <li>Click Current Database, and then click OK.</li> <li>In the Criteria row for Field1, type 3 .</li> <li>Run the new query. You see that record 3 is appended to the tblArchive table.</li></ol> </li> <li>Delete record 3 from Table1.</li> <li>In the dbFrontEnd.mdb database, create a new append query that is based on the tblArchive table in design view. <ol style="list-style-type: lower-alpha;"> <li>Add all fields from the tblArchive table to the design grid.</li> <li>On the Query menu in query design view, click Append query.</li> <li>In the Append To box, type Table1 as the table name.</li> <li>Click Current Database, and then click OK.</li> <li>In the Criteria row for Field1, type 3 .</li> <li>Run the new query. You see that record 3 is appended back to the Table1 table.</li></ol> </li> <li>Open Table1, and then try to add a new record. You receive the error message that is mentioned in the &quot;Symptoms&quot; section.</li></ol>

<div class="references_section">