enforcing referential integrity with split db

[Grace]

Hello. I am in the process of designing a database where
the backend resides on a server. The tables are linked to
a frontend which resides on my hard drive. I have one
table that must reside in the front end for security
reasons. This table is "unlinked".

My question is this ... when setting up the relationship
for this "unlinked" table, I am unable to enforce
referential integrity. Is this due to the fact that the
table I am joining the "unlinked" table to resides in the
backend located on the server?

Thank you for your assistance.

 

[Allen Browne]

Access cannot enforce RI across tables that reside in different database
files. That makes sense when you realise that someone else may also have the
back end tables linked and *not* your front end, so it cannot enforce
integrity between tables that it is not even aware of.

You will need to write code to ensure the integrity of the data. Not too
difficult, but it does make you appreciate what that little check box does
for us.

 

[Jeff Boyce]

Grace

Yes. Given the potential variability (i.e., non-Access) of back-end
sources, Access would be hard-pressed to be able to enforce RI from a
front-end.

 

[Grace]

Thank you . That makes perfect sense.

Can you direct me to some assistance in writing the code
that will enforcie RI?

Thanks again ....

 

[Allen Browne]

Use the BeforeUpdate event of the *form* where the related records are
added/edited, and the Delete event of the form that handles the primary
records.

It's generally a matter of performing a DLookup(). It returns Null if the
expected value is not found. This example could be used in an Order form, to
check that the CustomerID is a valid customer:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant

With Me.CustomerID
If IsNull(.Value) Then
Cancel = True
MsgBox "Customer required."
Else
If .Value = .OldValue Then
'do nothing
Else
strWhere = "CustomerID = " & .Value
varResult = DLookup("CustomerID", "tblCustomer", strWhere)
If IsNull(varResult) Then
Cancel = True
MsgBox "Invalid customer"
End If
End If
End If
End With
End Sub

Note: If the "Do nothing" bit looks odd, bear in mind that the "Else" will
execute if Nulls are involved.

For help with DLookup() see:
http://allenbrowne.com/casu-07.html

 

[Grace]

Thanks Allen. I will try this code...

Have a nice day.

-----Original Message-----
Use the BeforeUpdate event of the *form* where the related records are
added/edited, and the Delete event of the form that handles the primary
records.

It's generally a matter of performing a DLookup(). It returns Null if the
expected value is not found. This example could be used in an Order form, to
check that the CustomerID is a valid customer:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant

With Me.CustomerID
If IsNull(.Value) Then
Cancel = True
MsgBox "Customer required."
Else
If .Value = .OldValue Then
'do nothing
Else
strWhere = "CustomerID = " & .Value
varResult = DLookup

("CustomerID", "tblCustomer", strWhere)