Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2011
    Posts
    11

    Unanswered: using access over very slow network

    Hi,

    I'm using a split Access database (.mdb format). When I run it locally, it performs very quickly. But when I have the back end on my company's remote server, and run the front end on my local workstation, it performs slowly--the main startup form takes 3+ minutes to load, and most queries take a minute or two to run. Not good.

    I clocked my network connection at speedtest:

    ping: 73 ms
    download: 0.29 Mbps
    upload: 0.70 Mbps

    Is there anything I can do to improve performance, given this slow network?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    consider caching non volatile data in the front end. you cna do this on startup (most users are quite happy to wait say 5 minutes on application start up)
    redesign the system to use unbound controls
    be very careful when using combo and list boxes, make certain you only pull the data you need when you need it.
    one of the things to be wary of when using plain vanilla access is that you loose control of what happens and when. that means sometimes Access pulls the same data across the network multiple times before the form actually loads and displays.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2009
    Posts
    340
    Access simply does not run (well) if the FE & BE are not on a fast LAN....or a sufficiently highpowered WAN that emulates a LAN (i.e. terminal services)

    There really is no fix.

    What you might do/consider - is run it in the local office LAN so that it is fast - and set up an autobackup that copies the BE file to the remote server at night....so that you meet the criteria of your organization in putting the data on an official server while at the same time running locally. If you can get away with it.....

    (if other people also use the db from other locations - and enter/change data - then don't do this because the data will be out of sync..)
    www CahabaData com

  4. #4
    Join Date
    Apr 2011
    Posts
    11
    @NTC: I thought about running a local version here, but other users at a different site have to be able to use this db, so it has to be on the remote server.

    @healdem: I appreciate the suggestion about combo boxes; what do you mean by "pull the data you need when you need it"? Also: I'm experiencing the slowdown even when I have all forms closed and I'm just running a query by itself. Any thoughts about how to speed this up?

  5. #5
    Join Date
    Oct 2009
    Posts
    340
    It is very doubtful that one can meaningfully speed up performance of a split db when the network in the middle is slow. That has been my experience....and one really shouldn't blame it on Access per se. Management needs to know that the network is too slow for a distributed database....

    Since you have another group using the db; the best that you might be able to arrange - - is if they work in entirely separate records - i.e. west vs east or marketing vs engineering.....where both can operate separately and locally - - but you can merge the data daily - - so that everyone can see the entire data set. I've set this up on a couple occasions and it can work well once thought thru.

    Otherwise you might want to look into a web based application that is truely shared.
    www CahabaData com

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Access like other file server systems processes data locally
    so it has to suck data fromt he source to each computer, select the required records, retrieve those records and so on.

    so you are imposing a lot of traffic on an already slow network

    in an ideal world you should shift the data storage to a server, process the SQL queries there so only the information needed is sent back over the network. you can use server db's in Access, however it does require a redesign of the front end the user interface to get good results.

    in the mean time you can do things to improve application performance

    one is to consider storing non volatile data locally, whether on each PC running Access or some form local intermediate store (say an Access db running on a local server)
    say you have a table of products, customers and so on)
    arguably your customers and products may not change that much so you could periodically download the customer & product details to a local source and populate any combo / listr boxes requiring customer names form that local source. it depends on how volatile your data is. that stratgey will not work on things like such as stock levels.

    the other thing you could do, which is messy is use local site copies with a single master db, the local site copies synchronise periodically with the master db. look at replication GUID's and so on.

    mut gut feel is that you should be using a server back end.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jul 2013
    Posts
    1

    Slow Access over WiFi huge improvement

    I had a serious speed issue opening a form (local FE server BE, lots of controls on the form, a few queries) over our wireless network, I tried everything I found in the forums and was able to improve speed a fair amount, then I moved the BE to the root directory on the server, WOW!!! 3x faster opening that form.

Posting Permissions

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