Referential Integrity

[JimS]

I have a back-end db where I store lots of tables. I join the PO table to the
PO Line table by the PO ID. Works fine, shows as a one-to-many relationship.
Why is the referential integrity box greyed out? I need to enforce
referential integrity.

 

[Jerry Whittle]

Is the PO ID field the primary key in at least one of those two tables? If
not, you can't enforce RI.

 

[John W. Vinson]

I have a back-end db where I store lots of tables. I join the PO table to the
PO Line table by the PO ID. Works fine, shows as a one-to-many relationship.
Why is the referential integrity box greyed out? I need to enforce
referential integrity.

Are you doing this join in the Backend (where you can enforce referential
integrity) or in the frontend (where you CANNOT do so)?

 

[JimS]

John,
You're half right. Turns out one of the tables is in the backend of
another db. I need to get them all together.

So what's the strategy. I need to combine three backend db's with all the
necessary relationship(s). I have maybe two dozen tables, split between two
back ends. I can handle, I think, the front end remapping, but the backend
movement means I have to rebuild all the relationships among all the tables.
Is there a more automated and less error-prone way to do this?

 

[John W. Vinson]

John,
You're half right. Turns out one of the tables is in the backend of
another db. I need to get them all together.

Indeed you do. Access cannot enforce referential integrity across multiple
databases.

So what's the strategy. I need to combine three backend db's with all the
necessary relationship(s). I have maybe two dozen tables, split between two
back ends. I can handle, I think, the front end remapping, but the backend
movement means I have to rebuild all the relationships among all the tables.
Is there a more automated and less error-prone way to do this?

Not that I can imagine. You'll need to import the tables, and create
relationships (where none can have existed before) between the tables which
were not previously related.