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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.