ACC95: Cannot Create Relationship with Local & Replicated Tables

• Microsoft Access version 7.0

SYMPTOMS

Advanced: Requires expert coding, interoperability, and multiuser skills.

When you try to create a relationship between two tables in a replicated database, you may receive the following error message:

   You can't establish or maintain a relationship between a replicated
   table and local table.

CAUSE

Microsoft Jet Replication does not allow you to create a relationship between two tables if the following conditions are true:

• One table is replicated and the second is local. (That is, the second table is not replicated; therefore, it is not dispersed throughout the replica set.)
• The two tables are local and related (through referential integrity) when you try to turn on the Replicable property for one of the tables.
• The two tables have different KeepLocal property settings.

RESOLUTION

If one table is replicated and the second is local, turn off the Replicable property of the replicated table. Then, you can try to create the relationship between the two local tables.

If two tables are local and related, when you try setting the Replicable property, delete the relationship first. Then, try to turn on the Replicable property for the two local tables.

If the two tables have different KeepLocal property settings, modify one table's KeepLocal property so that it is the same as the setting in other table. Then, try to create the relationship.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

1. In Windows Explorer, make a copy of the sample database Northwind.mdb and name it Test.mdb.

2. Start Microsoft Access and open the Test.mdb.

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

4. In the "This database must be closed before you can create a replica" box, click Yes.

5. In the "Microsoft Access can make a backup of your database before converting it to a Design Master" box, click Yes.

6. In the Location of New Replica box, select the same folder location as the Test.mdb database. In the File Name box, type "TestReplica.mdb" (without the quotation marks).

7. Click OK to create the replica.

8. In the Design Master database (Test.mdb) make a copy of the Customers table and name it Cust1.

9. On the Tools menu, click Relationships.

10. On the Relationships menu, click Show Table.

11. In the Show Table box, select Cust1. Click Add, and then click Close.

12. Drag the CustomerID field from the Cust1 table to the CustomerID field in Customers table.

13. In the Relationships box, select Enforce Referential Integrity, and then click Create. Note that you receive the following error message:

    You can't establish or maintain a relationship between a replicated table and local table.

REFERENCES

For more information about creating relationships, search on the phrase "relationship," and then view "Define relationships between tables" using the Answer Wizard from the Microsoft Access for Windows 95 Help menu.