Referential Integrity Using Linked Tables

[Guest]

I have a 'FacilityInfo' 2003 Access database that contains a single table
'tblFacilityInfo'. This database/table centralizes various information about
our facilities.

Several other 2003 Access databases tap into this centralized data via
linked tables. I'd like to set referential integrity in these other
databases such that if one tried to enter a record with a FacilityID that
wasn't in the linked table 'tblFacilityInfo', it'd be rejected.

According to the Access 2003 Help topic "About relationships in an Access
database (MDB)" in the section entitled "Referential Integrity":

"If the tables are linked tables, they must be tables in Microsoft
Access format,
and you must open the database in which they are stored to set
referential
integrity."

I've done this, but I still can't enforce referential integrity. Is this a
bug in Access? Am I misunderstanding the Help text? Is Help incorrect?

Your help is greatly appreciated! Richard

 

[Graham Mandeno]

Hi Richard

Unfortunately you cannot set RI between tables in different databases. The
best you can do is make sure that you have robust code to ensure that only
valid values are entered into foreigh key fields. Combo boxes on data entry
forms are a very good tool for this.

Of course, a problem arises when you wish to delete records from your
reference table. You can check that there are no related records in any
tables that are linked to your current front end, but you have no way to
check that there are no related records in tables in other databases.

 

[Brendan Reynolds]

The Jet database engine can only enforce referential integrity between
tables in the same database. You cannot enforce referential integrity at
this level between tables in different databases. You can, of course, take
steps within your application to prevent users from violating referential
integrity, as long as you do not allow users direct access to tables.

 

[Jamie Collins]

do not allow users direct access to tables

Short of uninstalling MDAC <g>, how is this best achieved?

Thanks,
Jamie.

--

 

[Guest]

Hi, Richard

You can have a sort of RI, even when you entering records directely to a
table.
Under design mode of the table that you want the RI take effect, on the
FacilityID field change the option to show the field as a combobox an in the
control source use a SQL string to show only that fiel that cames from the
linked table, an as i sayed change the option LimitToList Property to yes.

I hope that this help you

 

[Jamie Collins]

You can have a sort of RI, even when you entering records (directly) to a
table.
Under design mode of the table that you want the RI take effect, on the
FacilityID field change the option to show the field as a combobox an in the
control source use a SQL string to show only that fiel that cames from the
linked table, an as i sayed change the option LimitToList Property to

yes.

What to you mean by 'entering records directly'? Does your fix prevent
the following direct change:

Sub MyExcelMacro()
Dim Con As Object
Set Con = CreateObject("ADODB.Connection")
Con.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Test_Secured_4.mdb;" & _
"Jet OLEDB:System Database=C:\System_Secured_4.mdw;" & _
"User ID=Jamie;Password=a;"
Con.Execute _
"INSERT INTO Table_With_No_DRI" & _
" VALUES ('Inadvertent orphan value here');"
End Sub

This must be a common issue for MS Access applications that don't use
DRI and other constraints in the database layer for whatever reason.
Brendan? Anyone?

Many thanks,
Jamie.

--

 

[Guest]

What an excellent idea! I'll give it a try ...

Richard

============================================

 

[Guest]

Hi Jamie!

Good point. I'll try it out and let you know.

BTW, in my case, the users tend to use the database either directly (it's
how someone else designed the database ) or via MS-Access forms (which tend
to limit what users can do). Thus, Nuno's suggestion probably hits the 99%
solution level for my purposes. At the same time, your question is well
taken -- my level of comfort is also cranked at "iron clad", 'cause that 1%
has a nasty habit of reaching out and biting you!

Thanks for the input!

Richard


P.S. -- given the stunning lack of anything to the contrary, I take it that
the Microsoft Access documentation is -- gasp! -- in error ...