Microsoft KB Archive/167171

= ACC97: Error Saving Table with Multiple Primary Key =

Article ID: 167171

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q167171



Novice: Requires knowledge of the user interface on single-user computers.



SYMPTOMS
When you create a multiple-field primary key in Design view of a table, you may receive the following error message:

Invalid index definition.

This will be followed by:

Microsoft Access was unable to create the table.

  -or-

Errors were encountered during the save operation. Indexes were not added or changed.



CAUSE
When you use the right mouse button (right-click) to create a multiple-field primary key, Microsoft Access 97 generates two fields in the PrimaryKey index for one of the fields in your table.



RESOLUTION
There are several ways to create a multiple-field primary key; the problem occurs only with one of them. You can work around this behavior easily by using one of the following methods.

Method 1: Set the Primary Key Using the Edit Menu or Toolbar

 * 1) Open your table in Design view.
 * 2) Press and hold down the CTRL key, and then click the record selector button to the left of the Field Name for each record you want to include in your primary key. Note that the field rows are highlighted to indicate that you have selected them.
 * 3) Release the CTRL key.
 * 4) On the Edit menu, click Primary Key.

-or-

Click the Primary Key button on the Table Design toolbar.
 * 1) Save your table.

Method 2: Edit the PrimaryKey Index

 * 1) Open your table in Design view.
 * 2) Press and hold down the CTRL key, and then click the record selector button to the left of the Field Name for each record you want to include in your primary key. Note that the field rows are highlighted to indicate that you have selected them.
 * 3) While still pressing the CTRL key, use the right mouse button (right-click) to click the record selector button for one of the fields you have selected, and then click Primary Key on the shortcut menu that appears.
 * 4) On the View menu, click Indexes.
 * 5) In the Indexes dialog box, locate the index named PrimaryKey and identify the field name that appears twice in that index.
 * 6) Click the record selector button to the left of the Index Name column for the duplicate field name.
 * 7) Press DELETE.
 * 8) Close the Indexes dialog box.
 * 9) Save your table.



MORE INFORMATION
Note that this behavior only occurs if you right-click the record selector button for one of the rows in your primary key; if you right-click elsewhere in one of the selected rows, and then click Primary Key on the shortcut menu, the problem does not occur.

Steps to Reproduce Behavior
 Start Microsoft Access and open the sample database Northwind.mdb. Click the Tables tab in the Database window, and then click the New button. In the New Table dialog box, click Design View, and then click OK.  Create three new fields in the table:

     Table: tblJunction ---     Field Name: InvoiceNumber Data Type: Number Field Size: Long Integer Field Name: ExtraField Data Type: Text Field Name: PartNumber Data Type: Number Field Size: Long Integer </li> Select the InvoiceNumber field by clicking the record selector button to the left of the field name.</li> Press the CTRL key and click the record selector button for the PartNumber field.</li> While still pressing the CTRL key, right-click the record selector button for the InvoiceNumber field, and then click Primary Key on the shortcut menu that appears.</li> On the View menu, click Indexes. Note that the PrimaryKey index contains two entries for the InvoiceNumber field.</li> On the File menu, click Save, and then click OK in the Save As dialog box. Note that you receive two error messages, and you cannot save the table. At this point, you can close the table without saving it.</li></ol>

<div class="references_section">