zimmer9
asked on
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.Connec tion")
oConn.Open "DSN=NY;" & _
"Uid=PW;" & _
"Pwd=po"
Set oCmd = New ADODB.Command
With oCmd
.ActiveConnection = oConn
.CommandType = adCmdStoredProc
.CommandText = "sp3D_GetDocumentPages 'VAKO100120080945000000014 B'"
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
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.Connec
oConn.Open "DSN=NY;" & _
"Uid=PW;" & _
"Pwd=po"
Set oCmd = New ADODB.Command
With oCmd
.ActiveConnection = oConn
.CommandType = adCmdStoredProc
.CommandText = "sp3D_GetDocumentPages 'VAKO100120080945000000014
Set recSP = .Execute
End With
recMod.Open "tblGetDocumentPages", CurrentProject.Connection,
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.