Split database persistent connection

[AccessThomas]

Using Access 2013.

We have split a database and have about 6 users connected to a gigabit switch. There is a noticeable delay of about 5 to 15 seconds when we search by an account name. We are working with a split form, where it has the database records listed at the bottom half of the screen.

Is there a way to improve the performance? I tried a persistent connection, but it didn't seem to help and would occasionally get an error message.

Thanks.

[cbende2]

How many records are you dealing with?

Where is the back end located? In a shared folder on the network?

[ItsMe]

... I tried a persistent connection...

How did you go about this? My main form for the Menu is unbound. However I have a subform on the main form that is bound to tblUsers. The subform displays basic info about the User that logged in. This is my persistent connection.

[AccessThomas]

The backend is on one of the 6 laptops in a shared folder. There are about 35,000 records.

[AccessThomas]

I tried this procedure code I found.

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)
  ' Source  : 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

[ItsMe]

Hmmm, the idea of a connection to the persistence layer is to create a locking file. If you close the connection, the locking file will go away. When you open another connection, the locking file needs to be created again.

When the user first opens their FE, have a form open that does not close until the application is closed. Bind this form to a small table that resides on the BE. If there are multiple BE files, create multiple connections (one for each BE). If you do not have a Main Menu form, you can hide a form.

[cbende2]

If its in a shared folder, why do you say it's on 1 of 6?

[AccessThomas]

It's in a shared folder on one of the laptops on the switch.

[AccessThomas]

I did the form for the persistent connection and I think it is helping a little. Thanks.

[ItsMe]

The other thing you can do is look at your queries. Perhaps you are using Group By on a large dataset. I find this to be a slow performer in Access. If possible, I use a WHERE clause in a sub-query and join that query to a parent that has the Group By statement.

[ohmydatabase]

I've tried to use the OpenDatabase Method in my AutoExec Macro and have run into a problem with other users already having the Back-End open in 'Exclusive Mode'. I believe that Microsoft's documentation was trying to say that if another user already has the Back-End opened, using the OpenDatabase method on the same Back-End, but on a different computer, will cause an error. I have recently set the option in OpenDatabase() to False which will keep the back-end opened in shared mode. I am not confident that this will actually solve this problem though. Has anyone had problems like this before?

I did notice a performance increase while establishing a persistent connection. However, it was not enough to overcome the sluggish network. I'm not sure if the persistent errors were worth the persistent connection. I may remove this code and let the afflicted users suffer.

[ohmydatabase]

The other thing you can do is look at your queries. Perhaps you are using Group By on a large dataset. I find this to be a slow performer in Access. If possible, I use a WHERE clause in a sub-query and join that query to a parent that has the Group By statement.

I was experiencing slow performance while digging through ~3000 records. However, I thought that Access could handle tables that had much more records. (possibly into the millions)
Do the types of queries we right slow performance? Would it be better to create new fields that was facilitate queries? (If I can make a flag in a smaller table to avoid searching a much larger table, should I do that?)