Unanswered: actions on unbound form slow after adding code for persistent connection
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??
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")
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
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)
MsgBox ("This division of work is already defined for this company")
Set rs = Nothing
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
strSQL = "DELETE from company_division WHERE company_division.id = " & company_division_id & ";"
'Set rs = CurrentDb.OpenRecordset(strSQL)
MsgBox ("Divsion of work deleted successfully.")
' Cancel the update.
'Cancel = True
Thanks.. that does seem to streamline and improve efficiency for that function. But the behavior remains the same.
Here's the code that makes the persistent connection using the startup form.
Option Compare Database
Dim dbsAlwaysOpen1 As DAO.Database
Dim dbsAlwaysOpen2 As DAO.Database
Private Sub Detail_Click()
Private Sub Form_Close()
Set dbsAlwaysOpen1 = Nothing
Set dbsAlwaysOpen2 = Nothing
Private Sub Form_Open(Cancel As Integer)
'Set dbsAlwaysOpen1 = OpenDatabase("\\servername\DB\dbname Data.mdb", False)
'Set dbsAlwaysOpen2 = OpenDatabase("\\servername\DB\zipcodes.mdb", False)
'MsgBox ("Debug: Opened connection to database")
Any other ideas on what would cause this lag?? I can't think of anything else to check except the list I've already gone through.