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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
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.

920 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now