Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    AccessXP, multiuser - Performance loss (Access XP)

    I have had an Access 97 based system running at a local firm for a number of years (recently on Windows XP based machines).
    They have just upgraded to a new server with the latest Windows / Exchange server versions. The firm that did the installation has also modified users settings on the local PC's.

    I have converted the Frontend to an Access XP based version (MDE) and changed the backend to Access XP version.
    Recordsets (apart from recordsetclone of forms) are as ADO recordsets.
    A typical example would be
    rs.open szSQL, currentproject.connection, adopenstatic, adlockoptimistic

    The first user logs into the system - bit sluggish but acceptable.
    The second user logs in and now has to wait twenty seconds before the startup form appears.

    I have also put the MDB version onto a PC to attempt to track the problem and found that opening a table (with one record which has one field -admin password) from the database view takes in excess of 4 seconds. Single stepping code shows that each call to open a recordset or use a dlookup takes many seconds.

    I have turned the subdatasheets option off on each table in the backend, to no effect.

    I am out of ideas as to what is so wrong with the performance. (On a PC here with Win XP I can open multiple users concurrently and it takes fractions of a second to start - as the Access 97 version used to do).
    What could be causing the problem? (IS it a known problem with the latest MS server?) What could I try to identify the true bottleneck?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: AccessXP, multiuser - Performance loss (Access XP)

    Have you turned off Name AutoCorrect in the General tab of Tools | Options...? Although, that should not make a difference in an MDE database, I think...

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

    Re: AccessXP, multiuser - Performance loss (Access XP)

    Have you placed the front-end on the workstation, or are all workstations hitting a single front-end on the server?
    Wendell

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AccessXP, multiuser - Performance loss (Access XP)

    No (or at least it is the default - must check).
    Frontend or backend or both?

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AccessXP, multiuser - Performance loss (Access XP)

    Yes, FE on workstations, BE on server.

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

    Re: AccessXP, multiuser - Performance loss (Access XP)

    OK - that eliminates that possibility. We've seen something rather similar a couple of times, and fought our way through it. One possibility has to do with the lack of a persistent connection to the backend, another is an interaction with security. Check out the performance tips at http://www.granite.ab.ca/access/performancefaq.htm. The one with the attempt to delete the LDB file sounds like it could be your issue.
    Wendell

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

    Re: AccessXP, multiuser - Performance loss (Access XP)

    Andy,

    Did you turn off subdatasheets on the tables, front and back end? They're on by default and I've found that they can cause serious delays in just opening a linked table in the database window! They especially sap performance if you are also using combobox lookup fields in your tables. With all those queries running, the system just seems to step all over itself. This can be true even with the front and back ends local on the same machine. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  8. #8
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AccessXP, multiuser - Performance loss (Access XP)

    I'd just done the BE. The FE has just a couple of 'trivial' tables, I'll turn it off on those and give it a try.
    I feel the DB is more or less in order as it works fine here (for 2 users) and performance (in real location) for the first user is OK, it's users 2, 3... that it deteriorates catastrophically.

  9. #9
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AccessXP, multiuser - Performance loss (Access XP)

    Network 'should' be OK. It worked fine for the Access 97 version on the old server. No hardware changes to switch/cables has been made, just new server/OP system, various patches installed and some changes (grrr..) on the local machines re user rights.

    I'll check the link out.

  10. #10
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AccessXP, multiuser - Performance loss (Access XP)

    Thanks - i've worked throught the list - those that I can influence.
    I've forced a connection to be permanent (bound form), checked things are turned off, replaced custom functions in queries (one) and changed dlookup/dmax to custom functions using recordsets.
    Starting for the second and other users is now acceptable (not as fast as with Access 97 but only 3-4 seconds).
    Using the interface doesn't seem to be any faster than before the changes. It's slower than Access 97 (ca 1 second where it took say 0.5 seconds) but it is usable.

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

    Re: AccessXP, multiuser - Performance loss (Access XP)

    Put some of the blame on unicode and some on the new VBE and the reworked query engine. Access 97 did NOT load all the modules immediately as 2000 and XP do and it handled optimization differently. Even in 97 multiple subforms loading when a form opened could slow the form down enormously, however. I make heavier use of saved queries in XP and make every effort to load subforms the first time they are needed, which speeds the opening of the form but delays the subform slightly.
    Charlotte

  12. #12
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AccessXP, multiuser - Performance loss (Access XP)

    There isn't much more I can optimise in the FE wrt. forms. Basically the 'normal' user just gets one form (with 4 sub forms) and then a simple print menu when they click a button.

    If you are interested it is example 1 on my home page. www.ainscow.ch

    The admin gets a different set of options but that is really just for control purposes.

Posting Permissions

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