ACC97: Error Saving Table with Multiple Primary Key (167171)

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.

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

Open your table in Design view.
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.
Release the CTRL key.
On the Edit menu, click Primary Key.

-or-

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

Method 2: Edit the PrimaryKey Index

Open your table in Design view.
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.
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.
On the View menu, click Indexes.
In the Indexes dialog box, locate the index named PrimaryKey and identify the field name that appears twice in that index.
Click the record selector button to the left of the Index Name column for the duplicate field name.
Press DELETE.
Close the Indexes dialog box.
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

Select the InvoiceNumber field by clicking the record selector button to the left of the field name.
Press the CTRL key and click the record selector button for the PartNumber field.
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.
On the View menu, click Indexes. Note that the PrimaryKey index contains two entries for the InvoiceNumber field.
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.