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.

 

[elsamiro]

splitting is easy, good design is tricky

[font=Verdana, Arial, Helvetica]I have a DB design question. I will describe the actual design, and
hopefully someone will give me a clue on how to design it correctly.

[/font]

[font=Verdana, Arial, Helvetica] Frontend: [/font]
[font=Verdana, Arial, Helvetica] With all the usual content, except tables. Will be used by multiple users [/font]
[font=Verdana, Arial, Helvetica] at the same time. [/font]
[font=Verdana, Arial, Helvetica] [/font]
[font=Verdana, Arial, Helvetica] Backend: [/font]
[font=Verdana, Arial, Helvetica] All the tables, related to each other with referential integrity (updates, [/font]
[font=Verdana, Arial, Helvetica] deletes) [/font]
[font=Verdana, Arial, Helvetica] [/font]
[font=Verdana, Arial, Helvetica] ContainerDB: [/font]
[font=Verdana, Arial, Helvetica] Containing all the data. Per customer/year 3 tables. (Axxxx,Bxxxx,Cxxxx) [/font]
​

[font=Verdana, Arial, Helvetica]
---

problem1:
If I link the containerDB's tables i want to work on into the backend, i
loose the referential integrity. RI doesn't work on linked tables.

problem2:
Else when I copy the tables data (Axxxx,Bxxxx,Cxxxx) to "work tables"
(Aworktable,Bworktable,..) RI should work, multiple users cannot use the
frontend at the same time they would overwrite each others work. Also data
is not updated realtime in the containerDB, which is desirable.

--

Is there a better design possible, or is the only way to manually do referential integrity ?


ps: i crossposted this here from [/font][font=Verdana, Arial, Helvetica]http://www.microsoft.com/office/community/[/font][font=Verdana, Arial, Helvetica] Discussion Groups since I went there after going from here to allenbrowne. Anyway thanks.

[/font]