Force referential integrity for linked tables

  • Thread starter Thread starter Bill Reed via AccessMonster.com
  • Start date Start date
B

Bill Reed via AccessMonster.com

Is there a way to impose referential integrity on foreign key fields in a
local table that is compiled from primary keys in linked tables?
 
RI can only be enforced in the same file.

So, you can enforce RI in tables in the front end, and you can enforce RI in
the tables in the back end.

However, you cannot enforce RI between two different mdb files (eg: from
front end to back end)

So, for example I have a mdb file on my computer, and if you change settings
on your mdb file, you can't enforce RI on my computer, or perhaps someone
sitting in the library in UCLA. So, different files spread all over the
world, or even spread all over your computer cannot have RI enforced.

The tables have to be in the same mdb for this to work.
 
I understand that. What I mean is how do I execute a work-around that
accomplishes the same thing?
 
You simply have to write code. In fact, it often turns out that despite
having RI enabled, you wind up writing a lot code anyway.

And, believe it not, there is a good number of developers that I respect
that do NOT rely on RI, but ALWAYS write their own code. And, further,
commercial products like the very popular simply accounting is actually a
ms-access file (you can open simply accounting fields with ms-access). So,
Simply Accounting while a ms-access file did NOT rely on RI..but actually
wrote their own code.

So, the simply answer is that a good many developers and commercial
products based on the ms-access format do indeed write their own RI.

An good example would be cascade deletes. I always written my own delete
routines (but, I do use RI and cascade deletes in my applications). However,
lets assume you have to roll your own delete code. You also of course has to
assume form level code. So, the code behind the button to delete the record
could be:

dim strSql as string
dim lngID as string

lngID = me.ContactID
strSql = "delete * from Contacts where Contactid = " & lngID

If MsgBox("Do you really want to delete this record" & vbCrLf & _
" (you can not un-do this operaton)", _
vbCritical + vbYesNo + vbDefaultButton2, "Delete record") =
vbYes Then
CurrentDb.Execute strSql
' now child reocrds

strSql = "delete * from tblInvoices where ContactID = " & lngID
CurrentDB.Execute strSql

DoCmd.Close acForm, Me.Name
end if
 
Great! Thank you. It's kind of what I had envisioned but wanted reassurance
from someone who's actually done it.

Bill
 
Back
Top