Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2010
    Posts
    6

    Unanswered: MS Access - multi-user, slow DB updates

    I have an app I created in MS Access that has only three tables. Each has no more than 400 records. I have three data management forms, each of which has some back-end VBA code running with it. Some is used to automatically replicate entries with new IDs (think "replicate #200 as 200A, 200B, 200C"), and some simply triggers calculations based on updates (keep running total, and alert when a threshold is reached). Also have some VBA macros for running batch processes for bulk printing. Inserts in VBA are run by basic DoCMD update queries, and DAO OpenRecordset's for select queries. It's a system I use once a year to track a charity event.

    Need to have three users access and update the database simultaneously. I'd swear it was working a few years and several updates ago. Last year, I noticed that updates to the DB from one machine would not show up on another machine for some time, and occasionally, not until the DB on the 2nd machine was closed and reopened.

    I recently tried doing a DB split, which seemed to help performance for the updates, but the inserts still do not take effect until I bounce the session. Cannot tell if it's the VBA code contributing to the lag or not.

    1) Is there a way to get over this performance issue?
    2) Does it seem that I need to keep the database split to front-end/back-end, or can I get away with keeping the DB intact for only three simultaneous users managing such a small data set?

    I saw a thread that had an "FAQ" for performance tuning, which unfortunately, didn't help much.

    Thanks in advance...

  2. #2
    Join Date
    Mar 2007
    Posts
    277
    Not having the db split to FE and BE in a multiuser situation is just courting disaster. Have you already looked at this link? Microsoft Access Performance FAQ
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  3. #3
    Join Date
    Oct 2009
    Posts
    340
    you've just got a few different things going on... one clearly is bulk printing - - that is going to slow down many PCs still today.

    Another is it sounds like you are relying on queries that are complex to do updates and when I read "replicate 200 as 200A, 200B,200C" I can only imagine what the code is for that type of task....complex queries get slower as the record sets get bigger.

    also it sounds like you need to refresh some forms....

    definitely you need to be split to be multiuser; don't expect to be bulk printing and doing other db work at the same PC, and probably you might want to fundamentally relook at the design to simpler forms/tables without complex action queries for updating info.

  4. #4
    Join Date
    Jan 2010
    Posts
    6
    Bulk printing: it's manually kicked off at the end of the night - looks for nonzero-balance users not flagged as "paid" (triggered update when an individual invoice is printed). Until then, it's not used at all. The duplicate-records code wasn't all that bad - if anyone's interested, I'll gladly paste it here.

    I've been doing this for about eight years now, and the earliest version doesn't have all that extra code. I brought that up on two PCs, and experienced the same data lags.

    Thanks,

  5. #5
    Join Date
    Mar 2007
    Posts
    277
    You didn't mention the version of Access you are currently using. Do you have all of the Service Packs and HotFixes applied?
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  6. #6
    Join Date
    Jan 2010
    Posts
    6
    MS Access 2003, SP2 (11.6566.6568)

  7. #7
    Join Date
    Mar 2007
    Posts
    277
    Access 2003 is up to SP3 and there is a HotFix for it too.
    Description of the Access 2003 post-Service Pack 3 hotfix package: December 18, 2007
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  8. #8
    Join Date
    Jan 2010
    Posts
    6

    Solution - link via drive mapping rather than UNC.

    First, thank you to those who posted responses.

    I discussed this with a friend who is an IT tech lead. He asked specifics on how I was linking between the front end and back end in Linked Table Manager. I mentioned the links being of the form, "\\servername\share\<subdirs>\database_be.mdb" . He advised me to map a drive to the share, rather than using the UNC in the path to the back end. On doing that, the response time improved dramatically. Updates that were taking as much as ten seconds showed up in 2-3, and updates that weren't showing up without reloading the other front end were showing up almost as quickly.

  9. #9
    Join Date
    Oct 2009
    Posts
    340
    do the forum a favor - post the explanation of your IT tech lead friend as to why this is faster.

    Nothing in Access changed. The path to the BE is a passive string of text within Access - it still connects to the BE via the same internal mechanism...but obviously the connectivity improved - the explanation on this would be interesting to hear.

  10. #10
    Join Date
    Mar 2007
    Posts
    277
    I believe the closer to the root, the faster it runs. Why, I have no idea!
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  11. #11
    Join Date
    Jan 2010
    Posts
    6
    I have a note out to my friend for a quick summary, but to attempt to paraphrase, the drive mapping sets up a reference in the OS, whereas locating the resource via UNC is a "look it up on the fly each time".

    I tried to do some research on your request for explanation, and came up with conflicting stories of which is more reliable (dropped drive mappings can be catastrophic on one hand, "what I'm seeing with my own eyes" on the other). One that I found quite useful was a very generic, "some applications just flat out don't work with UNCs, but work fine with drive letters".

    I will post my friend's reply when I hear back.

  12. #12
    Join Date
    Oct 2009
    Posts
    340
    As the Terry/friend advice is not standard Access speed improvement advice - - yet works - - then I presume it depends on the network set up or technology - - and remain very interested to know more about that if possible.

    RG's advice on keeping the BE file at the top of the root (rather than buried 10 folders down) is standard. I've never found it to help - but I think it is common sense and depends on if the server speed/load is the culprit vs the network.



    Terry's sounds like it involves the networking gear which is of interest to better understand.

  13. #13
    Join Date
    Jan 2010
    Posts
    6
    Two laptops with cat-5 connected thru a 4-port Linksys hub. Same configuration before and after using the drive mapping.

Tags for this Thread

Posting Permissions

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