linked tables - slow

[wowiwi]

Hi all,



My company uses Access for daily operations, which the databases are split into a backend (tables) and frontend by using lined tables. Under multi-user environment, the performance of access over the LAN is very slow at frontend. To solve this, I find the following web site, which create a persistent connection to the linked database, somehow because my VB script is not good, so some questions below would like to ask.
http://www.fmsinc.com/MicrosoftAcces...dDatabase.html

My questions are:
1. How and where to type OpenAllDatabases True / False?
2. Regarding "Sub OpenAllDatabases(pfInit As Boolean)", if I put it in module, how can I call it out in Macro?

Below is the copy of the the web site information.
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.
' Params : pfInit TRUE to initialize (call when application starts)
' FALSE to close (call when application ends)
' From : Total Visual SourceBook

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:\folder\Backend1.mdb"
Case 2:
strName = "H:\folder\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

[RuralGuy]

Use the AutoExec macro to open a hidden form. Implement your persistant connection in this form OnLoad or Open event and close the connection in the UnLoad or Close event.

[wowiwi]

Thanks a lot RuralGuy,

I tried to insert the following codes into the form. And then in On Open event, I entered "OpenAllDatabases", however, when the form is opened, error message pop up saying that Access cannot find the object. Not sure which part I am missing. Appreciate for advice. Thanks again.

Private Sub Form_Load()
Sub OpenAllDatabases(pfInit As Boolean)
  ' Open a handle to all databases and keep it open during the entire time the application runs.
  ' Params  : pfInit   TRUE to initialize (call when application starts)
  '                    FALSE to close (call when application ends)
  ' From    : Total Visual SourceBook

  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 = "\\Sza-server-02\Work_Documents\Database\_DB_Sales\Bona_Sales_Record.accdb"
        Case 2:
          strName = "\\Sza-server-02\Work_Documents\Database\_DB_Sales\Product_Information_v4_SZ.accdb"
      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

[RuralGuy]

You cannot put a Sub inside of a Sub. Your Open Event should be:
Private Sub Form_Load()
OpenAllDatabases(True)
End Sub

[wowiwi]

Yes, it works now!! Thanks a lot for your quick help!

Cheers.

[RuralGuy]

Great! I marked this thread as Solved for you. You would use:
OpenAllDatabases(False)
...in the UnLoad event to shut down the persistant connection.