Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Goose Creek, South Carolina, USA
    Posts
    108
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Frontend Content (Access 2k SR1A/Win98SE)

    Have built an app with around 20 tables and successfully split it into back end/front end. The front end goes on the individual workstations and the backend is on a server. Everything works fine.
    Here's my question: Some of the tables are used to populate combo boxes on entry forms. Some of them change (additions) and some don't. Would it be acceptable to include those tables that remain constant in the front end in order to reduce network overhead? I guess the question is: Is it ever acceptable practice to include reference tables in the front end?
    Have tried it out and it seems to work fine, but am leary about creating problems down the line.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Frontend Content (Access 2k SR1A/Win98SE)

    Dick
    I don't see any problem to transfer those tables to the front-end, if they don't change.
    Even if they change from time to time, you could copy the content of those tables from the back-end to the front-end with code or macro at the start-up of the application or on other regular basis.
    Francois

  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: Frontend Content (Access 2k SR1A/Win98SE)

    I don't think it is generally a good idea. The primary reason is that you lose the ability to create relationships with these tables enforcing referential integrity.

    Then there is the built-in logistics problem of how you update the tables and get new frontend updates to all users. Not difficult, just something else to go wrong.

    Beyond that, I'm not sure how much of a savings there would be. I suppose it would help if these were big tables, but in that case I'd suspect they would change frequently. Do you notice anything significant about the combo boxes based on these tables, like perhaps they are very slow? Do they all have a primary key? If you look at the values in the combo boxes in a different order than that of the primarykey, do you have such an index on the table?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Goose Creek, South Carolina, USA
    Posts
    108
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Frontend Content (Access 2k SR1A/Win98SE)

    Mark (et al):
    Thanks for the quick advice. Will probably abandon the idea, since these are all "small" tables. Have already discovered the 'referential integrity' factor, but things still worked.
    All tables have primary keys (autonumber); sql statements for cbo's have "ORDER BY" statements.
    You're right in that the 'larger' tables are the ones that change and would remain in the server back end.
    Logistics problem is met by placing a new SE zip front end file on the server, and having regional IT guys download it from WAN to LAN (even the users can get it from their individual office LANs).
    Having said all this, thanks again, and consider the idea abandoned.

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

    Re: Frontend Content (Access 2k SR1A/Win98SE)

    I once considered creating a global text string for each such lookup table when the database was opened. For example, the CustomerStatus lookup table might produce a string like "Active;Inactive;Credit Hold" or whatever. Then, I would use this as rowsource for a combo box, specifying the a Value List as the rowsourceType. This would have the added advantage of decreasing the number of open recordsets. This still might be useful for forms that have alot of subforms and combo, where it is taking a long time to open.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Goose Creek, South Carolina, USA
    Posts
    108
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Frontend Content (Access 2k SR1A/Win98SE)

    Thanks, Mark
    Up to this point there haven't been any problems with cbo response speed.
    Acutally, I was sitting here engrossed in the app on Sunday and suffered a momentary 'brain f__t'.
    The main slowdown occurs with entry and edit forms based on complex queries drawing from many tables.
    In general, is there an performance advantage to basing forms on such queries, or is it better practice to base forms on the tables underlying the queries?
    I'm one of those poor souls who is just beginning to get into vba since I've been too busy building an app that mostly works to spend the time learning vba.
    Obviously, if I had it to do over, etc, etc, ...

  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: Frontend Content (Access 2k SR1A/Win98SE)

    I usually try to have my forms based on a simple query that restricts the recordset to a single record at a time based on the primarykey field(s). For example, Access can quickly load a form for Invoice #12345, but it takes longer to load the whole Invoice table then look for #12345. I don't know why your queries are so complex, every situation is different. However, perhaps there are other ways of handling them. Maybe you could give an example?
    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
  •