ACC: Referential Integrity Error Message with Shared Databases

ID: Q132139

The information in this article applies to:

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.

After you click the OK button, you receive the following write-conflict 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.

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

If you choose Save Record, Microsoft Access does not update the record, and you receive the following referential integrity error message:

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.

MORE INFORMATION

Steps to Reproduce Problem

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

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.

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

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

In the second instance, change the Description field in the first record. Go back to the first instance and commit the change by moving the pointer to the next record. Then come back to the second instance and commit the change by moving the pointer to the next record. Note that 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.

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

For more information about Cascading Updates, search the Help Index for "Cascading Updates," or ask the Microsoft Access 97 Office Assistant.

Keywords : kberrmsg kbusage RltRef
Version : 2.0 7.0 97
Platform : WINDOWS
Issue type : kbbug