Linked tables in different mdbs

[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

 

[Duane Hookom]

You can't create referential integrity between more than one MDB. You may
need to write some code in forms to do the updating.

 

[John Vinson]

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 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]

 

[Kathy Webster]

Not the answer I was hoping for, but thanks!

You can't create referential integrity between more than one MDB. You may
need to write some code in forms to do the updating.

--
Duane Hookom
MS Access MVP

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