Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 354
  • Last Modified:

How can I read multiple record sets and avoid the error 3704, "Operation is not allowed when the object is closed" ?

I am developing an Access MDB application and calling a Sybase Stored procedure that I do not own.
In the subroutine that follows I use 2 DO LOOPS. The application goes through the inner DO LOOP a few times and processes all the records for the 1st recordset.

Then the application executes the NextRecordSet method at the bottom of the loop and then goes back to the top of the loop.
When the system now encounters the following statement:
Do While Not recSP.EOF
the system then generates Error number 3704.
Operation is not allowed when the object is closed.

Do you know how I could prevent this error from happening ?

Private Sub Command9_Click()
   Dim oConn As New ADODB.Connection
   Dim oCmd As New ADODB.Command
   Dim recSP As New ADODB.Recordset
   Dim recMod As New ADODB.Recordset
   Dim f As ADODB.Field
   DoCmd.Hourglass True
   Set oConn = CreateObject("ADODB.Connection")

   oConn.Open "DSN=NY;" & _
   "Uid=PW;" & _
   Set oCmd = New ADODB.Command
   With oCmd
       .ActiveConnection = oConn
       .CommandType = adCmdStoredProc
       .CommandText = "sp3D_GetDocumentPages 'VAKO100120080945000000014B'"
       Set recSP = .Execute
   End With
   recMod.Open "tblGetDocumentPages", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic, adCmdTable
   Do Until recSP Is Nothing
      Do While Not recSP.EOF

          If Not IsNull(recSP(0)) Then
             recMod!fMailID = recSP(0)  'assume 1st field in rs etc
             recMod!fPageNumber = Nz(recSP(1), " ")  'assume 2nd field in rs
             recMod!fPageType = Nz(recSP(2), " ") 'assume 3rd in rs
             recMod!fImagePath = Nz(recSP(3), " ") 'assume 4th in rs
             recMod!fImageSize = Nz(recSP(4), " ") 'assume 5th in rs
          End If
          'Advance cursor to next row
      Set recSP = recSP.NextRecordset

1 Solution
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I don't see where you're correctly building your "NextRecordset" ... this is a very little used command (at least in these forums and in others I subscribe to), and it basically expects a series of commands ... online help gives this example:

Set rstCompound = New ADODB.Recordset
   rstCompound.Open "SELECT * FROM authors; " & _
      "SELECT * FROM stores; " & _
      "SELECT * FROM jobs", strCnn, , , adCmdText

   ' Display results from each SELECT statement.
   intCount = 1
   Do Until rstCompound Is Nothing
      Debug.Print "Contents of recordset #" & intCount
      Do While Not rstCompound.EOF
         Debug.Print , rstCompound.Fields(0), _
      Set rstCompound = rstCompound.NextRecordset
      intCount = intCount + 1

As you can see, the rstCompound object was passed a series of 3 SELECT statements AND was set to adCmdText ... after processing the first, apparently it moves to the second.

The question then is what, exactly, does your sp3D_GetDocumentPages 'VAKO100120080945000000014B stored proc return? If it's not returning something like the above example, then you may not be getting the correct or expected number of recordsets.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now