Referential Integrity Problem

[cqdigital]

I'm building a database that consists of frontend and backend. Some
of the lookup tables need to reside in the frontend database. The
data in the lookup fields that relate to the backend tables will never
change, but a descriptor for items in these fields will. I may need
to update the descriptors periodically, hence the need to keep these
tables in the frontend database so that the new descriptors will be
distributed with frontend updates.

Ideally the lookup fields should be linked to the backend tables with
referential integrity enforced. Because the tables are in 2 different
databases this can't be done.

What options do I have? Given that the lookup fields will always be
under my control and will never change, is enforcing referential
integrity really an issue?

 

[Tom Wussernark [MSFT]]

look buddy

linked tables suck a big fat cock

you should move to ADP if you want DRI

 

[John Spencer]

I would still put the tables in the backend and use VBA code routine to
update the table values when launching the (new) front end. Using the
database engine is the only way you can reliably enforce referential
integrity and in my opinion this is the best way to handle the situation.

Other options are careful design to make sure that the users can never enter
data directly into the tables (only through forms).
-- All forms must use comboboxes (or listboxes) for entering the data.
-- All data imports (if any) must carefully check the data to make sure
there is no invalid values.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Tony Toews [MVP]]

look buddy

linked tables suck a big fat cock

you should move to ADP if you want DRI

Note that this person is really A a r o n K e m p f and that he is not an employee
of Microsoft.

And needless to say no Microsoft employee would use such language.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Chris2]

I'm building a database that consists of frontend and backend. Some
of the lookup tables need to reside in the frontend database.
cqdigital,

Why?


I may need to update the descriptors periodically, hence
the need to keep these tables in the frontend database

This makes it seem like the data should be in the backend database and exist in the
frontend databases as linked tables . . .

so that the new descriptors will be distributed with frontend updates.

.. . . which would accomplish this without additional effort.

What options do I have? Given that the lookup fields will always be
under my control and will never change, is enforcing referential
integrity really an issue?

As long as instant updates are not necessary, you could think of putting code in each
frontend database that ran on open that checked for updates and copied them over.

Otherwise, you will need to use your backend to programmatically update all frontend
databases whenever you make a change. Ideally, this would always be run when no users are
accessing the backend.

(No referential integrity would be available in either case.)


Sincerely,

Chris O.