Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2011

    Unanswered: Linked Tables - slow over LAN

    Hi all,

    My company uses Access for daily operations, which the databases are split
    into a backend (tables) and frontend by using linked 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.

    My questions are:
    1. How and where to put OpenAllDatabases True / False?
    2. Regarding "Sub OpenAllDatabases(pfInit As Boolean)", where should I put it? Form? or Module?

    Below is the copy of the content from that web site
    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
        On Error Resume Next
        For x = 1 To cintMaxDatabases
        Next x
      End If
    End Sub

    Thanks a lot.

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    I might be wrong in some or all points, but here's what comes to my mind when reading your post:

    1. If the front-end uses linked tables there should an open link to the back-end already.
    2. If the problem actually comes from a slow LAN, I don't see how maintaining a connection to the database open could enlarge the bandwidth of the network.
    3. How many users are usually connected to the back-end simultaneously?
    4. Does every user has his (or her) own copy of the front-end on his (or her) computer?
    5. Are all parts of the application (front-end) slow, or only some specific ones?
    6. I would turn the Jet Engine debug features on:
    And carefully analyze the output file showplan.out. There surely is an explanation and a path to the solution for an improved performance in there.
    Have a nice day!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts