Can A Relationship Be Set Up Between Tables In Different MDB Files?

[Douglas J. Steele]

Is there a way to enforce referential integrity between tables in different .mdb
files and set up cascade delete and cascade update between the tables?

No, there isn't. You'll have to do it manually through code.

 

[Heather]

I am creating a database with a backend and frontend. The backend consists of a
Products table and many other tables. The products table in an .mdb file by
itself is over 2 meg. I thought about putting the products table in a file by
itself and all the other tables in another file. I anticipate there will be a
need for changes in the other tables and since communication is over the
internet, having the products table in a different file would make a smaller
file to email back and forth. I need to have referential integrity enforced
between the products table and other tables and I also need cascade update and
cascade delete. I linked the products table to the other backend file that
contained all the other tables. I then added the linked products table and the
other tables I needed to relate the products table to to the relationships
window. I then discovered I could create what seemed to be a relationship
between the linked products table and the other tables but could not enforce
referential integrity or set cascade delete or cascade update.

Is there a way to enforce referential integrity between tables in different .mdb
files and set up cascade delete and cascade update between the tables?

Thanks!

Heather

 

[Graham Mandeno]

Hi Heather

No sorry - this cannot be done.

However, you might consider not allowing deletes on your products table, and
perhaps adding an "Obsolete" field to mark those which have been removed
from stock. Surely you wouldn't want to cascade deletes anyway, as you
would then delete any related order/sales records when you remove a product
from your inventory. I can't see why it should be necessary to cascade
updates in a products table, as the primary key ought to be static.

Of course you would have live without enforced relational integrity.

 

[Heather]

Doug,

Could you give me an outline on how to do it through code.

Thanks!

Heather

 

[John Vinson]

I am creating a database with a backend and frontend. The backend consists of a
Products table and many other tables. The products table in an .mdb file by
itself is over 2 meg.

2 Mbyte is a very small database. Most of the production databases
that I work with run well over 10 MByte, 100 MByte is not at all
unusual; Access is limited to 2 GByte (2048 meg).

I thought about putting the products table in a file by
itself and all the other tables in another file. I anticipate there will be a
need for changes in the other tables and since communication is over the
internet, having the products table in a different file would make a smaller
file to email back and forth. I need to have referential integrity enforced
between the products table and other tables and I also need cascade update and
cascade delete. ...
Is there a way to enforce referential integrity between tables in different .mdb
files and set up cascade delete and cascade update between the tables?

No, there is not. Think about it: you have A.mdb over here, B.mdb over
there. You set up some constraints in A.mdb controlling what records
can and cannot be added to a table in B.mdb.

That's fine if you open A.mdb directly. But if I were to open B.mdb by
itself, or by linking from X.mdb, the rules in A.mdb aren't even
*visible*, much less enforcable.

Relational integrity can be enforced only within the database
containing the tables.

And don't worry about 2 MByte. Compact regularly, zip the file before
transmitting it over the internet, and you'll be fine.

 

[Douglas J. Steele]

What I meant was you'd have to write queries which would delete related
records from the second table when a record is deleted from the first. How
you do that depends on the specifics of the application.