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
338 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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.

738 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