Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2012
    Posts
    4

    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??

    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

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I'm not sure of how you manage the persistent connection but you could optimize the code that access the data. Here's an example (aircode):
    Code:
    strCriteria = "company_id = " & Me.ID.Value & " AND subdivision_number = '" & Me.combo_subdivision_lookup.Column(2) & "'"
    If DCount("*", "company_division", strCriteria) = 0 Then
        strSQL = "INSERT INTO company_division (company_id, subdivision_number) " & _
                 "VALUES (" & Me.ID & ", '" & Me.combo_subdivision_lookup.Column(2) & "');")
        CurrentDb.Execute strSQL, dbFailOnError
    Else
        MsgBox ("This division of work is already defined for this company")
    End If
    Have a nice day!

  3. #3
    Join Date
    Nov 2012
    Posts
    4
    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.

    Code:
    Option Compare Database
    
    Dim dbsAlwaysOpen1 As DAO.Database
    Dim dbsAlwaysOpen2 As DAO.Database
    
    Private Sub Detail_Click()
    
    End Sub
    
    Private Sub Form_Close()
          Set dbsAlwaysOpen1 = Nothing
          Set dbsAlwaysOpen2 = Nothing
    End Sub
    
    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")
    End Sub
    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.

    thanks,

    baulrich

Posting Permissions

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