ACC97: Error Saving Table with Multiple Primary Key

• Microsoft Access 97

SYMPTOMS

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

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

   Invalid index definition.

   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.

5. 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

1. Start Microsoft Access and open the sample database Northwind.mdb.

2. Click the Tables tab in the Database window, and then click the New button.

3. In the New Table dialog box, click Design View, and then click OK.

4. 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
   
   

5. Select the InvoiceNumber field by clicking the record selector button to the left of the field name.

6. Press the CTRL key and click the record selector button for the PartNumber field.

7. 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.

8. On the View menu, click Indexes. Note that the PrimaryKey index contains two entries for the InvoiceNumber field.

9. 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.

REFERENCES