Enforcing referential integrity across databases

[Daniel Jacobs]

Is it possible to enforce referential integrity across Access 2007 database
instances? i.e. can i create a foreign key relationship between table a in
one database to a linked table from another database so that changes and
deletions to the parent are reflected (cascaded) in the child table?

 

[Allen Browne]

No. Access cannot enforce RI for you across different database files.

You need to handle it yourself (by checking each time there is an insert,
edit, or deletion.)

 

[John W. Vinson]

Is it possible to enforce referential integrity across Access 2007 database
instances? i.e. can i create a foreign key relationship between table a in
one database to a linked table from another database so that changes and
deletions to the parent are reflected (cascaded) in the child table?

No.

The reason is simple: if you define a relationship in A.MDB (or .ACCDB or
whatever) affecting a table in B.MDB, there is no way to prevent someone from
opening B.MDB directly, or linking to it from X.MDB and making a change that
would violate the constraint. A.MDB isn't even open, so it has no way to
enforce the constraint.

 

[Keith Howard]

Thanks for the feedback so far.

Do you know where I could find sample code that does the referential
integrity enforcement for additions, edits, and deletions?

Thanks.

Keith Howards

 

[Allen Browne]

To manage the relationships manually:

a) For additions and edits in the main table, use DLookup() to see if the
value actually exists in the lookup table.

b) For deletions from the lookup value, block the deletion if the value
exists in the main table.