Referential Integrity Enforcement

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Sprinks said:
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
Click to expand...

No. RI can only be enforced between tables in the same MDB file.
 
"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
 
Thanks, Jeff; that makes sense.

Sprinks

Jeff Boyce said:
"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
Click to expand...
 
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
 
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
 
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
 
Back
Top