ACC: Cascade Deletes May Cause Err Msg in Synchronized Replica

• Microsoft Access versions 7.0, 97

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

When you open a database after synchronizing it with another member of the replica set, you see a replication data error because an Insert operation failed due to referential integrity violations. You see one of the following errors:

In Microsoft Access 97

   You can't add or change a record because a related record is required
   in table '<tablename>'.

In Microsoft Access 7.0

   Can't add or change record. Referential integrity rules require a
   related record in table '<tablename>'.

CAUSE

The following events occurred:

• A record was deleted from a primary table in one database in the replica set, while a record was added to a related table in another database in the set.
• The relationship between the primary and related tables enforces cascading deletes.
• The synchronization was started from the database replica where the related table record was added.

RESOLUTION

If the relationship between primary and related tables enforces cascading deletes, then the next time you synchronize the two databases, the error will resolve itself.

MORE INFORMATION

When you create a relationship that enforces cascading deletes, all records from the many-side table in the relationship are deleted when you delete the related record from the one-side table. However, with replicated databases, it is possible to add a new record in one database after its related record in the primary table has been deleted from another database. When this happens, it may take two synchronization events to delete the records from both databases and clear the replication errors that occur.

For example, you have two databases in a replica set: a Design Master and a Replica. The databases have a one-to-many relationship between the Customers and Orders tables. The relationship enforces cascading deletes. You delete a customer from the Design Master, and all orders for that customer are deleted as well. At the same time, someone adds an order for that customer in the Replica database. The result when these two databases are synchronized differs depending on which database starts the synchronization.

Design Master Synchronizes with Replica

When the Design Master starts synchronization with the Replica, it sends its changes to the Replica first. The customer and all related orders were deleted from the Design Master, so the customer and all related orders are deleted from the Replica, including the new order that was added to the Replica. The result is that no replication data errors occur because the order was deleted from the Replica before it could be added to the Design Master.

Replica Synchronizes with Design Master

When the Replica starts synchronization with the Design Master, it sends its changes to the Design Master first. The Replica tries to insert a new order in the Design Master, but since the related customer record was deleted from the Design Master, a referential integrity error is entered in the MSysErrors system table in both databases. Then the Design Master sends its updates to the Replica, and the customer and all related orders are deleted from the Replica, including the order that caused the referential integrity error. The result is that both the Design Master and the Replica are fully synchronized, but there is a referential integrity error that you see the next time you open either database. If you synchronize again, the error is automatically removed because the record that caused the error no longer exists.

Steps to Reproduce Behavior

Creating the Replica Set:

1. Copy the sample database Northwind.mdb to NWDesignMaster.mdb.

2. Open the NWDesignMaster.mdb database.

3. On the Tools menu, click Relationships.

4. Double-click the join line between the Customers and Orders tables to open the Relationships dialog box.

5. In the Relationships dialog box, click Cascade Delete Related Records, and then click OK.

   NOTE: The relationship between Orders and Order Details already enforces cascading deletes, so it is not necessary to edit that relationship.

6. Close the Relationships window.

7. On the Tools menu, point to Replication, and then click Create Replica.

8. Click Yes when you see the warning about closing the database before you create a replica.

9. Because you are already working with a copy of the database, click No when you see the message about making a backup of your database before you convert it.

10. In the "Location of New Replica" dialog box, name the replica

        NWReplica.mdb, and then click OK.
    

11. After the new replica is created, the NWDesignMaster database opens

        again automatically.
    

1. In the NWDesignMaster database, open the Customers table.

2. Delete the record for Alfreds Futterkiste, which is CustomerID ALFKI, and notice that related records in the Orders and Order Details tables are deleted also.

3. Close the NWDesignMaster database, and then open the NWReplica.mdb database.

4. Open the Orders form and add a new record for Alfreds Futterkiste. Select Alfreds Futterkiste in the Bill To field, and any product in the Product field.

5. Close the Orders form.

6. On the Tools menu, point to Replication, and then click Synchronize Now.

7. Select NWDesignMaster.mdb in the Synchronize With box, and then click OK.

8. When you see the message that synchronization was completed successfully, click Yes to close and reopen the database.

9. When the database reopens, you see a message about errors from synchronizing changes with another member of the replica set; click Yes to see the errors now.

10. In the Resolve Replication Conflicts dialog box, click View Data

        Errors.
    

11. Note that there are two errors in the Replication Data Errors dialog

        box. Both errors are failed Insert operations due to referential
        integrity rules. One record failed to insert into the Orders table,
        and another record failed to insert into the Order Details table.
        However, if you look in either the NWDesignMaster or NWReplica
        databases, the conflicting records do not exist.
    

12. Repeat steps 6-8. Note that when the database reopens, the errors

        are gone.
    

REFERENCES

For more information about referential integrity and the relationships between tables, search the Help Index for "referential integrity," or ask the Microsoft Access 97 Office Assistant.

For more information about obtaining a copy of the Microsoft Jet Database Replication white paper, which contains more information about replication features in Microsoft Access, please see the following articles here in the Microsoft Knowledge Base: