hotfix MS Access performance windows 7

New hotfix solves Access 2010 performance issue

Thanks to Tony Toews and his blog for bringing this to our attention.

Access 2010 hotfix package (Ace-x-none.msp): August 30, 2011  which can download from here by request.

This solves the Access 2010 nd Windows 7 performance issue.

As with all hotfixes, only apply if you really has having an issue described and you can’t live with it until the next Service Pack is released.

MS Access performance

A simple way to improve performance

When you split a database and place it on a lan drive – the most common configuration – you can find performance begins to suffer. This is for two main reasons, one because a LAN is much slower than an disk, and the file opening and closing that happens. I can propose a solutions to the second problem.

The first problem is mainly a hardware issue. Put in a gigibit LAN and NICs and make sure the path to the database file on the server is as short as possible.

The second issue is, I believe, caused by the file open, locking and close process that occurs each time the access to the database file is required. Simply because the front-end is open does not mean that the file is also open, it most likely is not.

The technique I use is to hold a constant connection to the file open at all times. The only way to do this is to have a hidden form with some code on it.

Private Sub Form_Open(Cancel As Integer)
Dim ors As Recordset
Set ors = CurrentDb.OpenRecordset("select * from dummy")
Form.Visible = False
End Sub

Open this form when the database opens, say in the autoexec macro.

I find this technique works quite well when performce problems affect your application. I have even used on applications which connect to SQL Server and Oracle.