Slow data access via an ODBC datasource linked to an Access database

Question

My company has developed an application with Visual Basic 6.
The application uses an Access database via an ODBC datasource.
The Access database is a file with the ".mdb" extension.

We have not noticed any slow data access when running the application in the following environment :

• OS : Windows 7 32 bits.
• Installed version of MS Access : Access 2007.
• Access driver used by the OBDC datasource : Microsoft Access Driver (*.mdb) version 6.01.7601.17632.

But we have noticed slow data access when running the application in the following environment :

• OS : Windows 7 64 bits.
• Installed version of MS Access : Access 2010 or 2003.
• Access driver used by the ODBC datasource : Microsoft Access Driver (*.mdb) version 6.01.7601.17632.

I have googled for a while to find a solution.
Other developpers have experienced the same problem according to articles found on the Internet.

For information the tracing feature is not enabled in my ODBC 32 bits administrator.
The following page mentions the tracing feature :
http://answers.microsoft.com/en-us/office/forum/office_2010-access/my-solution-to-access-being-slow-with-odbc/a5a6522f-a70f-421e-af1b-48327075e010

I have also tried without success to disable the LLMNR protocol as mentionned in the following page : http://accessexperts.net/blog/2011/11/02/windows-7-64bit-slow-with-access-2007-solved/

Any help will be greatly appreciated

Solution

There are number things to check, the first and foremost thing you want to do is create in your application what we call a persistent connection. A persistent connection simply means that somewhere in your startup code, you open up a table from the back end to a record set, and keep it open for the duration of any other operations you do in that application.

The reason why a persistent connection often makes a huge difference is that the newer operating systems tend to do have a tremendous amount of additional security and overhead when a connection to the databases created. And it turns out that generally opening and closing tables in access databases requires that these connections are also opened and closed. If you force the persistent connection to remain open at all times, then this very slow and large overhead process that interferes with general performance of general updates in your code will now not be incurred.

Give the above a try, since this setup very often cure is this performance issue and problem.

Comments

[user1139666]

My company's application uses the Access database via the DAO 3.6 library. I have checked the VB6 source code to see how the connection is created. The connection is created in the beginning of the execution. First the application gets the default workspace which uses the Microsoft Jet DB engine. Then the application executes the OpenDatabase method of the Workspace object. The OpenDatabase method returns a Database object used during the rest of the execution time to execute SQL queries.

[Albert D. Kallal]

Does it keep that OpenDatabase open at all times? you don't want to re-open and re-create the OpenDatase. It needs to occur ONLY one time. Thus you need to create a global object that opens the database and remains in place for the duration of the sesson.