[AccessD] Managing Lookup Tables

Hi Arthur

I've never understood the concept of "lookup" tables. To me data are
either user/organization specific or application specific. The
idea of tables with "data that never changes" like postal codes (which
do change) is doomed to fail.

Application specific data that change only by a version change can be
held within the application itself. In some cases the application
specific data can change more often or may be independent of the
application version. One example of this is an application we build
for handling payments and transfer to the client's on-line banking
system. Here we have six different systems used be many hundreds of
banks. The mix of banks and systems and im/export routines changes
rarely but will change over the years and banks will open, close or
merge. We chose to put this list in a separate write protected
database named ...sys.mda to be put in the same folder as the
application which automatically relinks the tables at launch. It is
only used as a convenience for the user who now only has to pick the
bank(s) he/she uses, then everything else is set right for the
application. 

This system allows us to send an update for either the system database
or the application or both. The about box in the application tells, of
course, the version numbers of the app and of the sysbase.

The user only needs to backup the true database backend.

If this approach is what you have in mind, I can recommend it.

/gustav


> I have been trying to devise a cool way of managing lookup tables v.
> "data" tables. I.e. PaymentTypes v. Payments. It occurred to me that I
> could maybe link to two different back ends, one containing only the
> lookup tables and the other containing only the data tables. What I
> don't like about this approach is that I tend to define everything I can
> at the table level -- Caption, lookup, validation criteria, etc. etc.. I
> know this is controversial, and I don't want to rekindle those threads.
> What I am wondering instead is, Suppose I link the front end to the data
> database, and then link it in turn to the lookups database. I'm going to
> build 3 toy databases to check this out, but I'm wondering if anyone can
> see any significant problems in this approach.

> My basic reason for wanting to do this is so that I can freely nuke all
> the data in the data database, while preserving all the data in the
> lookup database.

> Insights, advice, and cash donations appreciated :)
> Arthur