ACC1x: Relationships Between Attached Microsoft Access Tables

ID: Q109322

The information in this article applies to:

SUMMARY

Although relationships cannot be established between attached tables in Microsoft Access, referential integrity can be enforced if the attached tables reside in another Microsoft Access database, and if relationships have been established in that Microsoft Access database.


MORE INFORMATION

The following example demonstrates the use of referential integrity between attached Microsoft Access tables:

  1. Create a new database in Microsoft Access.


  2. From the File menu, choose Attach Table. Select Microsoft Access in the Data Source box, and then choose OK.


  3. In the File Name box, select the sample database NWIND.MDB, and then choose OK.


  4. Attach the Categories and Products tables, and then choose Close.


  5. Open the Categories table in Datasheet view.


  6. Try to delete any record in the table. You will receive the following error message:
    Can't delete or change record. Since related records exist in table 'Products', referential integrity rules would be violated.


  7. Open the Products table in Datasheet view.


  8. Try to add a record whose Category ID does not exist in the Categories table. You will receive the following error message:
    Can't add or change record. Referential integrity rules require a related record in table 'Categories.'


Note that you cannot define relationships for the attached tables in the Edit Relationships dialog box; the relationship must be established by a join in a query. If you do create a query using the attached tables, make sure that any join lines join fields with the same data type.

Additional query words: relate attach 


Keywords          : kbusage RltOthr 
Version           : 1.0 1.1
Platform          : WINDOWS 
Issue type        : kbinfo

Last Reviewed: March 30, 1999