
	Quick Find:		Search for:

Free Resources from FMS
Demos Email Newsletter Technical Papers Tips and Techniques Blog Links Book Recommendations Microsoft Access Resource Center



"Thank you! Thank you! I just finished reading this document, which was part of a link in the recent Buzz newsletter. I have printed it for others to read, especially those skeptical on the powers of Access and its capabilities." - *Darren D.*

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

Good luck!

Additional Performance Tips for Microsoft Access Databases

Main tips page