Significantly Improve the Performance of Microsoft Access Databases with Linked Tables

Provided by: Luke Chung, FMS President

If you are using a split Microsoft Access database architecture where your front-end database has tables linked to a backend Access Jet database, you can significantly increase its performance by maintaining an open connection to the backend database throughout the time your front-end database is opened.

By forcing Access to keep the linked table's database open, Access avoids creating a new lock on the backend database every time one of its tables is used. This lets you open tables, forms, and reports much faster. Over a network, you'll usually see a substantial improvement with how quickly a form opens when it's based on a linked table.

The DAO OpenDatabase Method

To create a persistent connection to the linked database, open a MS Access database variable in VBA using the DAO OpenDatabase method. Keep this variable open as long as your application is running.

The procedure below supports multiple backend databases. Edit the section with the list of databases to open, then call this when your application starts:

OpenAllDatabases True

When you finish, call this to close the database variables/handles:

OpenAllDatabases False

Here's the procedure code:

Sub OpenAllDatabases(pfInit As Boolean)
  ' Open a handle to all databases and keep it open during the entire time the application runs.
  ' In: pfInit   TRUE to initialize (call when application starts)
  '              FALSE to close (call when application ends)
  ' Created : FMS, Inc.
 
  Dim x As Integer
  Dim strName As String
  Dim strMsg As String
 
  ' Maximum number of back end databases to link
  Const cintMaxDatabases As Integer = 2
 
  ' List of databases kept in a static array so we can close them later
  Static dbsOpen() As DAO.Database
 
  If pfInit Then
    ReDim dbsOpen(1 To cintMaxDatabases)
    For x = 1 To cintMaxDatabases
      ' Specify your back end databases
      Select Case x
        Case 1:
          strName = "H:\Dir\Backend1.mdb"
        Case 2:
          strName = "H:\Dir\Backend2.mdb"
      End Select
      strMsg = ""
 
      On Error Resume Next
      Set dbsOpen(x) = OpenDatabase(strName)
      If Err.Number > 0 Then
        strMsg = "Trouble opening database: " & strName & vbCrLf & _
                "Make sure the drive is available." & vbCrLf & _
                "Error: " & Err.Description & " (" & Err.Number & ")"
      End If
 
      On Error GoTo 0
      If strMsg <> "" Then
        MsgBox strMsg
        Exit For
      End If
    Next x
  Else
    On Error Resume Next
    For x = 1 To cintMaxDatabases
      dbsOpen(x).Close
    Next x
  End If
End Sub

This simple technique yields considerable performance gains.

Additional Resources

• Microsoft Access Best Practices: Finding Errors, Suggestions, and Performance Tips in Your Databases with Total Access Analyzer
• Splitting Microsoft Access Databases to Improve Performance and Simplify Maintainability
• Microsoft Access Performance Tips to Speed up Your Access Databases

Good luck!

Additional Performance Tips for Microsoft Access Databases

Return to the tips page