none
Operation is not allowed when the object is open

    Question

  • I'm having a different problem with the following code again.  I'm getting the error "operation is not allowed when the object is open".  I added a separate connection to each record set to see if that would help but to no avail.  The first record updates okay, the error seems to occur after the first loop fires in order to update the second record in the record set.  Not sure how to proceed.

     

    Dim strWhere As String
                        Dim myConnect1 As ADODB.Connection
                        Dim myConnect2 As ADODB.Connection
                        Dim rQry As ADODB.Recordset
                        Dim rUpdate As ADODB.Recordset
                        Dim strConn As String
                        Dim strPath As String
                        strPath = CurrentProject.Path & "\"
                        strConn = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & strPath & "CustomJobEnter.accdb"
                        Set myConnect1 = New ADODB.Connection
                        Set myConnect2 = New ADODB.Connection
                        myConnect1.Open strConn
                        myConnect2.Open strConn
                        Set rQry = New ADODB.Recordset
                        Set rUpdate = New ADODB.Recordset

                        rQry.Open "Select * From qryCustIDJoInput where ID = " & Me.txtID, myConnect1, adOpenDynamic, adLockOptimistic
                        If rQry.BOF = True And rQry.EOF = True Then
                            MsgBox "No data found", vbOKOnly
                            rQry.Close
                            Set rQry = Nothing
                            Set rUpdate = Nothing
                            Set myConnect1 = Nothing
                            Exit Sub
                            End If
                        rQry.MoveFirst
                            Do Until rQry.EOF = True
                            strWhere = "Origin = 'Required' And JobEnvelopeNo = " & rQry.Fields("ID") & " And Weight = " & rQry.Fields("StoneWeight1")
                            rUpdate.Open "Select Origin, Selected, TW, Type, Cost from tblParts Where " & strWhere, myConnect2, adOpenDynamic, adLockOptimistic
                        If rUpdate.BOF = True And rUpdate.EOF = True Then
                            rUpdate.Close
                        Else
                            rUpdate.MoveFirst
                            Do Until rUpdate.EOF = True
                            rUpdate.Fields("Origin") = rQry.Fields("ItemNum")
                            rUpdate.Fields("Selected") = rQry.Fields("TotalPieces")
                            rUpdate.Fields("TW") = rQry.Fields("TotalWeight")
                            rUpdate.Fields("Type") = rQry.Fields("ItemDescription")
                            rUpdate.Fields("Cost") = rQry.Fields("TotalCost")
                            rUpdate.Update
                            rUpdate.MoveNext
                            Loop
                        End If
                            rQry.MoveNext
                            Loop
                        rQry.Close
                        rUpdate.Close
                        Set rQry = Nothing
                        Set rUpdate = Nothing
                        Set myConnect1 = Nothing
                        Set myConnect2 = Nothing

    Wednesday, April 27, 2011 9:50 PM

Answers

  • I see, indeed, in that case... :-)

     

    Can you move the

        rUpdate.Close

    to just before the

        rQry.MoveNext

    instead of leaving it after the rQry close, as it is now:

                        rQry.Close
                        rUpdate.Close

     

     

    • Marked as answer by Bruce Song Tuesday, May 03, 2011 9:54 AM
    Thursday, April 28, 2011 2:14 PM

All replies

  • Not the answer that you seek, but why on Earth don't you use an Update query:

     

    "UPDATE tblParts INNER JOIN qryCustIDJoInput  ON tblParts.JobEnvelopeNo=qryCustIDJoInput.ID   AND  tblParts.Weight=qryCustIDJoInput.StoneWeight1
    SET tblParts.Origin=qryCustIDJoInput.ItemNum,
    tblParts.Selected=qryCustIDJoInput.TotalPieces,
    tblParts.TW=qryCustIDJoInput.TotalWeight,
    tblParts.Type=qryCustIDJoInput.ItemDescription,
    tblParts.Cost=qryCustIDJoInput.TotalCost
    WHERE qryCustIDJoInput.ID = " &  Me.TextID

     

    using that string as CommandString? No need to have two connections, not a single recordset, no loop.

    Wednesday, April 27, 2011 11:55 PM
  • You are missing an rUpdate.Edit after the Do Until rUpdate.EOF = True.
    Thursday, April 28, 2011 3:31 AM
  • I initially did just that but I was getting "operation-must-use-updateable-query" error.  Using ADO and two record sets is a work around from that error.  The "qryCustIDJoInput" query incorporates a pass through query to a Pervasive SQL database.  P-SQL is very slow and cranky and requires a lot of creative programming to get anything out of it.  Hence the unorthodoxy here.
    Thursday, April 28, 2011 1:41 PM
  • " rUpdate.Edit"

    Nope - that produces a compile error: method or data member not found.  It doesn't appear to be part of the ADO collection.

    Thursday, April 28, 2011 1:54 PM
  • I see, indeed, in that case... :-)

     

    Can you move the

        rUpdate.Close

    to just before the

        rQry.MoveNext

    instead of leaving it after the rQry close, as it is now:

                        rQry.Close
                        rUpdate.Close

     

     

    • Marked as answer by Bruce Song Tuesday, May 03, 2011 9:54 AM
    Thursday, April 28, 2011 2:14 PM
  • YES!   Awesome!

    Thanks a million - worked like magic...

    Thursday, April 28, 2011 2:32 PM