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;" & _
   "Pwd=po"
   
   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.AddNew
             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
         
             recMod.Update
          End If
          'Advance cursor to next row
          recSP.MoveNext
      Loop
      Set recSP = recSP.NextRecordset
   Loop

   
zimmer9Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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), _
            rstCompound.Fields(1)
         rstCompound.MoveNext
      Loop
   
      Set rstCompound = rstCompound.NextRecordset
      intCount = intCount + 1
   Loop

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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.