Referential Integrity - Linked Tables

[Binnit]

Can it be done??
The Access97 help states that to set RI the following condition must apply

Both tables belong to the same Microsoft Access database. 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. Referential integrity can't be enforced for linked tables from databases in other formats.

I have 2 standard A97 databases and wish to link a table from one to other, no problem there, however, when I try to set RI on the linked table it will only create a standard relationship without RI. As per the help notes, the originating db is open in design mode when trying this and the relevant fields are of the same datatype etc

Any clues as to why this is not working for me appreciated.
Thanks

If IT ain’t working Binnit and Reboot

 

[KenReay]

Hi

You quoted the answer yourself "and you must open the database in which they are stored to set referential ", both tables must be physically in the same mdb, yours are in two seperate mdb's

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Binnit]

Ken
Thanks but I'm confused, I thought the whole purpose of linking tables was to have them in separate locations and schema's, why would you want to link tables in the same database?

I have db1 with a table called tblBroker
db2 has a link through to db1 tblBroker

db2 also has other tables that I wish to enforce RI against the Pkey in tblBroker but I cannot do this.

Am I missing something fundamental?



If IT ain’t working Binnit and Reboot

 

[KenReay]

Hi

We are clearly at cross purposes here.

Linking and making a referential integrity relationship are not the same thing

I am talking of making a referential integrity relationship, I thought you were too. All I am saying is that in A97 to make a RI relationship, both tables must be physically in the same mdb file.

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Binnit]

Ken
Thanks again, the A97 help says:-

Both tables belong to the same Microsoft Access database.
(ok thats fine)

the exception would appear to be:-

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. Referential integrity can't be enforced for linked tables from databases in other formats.

This is the bit that I have tried but cannot get to work, am I misunderstanding its meaning?

I have copied the original table and pasted in to db2 and successfully created the RI so I know that the table format and contents are OK. Just cannot do it when linked!




If IT ain’t working Binnit and Reboot

 

[KenReay]

Hi

Sorry, it is probably my lack of ability to explain

You can only make a RI relationship between tables which are STORED in the same mdb. A linked table is not stored in the mdb in which it is linked, in that mdb there is simply a pointer to the 'home' mdb.

Has that clarified it?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK