|
Free Resources from FMS |
|
|
|
|
"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. |
|
|
|
|
Microsoft
Access Resource Center
Significantly Improve the Performance of Microsoft Access Databases with Linked Tables
Provided by: Luke Chung, FMS President
If you have a Microsoft Access database with linked tables, you can significantly increase its performance by maintaining an open connection to the backend database containing the linked tables.
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 opened. 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 OpenDatabase Method
To create a persistent connection to the linked database, open a MS Access database variable in VBA using the OpenDatabase method. Keep this variable open as long as your application is running.
The procedure below can do this for 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)
'
Comments: Open a handle to all databases and keep it
open _
' during the application
' 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 on your machine." &
_
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
