"Referential Integrity" between Linked Tables in Access 2007

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.
kirk0820Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"But I've since discovered that I cannot seem to enable this feature I think because the two tables are in separate databases "
That is correct.

"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.""

Well, that is a tad misleading, but 'technically' correct I suppose.  It's the term 'linked' that is confusing, since linked implies the tables are not *in* the local database ... where you are probably trying to establish RI.  However, the 'open the database' part does clear it up a bit.

In a local MDB ... which has Linked tables, you can technically create a 'Relationship' ... wherein a line is drawn between two fields in the two tables ... and this 'relationship' will appear in MSysRelationships in the local table.  However, you cannot establish RI in this case, thus rendering the relationship pretty much useless.

mx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
In the same vein, you cannot create a Relationship between two (non-linked) tables in separate MDB ...ie across two different back ends ... both of which may have tables that are linked to from a common front end.

mx
0
dportasCommented:
Most SQL-based DBMSs make it impossible to enforce a mandatory one-to-one relationship between two tables even in the same database. It's not a problem unique to Jet. One side of the constraint always has to be optional, ie: 1 to 0/1 rather than 1 to 1. In many cases it will make sense to combine the data into one table instead.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"in many cases it will make sense to combine the data into one table instead."
Absolutely.  In fact, in almost any case I can think of, there is no need for a 1-1 relationship. And if someone 'thinks' there is, then I'm guessing there are some design issues.

One possibly exception might be ... a portion of the fields that 'might' be all in one table ... might contain sensitive data such as employee SSN, pay, etc ... and you 'might' want a separate table for that ... but, unless you secure that table with ULS (gone as of A2007) ... then it still doesn't really make sense.

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
kirk0820 ... please remember to keep active in this Q ... as experts are donating their time to help ... You!

mx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.