Make networked Access database faster

[OTWarrior]

I stumbled across this code, and thought I would share it with you guys

Source:

http://www.fmsinc.com/MicrosoftAccess/Performance/LinkedDatabase.html

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

[CreganTur]

Have you used this code and done any sort of stress testing with multiple machines using it? It looks like it's just forcing open a netowrk connection and it makes me worry about the impact it would have on other users who are trying to access the shared drive who aren't accessing the backend DBs.

Also, is it going to add any extra chaching, and if it does would it be client side or server side? If it's server side you could run out of memory if the drive's as overworked as some are at my company.

It's interesting code that I would like to try out, but I need more info before I even think of testing it on my company's shared drives.

[stanl]

Have you used this code and done any sort of stress testing with multiple machines using it? It looks like it's just forcing open a netowrk connection and it makes me worry about the impact it would have on other users who are trying to access the shared drive who aren't accessing the backend DBs.


I agree. Also, DAO is rather old hat and requires each user to have Access installed. MDAC and Disconnected Recordsets may be preferrable for multi-use, with error handling at the connection level. .02 Stan

[CreganTur]

Or temp tables within the distributed version for clinet-side processing... that presents its own set of hurdles, but it will give a great performance boost over the shared drive.