"Referential Integrity" between Linked Tables in Access 2007
Posted on 2010-04-05
I have two Access 2007 data tables. One contains employee number, name, dept. The other contains employee number, emergency phone, emergency contact relative... The employee number is set as a primary key in each of the two tables.
The data table with the emergency contact info in it is on a drive that only my office staff can access, and the other table with the employee name is on a drive that anyone in the organization can get to.
I've created a query which pulls the fields from both tables into one screen. The second table with the emergency contains a linked table to the first (employee) table. I believe the tables have a "one-to-one" relationship. I've set the "join" properties on the query between the two tables so that it only "includes rows where the joined field from both tables are equal". The joined field is of course employee number.
That's working well as far as I can tell. Just my office employees can get to the records in both tables, and people outside of my office in my organization can only get to the employee name table.
But then I stumbled across a great feature which would serve me perfectly for this arrangement: "referential integrity." But I've since discovered that I cannot seem to enable this feature I think because the two tables are in separate databases and the second emergency contact info table only contains a "linked" table to the first employee name table.
I wanted to verify that it it is in fact impossible for me to enable this built-in "referential integrity" feature. I see that it's not allowed on linked tables. However, frustratingly piquant, I keep seeing reference to the possibility of enabling this if both tables are in Access and I open the database which contains the linked tables. Here's one quote from an Access 2000 book that I have:
"REFERENTIAL INTEGRITY....you must make sure that the following conditions are met...Both tables are in the same access database. You can set referential integrity between linked tables providing they are both in Access format and you open the database that contains the linked tables."
So close, but either I misunderstand the above or I just don't know how to make it work.
Thanx in advance for any help.