Solved performance issue with backend Access database

Posted on 2011-10-04
Last Modified: 2012-08-13

I have an Excel 2007 project in that talks to a backend Access database using the connection string below. There are now 2000 records in the main table used in the database and the processing takes almost a minute just to open one form. What sort of thing causes this? How can I speed this up?

 Dim ConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & oFullPath & ";"
Question by:murbro
    LVL 61

    Assisted Solution

    There are a lot of factors that can influence the performance of your database.  This article is well worth the read:

    In addition to the tips there, I have heard others say that the length of the path (number of folders) to your Access back-end can also make a difference.  

    ie: a database linked to this path:


    Would not perform as well as one linked to this path:


    I'm not sure of the rationale behind it, and can't personally vouch for it, but have colleagues who vow that there is something to that, so I'm just tossing that out there...

    LVL 5

    Accepted Solution

    There can be more factor, now first try to sort out factor, may be your sql query is causing speed issue:

    1)  Can you post your sql query and table structure?
    2) What is completion time when you run  same SQL( query) in MS Access ?

    LVL 56

    Assisted Solution

    by:Jim Dettman (Microsoft MVP/ EE MVE)

     In addition to what's been said, make sure that the DB is not being virus scanned at open.  I'd also look carefully at indexing.

     Finially I don't believe it applies in this case, but tables have a subdatasheet property, which should be set to none.


    LVL 56

    Expert Comment

    by:Jim Dettman (Microsoft MVP/ EE MVE)

     Should have also mentioned that there are specific instances/situations that can cause problems.  SMB2 has been a lot of headaches:

    A computer that is running Windows 7 or Windows Server 2008 R2 takes four minutes to open a Microsoft Office 2003 document from a network share

    Also, if your running a multi-core processor, JET/ACE can get hung up in garbage collection.  Setting the cache larger (Maxbuffers) takes care of that.

     As mbizup said, lots of things that could apply to this.  A one minute open time is not normal.  So I would suggest testing in a different situation and see if you get the same results (ie. DB local and not on a server, different PC, etc).


    Author Closing Comment

    Thank you all for the help

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now