Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Sep 2003
    Location
    Auckland, New Zealand
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Where to put LookUp Tables (2000 SP3)

    I need some advice.

    I've got a fairly normal Access 2000 application on a client's site. It runs across a 100x LAN to a dual-processor server. The BE resides on the server and the FE (mde) is on each workstation. The FE is kept up-to-date automatically - the 'modAutoexec' checks its own version number against the BE and pulls any new release down from a 'master' FE held on the server. This all works fine.

    In setting up the database, I used small 'look-up' tables to hold the elements of various comboboxes and lists. There are now about 30 of these - with from 2 to about 20 records - all with autonumber ID primary indexes; and they don't change much or often - as was planned. I am pleased with the benefits that I get from this approach.

    To reduce network traffic, I put these tblLkUps in the FE. As long as only I (the developer) wanted to make any changes to them, this was OK. I'd modify the FE mdb, build & deploy a new 'master' mde and the workstations would automatically refresh themselves when next they started up.

    Now the problem <grin>. The customer would like to be able to tweak the contents of these tables. Mostly just changing the wording, sometimes adding a record or changing the sort order (one of the fields). They'll accept that deleting a record is major surgery.

    Seems I have a number of approaches:

    (a) Teach the client how to build & deploy mde files.
    Hmmm, currently I _KNOW_ the FE code is my own.

    ([img]/forums/images/smilies/cool.gif[/img] Move them all to the BE.
    Hmmm, must then live with slower perfomance.

    Leave them in the FE and try to update them at startup.
    Possibly using inert 'master' copies stored in the BE ?
    Hmmm, this is tricky because of the auto-numbering.
    It'd screw up the ID numbers, if I simply wipe a local
    table's records and then re-build them from a master table.

    (d) As above, but 'special-case' each table and copy each
    field in each record - leaving the ID numbers alone and
    simply adding any new records from the 'master'. The new
    ID numbers should always remain in sync.
    Hmmm, it's pretty ugly - not really highly modular code.

    (e) Move them to a third mdb - call it an ME (mid-end) - held
    on each WS.
    A 'master' copy of the ME would be held on the server.
    This would be version-numbered, modifyable by the user,
    auto-copied to the WS at startup, like the FE itself.
    Hmmm, this looks like the best - but is probably the most
    work. Funny that.

    Any bright ideas on the above or insights on a really good approach ?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Where to put LookUp Tables (2000 SP3)

    I've never found a really good way to address this and it probably depends on how heavy the use of the lookups is. If you didn't go crazy with a lot of lookups in the tables themselves, you could find some alternative ways of populating the comboboxes and lists. I have a couple of demos at this site that might give you some ideas. Look at the No Tables Sample Database, which shows how to work with ADO connections to retrieve data for lists. The Persisted Recordset demo might give you some ideas, but persisted recordsets don't provide for any real "merge" capability between machines, although they can certainly be exchanged between machines.
    Charlotte

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Where to put LookUp Tables (2000 SP3)

    Quite frankly, I'd be really surprised if putting them in the BE and linking to them would impact your performance that much, if at all (given how small they are); and I'm not sure it is even worth the time to create an FE alternative. But that's my opinion. One advantage is that it does give you the ability to enforce referential integrity between each of these tables and the other tables.

    If you really felt strong about having them in the FE for performance, I'd then opt to re-initialize the data in each such table by perhaps doing a delete query and then an append query.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Where to put LookUp Tables (2000 SP3)

    Our experience coincides with what Mark suggests - put them in the back-end and don't sweat it. It does take a bit of time at start-up, but once you've done that, it lets you enforce referential integrity, and the ongoing performance penalty is negligible, since for tables that small, Access loads the whole thing into memory.
    Wendell

  5. #5
    New Lounger
    Join Date
    Sep 2003
    Location
    Auckland, New Zealand
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Where to put LookUp Tables (2000 SP3)

    Charlotte:
    Many thanks for the suggestions - I will look at the examples you suggest.

  6. #6
    New Lounger
    Join Date
    Sep 2003
    Location
    Auckland, New Zealand
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Where to put LookUp Tables (2000 SP3)

    Mark & Wendell

    Thanks for the advice. I guess I should never have just assumed that the performance hit would be significant.
    I'd better activate the stopwatch & logfile on the startup sequence and try it both ways - look-up tables in BE & FE.

    Mark - I didn't like the approach 're-initialize the data in each such table by perhaps doing a delete query and then an append query' because I couldn't see how to do it without upsetting the ID auto-numbers (unless I somehow force a compact) and thereby completely screwing up the relationships.

    Wendell - I agree on the benefits of referential integrity, but I'm challenged by the suggestion of Access holding small tables in memory. I'd like it to be true in this instance, but I had imagined that Access must at least check a 'dirty' flag of some kind before using the contents of a cached table (eg: to build a query for a combo-box as you open a form for the nth time). How would it 'know' that the cached table was up-to-date ?

    One of the app's main forms has about 18 combo-boxes (on various tabs, admittedly) and I can see that generating some traffic on the LAN each time the form opens. Maybe it'll be insignificant. - I'll do my homework, as described above <grin>.

    Thanks for considering the problem and for your thoughts on it.

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Where to put LookUp Tables (2000 SP3)

    You wrote:
    >>I didn't like the approach 're-initialize the data in each such table by perhaps doing a delete query and then an append query' because I couldn't see how to do it without upsetting the ID auto-numbers (unless I somehow force a compact) and thereby completely screwing up the relationships.<<


    Paul,

    When doing the append query, you append the entire record; that is, all fields including the ID field. No new ID # is assigned. But I still think this is unnecessary and not worth the effort.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •