I have a project split into FE and BE end files. When more than one user was connected I was having very slow response times on some queries. At the suggestion of some other forum threads, I used a startup form in the FE file to open a persistent connection to the BE file. This solved the problem of those queries being slow, but made one other portion of the app slow to respond. Even with multiple users connected, this portion of the app runs quickly as long as I TURN OFF the persistent connections. But I obviously need to keep persistent connections ON for the sake of all other queries in the project. I've tried the following to no avail:

-for all tables, turning subdatasheets off
-linked to the BE file using UNC instead of mapped drive
-made sure all autocorrect options where off in FE an BE files (they already were)
-made sure the FE and BE files are in trusted locations

Here's the code that runs slowly. The code lets a user select a value from a combobox, then click an "add" button that will add a record in a junction table to link the current company to the "division" that the user selected in the combobox. The listbox then refreshes to show the new entry. The problem (only when using persistent connections) is that the record apparently doesn't actually get written fast enough before the listbox refreshes and therefore it's not displayed to the user. Waiting a second or two and refreshing allows enough time and the entry will then show up in the listbox. There is also a "remove" button that will allow the user to select a listbox entry and remove it from the junction table. That function has the same problem: the deletion occurs, but not before the listbox is requeried.

Is there anything else I can do to avoid this side effect of using persistent connections??


Code:
Private Sub Add_Divsion_of_Work_Click()

Dim strSQL As String
Dim rs As DAO.Recordset

If IsNull(Me.combo_subdivision_lookup.Value) Then
MsgBox ("You must make a selection before adding")
Else

strSQL = "SELECT company_id, subdivision_number from company_division where company_division.company_id = " & Me.ID.Value & _
" AND company_division.subdivision_number = '" & Me.combo_subdivision_lookup.Column(2) & "';"

Set rs = CurrentDb.OpenRecordset(strSQL, , dbAppendOnly)

If rs.EOF Then

    rs.AddNew
    MsgBox ("Debug: Adding company ID: " & Me.ID.Value)
    rs!company_id = Me.ID
    MsgBox ("Debug: Adding division ID: " & Me.combo_subdivision_lookup.Column(2))
    rs!subdivision_number = Me.combo_subdivision_lookup.Column(2)
    rs.Update

    
Else
    MsgBox ("This division of work is already defined for this company")
End If

rs.Close
Set rs = Nothing

Me.List_of_subdivisions.Requery
End If
End Sub

Private Sub remove_Click()
Dim i As Integer
Dim company_division_id As Integer
Dim strSQL As String
'Dim rs As Recordset


For i = Me.List_of_subdivisions.ListCount - 1 To 0 Step -1
    If Me.List_of_subdivisions.Selected(i) Then
        company_division_id = Me.List_of_subdivisions.Column(4)
        'MsgBox ("Debug: The company division ID to delete is: " & company_division_id)
        
        'Prompt user to save changes before updating
        Dim strMsg As String
        Dim iResponse As Integer

        ' Specify the message to display.
        strMsg = "Are you sure you wish to delete the scope of work for this company?" & Chr(10)
        strMsg = strMsg & "Click Yes to proceed or No to Discard changes."

        ' Display the message box.
        iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Delete?")
   
        ' Check the user's response.
        If iResponse = vbNo Then
        'Do nothing
        Else
        
        strSQL = "DELETE from company_division WHERE company_division.id = " & company_division_id & ";"
        'Set rs = CurrentDb.OpenRecordset(strSQL)
        CurrentDb.Execute (strSQL)
        MsgBox ("Divsion of work deleted successfully.")
        ' Cancel the update.
        'Cancel = True
        
        End If
    End If
Next i

Me.List_of_subdivisions.Requery

End Sub

thanks in advance,

baulrich