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

[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.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Heather" <ha******@cseducationalsystems.org> wrote in message
news:FM******************@newsread1.news.atl.earth link.net...

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

[Douglas J. Steele]

"Heather" <ha******@cseducationalsystems.org> wrote in message
news:FM******************@newsread1.news.atl.earth link.net...

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

[Heather]

Doug,

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

Thanks!

Heather
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:u$**************@TK2MSFTNGP10.phx.gbl...

"Heather" <ha******@cseducationalsystems.org> wrote in message
news:FM******************@newsread1.news.atl.earth link.net...

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

[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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

"Heather" <ha******@cseducationalsystems.org> wrote in message
news:TN*******************@newsread1.news.atl.eart hlink.net...

Doug,

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

Thanks!

Heather
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:u$**************@TK2MSFTNGP10.phx.gbl...

"Heather" <ha******@cseducationalsystems.org> wrote in message
news:FM******************@newsread1.news.atl.earth link.net...

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)