Referential integrity in a split back end

Question

How do you enforce referential integrity when the parent record is in a table in one accdb (be_MainData.accdb) file and the child record is in a table in a different accdb file (be_SubData.accdb)?

Answer 1

Scott, what you've shown implements Cascade Deletes, which is a good thing and is part of RI. But it's only a piece of the puzzle! You also need to check that deletions from a parent table don't leave orphan records in a child table, and error out the deletion if so; you need to ensure that adding, or changing the foreign key value, in a child record is only allowed with a valid parent record; and so on. It can all be done but the effort required really makes you appreciate a working RI system!

KarenAnne, why the two backends? What benefit are you getting from the extra complication?

Answer 2

There are many ways. As an example. Lets say you have a student database with a grades table. Now the student drops out so you want to delete his record and also his grades. So you have a delete student button. Behind the button you have code like:

Dim strSQL as String

strSQL = "DELETE * FROM tblGrades WHERE StudentID = " & Me.StudentID & ";"

CurrentDB.Execute strSQL, dbFailOnError

strSQL = "DELETE * FROM tblStudents WHERE StudentID = & Me.StudentID & ";"

CurrentDB.Execute strSQL, dbFailOnError

Me.Requery

Answer 3

You can't, other than with VBA code in the data entry forms or with Before Insert Data Macros in the tables (and I'm not even sure if that will work). Referential integrity itself only applies within one database.

Answer 4

Hmm, can you give me a thought as to how I would attack this?

Answer 5

You have to enforce RI through your user interface.