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.
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
<cqdigital[ at ]volcanomail.com> wrote in message
news:1182838730.285639.282360[ at ]g37g2000prf.googlegroups.com...
> 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?