Linked tables in different mdbs

  • Thread starter Thread starter Kathy Webster
  • Start date Start date
K

Kathy Webster

I have a database with its linked tables in 2 different mdbs. I need to
define referential integrity, since the records are linked by a [FileNumber]
field. If the file number is changed in the "master" database, it needs to
cascade and updated related records in the "child" database. I notice the
option is greyed out when I go to Relationships. How can I accomplish this?

TIA,
Kathy
 
You can't create referential integrity between more than one MDB. You may
need to write some code in forms to do the updating.
 
I have a database with its linked tables in 2 different mdbs. I need to
define referential integrity, since the records are linked by a [FileNumber]
field. If the file number is changed in the "master" database, it needs to
cascade and updated related records in the "child" database. I notice the
option is greyed out when I go to Relationships. How can I accomplish this?

TIA,
Kathy
Click to expand...

You cannot, at least not within Access.

Think about it: suppose you define a rule in Master. Somebody opens
Child (either by itself, or from some new frontend) and makes a change
which violates that rule.

There is no way that the rule you have defined in Master can be
checked, since *Master isn't even open*!

You'll just need to use Access security to prevent either backend from
being edited except from your frontend, and use VBA code in
appropriate form events to enforce the referential integrity yourself.
It cannot be done at the table level.

John W. Vinson[MVP]
 
Back
Top