[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

"Referential Integrity" between Linked Tables in Access 2007

Posted on 2010-04-05
5
Medium Priority
?
555 Views
Last Modified: 2013-11-29
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.
0
Comment
Question by:kirk0820
  • 4
5 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 29837445
"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
 
LVL 75
ID: 29837546
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
 
LVL 22

Expert Comment

by:dportas
ID: 29987416
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
 
LVL 75
ID: 29989804
"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
 
LVL 75
ID: 29989889
kirk0820 ... please remember to keep active in this Q ... as experts are donating their time to help ... You!

mx
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Implementing simple internal controls in the Microsoft Access application.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Get the source code for a fully functional Access application shell with several popular security features that Access VBA application developers desire, but find difficult or impossible to figure out how to code. You get the source code for managi…

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question