Referential Integrity Enforcement

[Sprinks]

I have several general-use tables that are in a "Common.mdb" database. A
front-end file will link tables from this database as well as tables
developed specifically for the application.

Can I enforce referential integrity between tables from these sources?

Sprinks

 

[Rick Brandt]

I have several general-use tables that are in a "Common.mdb"
database. A front-end file will link tables from this database as
well as tables developed specifically for the application.

Can I enforce referential integrity between tables from these sources?

Sprinks

No. RI can only be enforced between tables in the same MDB file.

 

[Sprinks]

Thanks, Rick.

No. RI can only be enforced between tables in the same MDB file.

 

[Jeff Boyce]

"Yes, but..."

Rick's response reflects what Access can do.

You asked if YOU could enforce RI <g>? Yes you can, but YOU have to do all
the coding and connecting to make sure ...

.... and there's a good reason why Access won't enforce RI between your
front-end table and a linked back-end table. Since the back-end table is
accessible by more than one application, how would/could Access enforce RI
if another front-end could have a different set of rules?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Sprinks]

Thanks, Jeff; that makes sense.

Sprinks

"Yes, but..."

Rick's response reflects what Access can do.

You asked if YOU could enforce RI <g>? Yes you can, but YOU have to do all
the coding and connecting to make sure ...

.... and there's a good reason why Access won't enforce RI between your
front-end table and a linked back-end table. Since the back-end table is
accessible by more than one application, how would/could Access enforce RI
if another front-end could have a different set of rules?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Sprinks]

Jeff,

I initially placed these "common" tables in their own .mdb since I had
created several conceptually different applications that each utilized these
tables. I recently combined several small utility .mdbs into a single
menu-driven application to simplify, and it occurs to me that I could really
do the same with the rest, which would enable me to have a single back-end
file where I could enforce referential integrity.

I wonder however, at what limit I might start adversely affecting
performance. My suspicion is, that being a small office of 15 employees, it
might not *ever* be an issue. Currently, the back-end files are all less
than 1 MB. Do you have any sense of this?

Thank you.

Sprinks

 

[Jeff Boyce]

Sprinks

I'm not sure what "sense of" I'm being asked for ...

15 folks simultaneously doing data entry against an Access/JET back-end
might be pushing it.

15 folks infrequently doing data lookup against ... are not an issue.

Some number (?2, ?5, ?10) of independent back-end dbs, each around 1 Mbyte
would present no particular performance issues, in my experience.
Access/JET back-end of 20-50 Mbytes are quite workable.

Several hundred this size would...

I'm guessing your situation is somewhere in the middle...<g>

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Sprinks]

Jeff,

Thanks for your opinion. Actually, we are toward the infrequent user end of
the spectrum. All 15 only enter a timesheet once a day. The other apps are
part-time amongst 6 of us.

Your answer took me back to when I was a Product Manager trying to envision,
define and develop new industrial products. I took great care in formulating
questions for our engineers, regardless of which the answer always was a form
of "It depends."

Sprinks