Solved

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

Posted on 2008-11-01
1
335 Views
Last Modified: 2013-11-27
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

   
0
Comment
Question by:zimmer9
1 Comment
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 22865847
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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question