Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2005
    Posts
    5

    Unanswered: Speeding up an Access database

    I have created an MS Access database primarily for tracking file movement. The database is a split database and has a front end which sits on client computers (about 10 computers) and a back end which sits on a Server 2003 server. The problem I am having is that the database runs too slow even with no more than 2 concurrent users. I have checked our etwork and can't spot any issues that culd slow things down. On a standalone non-split database it appears to run quite ok. The tracking number is scanned in via a bar code scanner and other fields are manually entered.

    Grateful for advice on how I can speed things up and also if anyone has some ideas on how to generate a search form.

    I have attached a non-split database in zipped fomat

    Many thanks
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    depends on how you have designed the db
    depends on whether you have optimised the db to work for an Access front end and a server back end
    depends on where you run the the queries... do you use pass through wqueries of native JET queries
    are you suing bound or unbound controls/forms/reports

    are you using a lot of dlookups and other domain functions.. they are a performance killer used unwittingly

    are you using a good book such as the Access Developers 'Enterprse' handbook to give you code examples
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I did notice that your mdb didn't debug/compile (coding errors). This though 'shouldn't' have an impact on your performance.

    After looking at the forms, I didn't see anything which would cause an extreme slowness (such as bad form design).

    A slow or problematic network drive can really hamper an mdb performance, especially if external users must connect in to the network drive (ie. the network drive can be running efficiently but when an external user connects, the connection to the network drive is hampered by how that user is connecting). If it's slow for only specific users (and none of them are connecting externally), I'd check to see if anything else might be running on the user's computers (such as an antivirus scan.) Other than that, the connection for your external users to the network drive itself would most likely be the culprit (ie. someone connecting external via dialup or a slow connection.)

    VPN (ie. having users connect via VPN) along with a SQL Server backend for the tables would really help your performance for external users (since you'd then bypass connecting to the network drive.) But I think your problem lays with the user's external connection itself to the network drive (which again, if you configured external users to connect via VPN and then had the backend tables in SQL Server, you'd get much better performance).

    The other option is to design your forms in an 'unbound' fashion. This is great for slow external connections. See the code bank (last page) for an example on how to design unbound forms.
    Last edited by pkstormy; 03-23-10 at 14:35.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    For what it's worth:
    Microsoft Access Performance FAQ
    Me.Geek = True

  5. #5
    Join Date
    Oct 2009
    Posts
    340
    make sure your BE is at the top of the root of the server - and not buried multi-folder down (unfortunately to make this change you'll have to relink everyone)

    you've already tested it have the FE/BE on one single PC and say it is ok.

    Now test it have the FE on one PC and BE on another PC (not the server). This assumes that you have a shared folder that you can see via MyComputer/MyNetwork and can link to. If this is also ok - then it may be the server rather than the network - but it could be still the network if there are other segments involved. The ideal test is to a PC that is colocated near the server and working off the same network segment as the server.

    If this is also real slow then you do have a network issue. But the equipment may work ok - only you have too much congestion with people watching youtube videos or something. Network equipment now has a QOS dimension where you can prioritize which application gets bandwidth. There is a company that does remote network troubleshooting that is very reliable Trace Laboratories | Trace Labs | Testing and Analysis but it isn't free of course.

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by nckdryr View Post
    I can't say that I agree with the "LDB locking which a persistent recordset connection fixes" method described in the link. I prefer using my cloning vb script to resolve locked "*.ldb" type issues (which works very well) versus trying to establish a constant connection to the backend via a hidden opened form.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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