placing lookup tables in front end

[RWilly]

I'm presently supporting an Access application at about 30 remote locations,
in addition to my "real" job. At present all lookup tables are in the back
end, along with client, customer, and local district data. I'm finding that I
have to update lookup tables fairly regularly. New versions of our
assessments and inspections are developed every 6 months or so, each of which
is linked to about 100 topics and specific practices, none of which are ever
in the same order, etc, etc.

I can add this new information to the back end tables through code and a
start up form (e.g., "Click on this button to add latest versions"). It would
just be easier, and probably more reliable, to have this information in the
front end, which I could then e-mail to users to overwrite or replace their
current front end.

I'd appreciate any input on this.

 

[tina]

you can put look-up tables in the FE database, if it suits your purpose. but
there are some trade-offs. you can't enforce referential integrity between
the look-up tables and the data tables that they support. you can employ
various tricks in the user interface to mostly cover that hole in the data
integrity safeguards. but if somebody else works on the either the BE or FE
database in the future, they may not be aware of the need to build in those
fail-safes (not to mention the fact that you could forget to do it yourself
at some point); and if someone connects to your data tables from "outside",
there is no way to enforce referential integrity at the table level - so the
data is completely exposed to being screwed up.

hth

 

[Jim K Access Guy]

I've done this for performance reasons too. One way to do it is to put a
Settings table in both the front end and back end (localSetting ,
serverSettings). You can place a version number in the settings table on the
server. If the localSettings (i.e, the version from the user's hard drive) is
not at that version, it forces a make table query for each front end lookup
table. You can get more elaborate , for example, triggers on the back end
table that show timestamps of last update, then update all existing items,
then insert any new records. The settings tables could contain table names
and their verisons so you can only update the tables that have changes. This
can be done when the database is opened so the user cannot control it.


Just some thoughts....

 

[RWilly]

Thanks for the replies. I'll stay with my prsent design for now, as
maintaining ref. integrity is more important than ease of updating.

Regards