ACC: Referential Integrity Error Message with Shared Databases

• Microsoft Access versions 2.0, 7.0, 97

SYMPTOMS

When you share a database in Microsoft Access and try to update a record that another user has already updated, your update is not successful, and you receive the following error message:

   Data has changed; Operation stopped.

   This record has been changed by another user since you started
   editing it. If you save the record, you will overwrite the
   changes the other user made.

   Copying the changes to the clipboard will let you look at
   the values the other user entered, and then paste your changes
   back in if you decide to make changes.

   You can choose one of three option buttons:

      Save Record
      Copy To Clipboard
      Drop Changes

   In Microsoft Access 97:

    The Record cannot be deleted or changed because table '<Table Name>'
    includes related records.

   In Microsoft Access 2.0 and 7.0:

    Can't delete or change record. Since related records exist in table
    "<Table Name>", referential integrity rules would be violated.

CAUSE

You have a one-to-many relationship between your tables where the Enforce Referential Integrity option is turned on and the Cascade Update Related Fields option is turned off.

RESOLUTION

To work around this problem, modify the one-to-many relationship to select the Cascade Update Related Fields option.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access version 2.0, 7.0, and 97. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

Steps to Reproduce Problem

NOTE: Make sure that the Exclusive option is not selected in the File Open dialog box.

1. Open two instances of Microsoft Access. In each instance, open the sample database Northwind.mdb (or NWIND.MDB in Microsoft Access 2.0) for shared access.

2. In each instance, open the Categories table in Datasheet view.

3. In the first instance, change the Description field in the first record. Do not commit the change.

4. In the second instance, change the Description field in the first record. Commit the change by moving the pointer to the next record.

5. Switch to the first instance and try to commit the change. You receive the following error message:

         This record has been changed by another user since you started
         editing it. If you save the record, you will overwrite the
         changes the other user made.
   

6. Click Save Record. Note that you receive the following error message:

         Can't delete or change record. Since related records exist in table
         "<Table Name>", referential integrity rules would be violated.
   

REFERENCES