ado copyfromrecordset and nextrecordset

[Matt Williamson]

I have a stored proc on a sql server that is just a series of select
queries. Right now, it's 7 but I'll be adding more as time goes on. I using
ado in Excel 2003 to dump the results into the excel spreadsheet using
copyfromrecordset. This works fine if I know the number of recordsets and
write a block of code for each one e.g

' Set rst = rst.nextrecordset
' lLastRow = ActiveSheet.Range("a65536").End(xlUp).Row
' Range("A" & lLastRow).Offset(2, 0).CopyFromRecordset rst
' Set rst = rst.nextrecordset
' lLastRow = ActiveSheet.Range("a65536").End(xlUp).Row
' Range("A" & lLastRow).Offset(2, 0).CopyFromRecordset rst
' Set rst = rst.nextrecordset
' lLastRow = ActiveSheet.Range("a65536").End(xlUp).Row
' Range("A" & lLastRow).Offset(2, 0).CopyFromRecordset rst
' Set rst = rst.nextrecordset
' lLastRow = ActiveSheet.Range("a65536").End(xlUp).Row
' Range("A" & lLastRow).Offset(2, 0).CopyFromRecordset rst
' Set rst = rst.nextrecordset
' lLastRow = ActiveSheet.Range("a65536").End(xlUp).Row
' Range("A" & lLastRow).Offset(2, 0).CopyFromRecordset rst

But since I'm adding to it over time, I'm trying to make it dynamic and just
loop through each recordset. The code I came up with to do that is:

Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandText = "Compare_APX_UDA"
cmd.CommandType = adCmdStoredProc

Set rst = cmd.Execute()

Range("A8").CopyFromRecordset rst

Do
lLastRow = ActiveSheet.Range("a65536").End(xlUp).Row
Range("A" & lLastRow).Offset(2, 0).CopyFromRecordset rst
Set rst = rst.NextRecordset
Loop Until rst Is Nothing

Set rst = Nothing
Set cmd = Nothing
Set cn = Nothing

But the recordset doesn't get set to nothing when it reaches the end and it
gives me the error:

Error 3407: Operation not allow when object is closed.

I've tried Do until rst is nothing : loop as well but there is no change. I
know I can trap the error and just dump out there but I'd like to know how
this is supposed to work. I haven't found any examples that use
copyfromrecordset and nextrecordset to figure out the right way to do it.

How do I break out of the loop when I get to the last recordset?

TIA

Matt

 

[Richard Mueller [MVP]]

I have a stored proc on a sql server that is just a series of select
queries. Right now, it's 7 but I'll be adding more as time goes on. I using
ado in Excel 2003 to dump the results into the excel spreadsheet using
copyfromrecordset. This works fine if I know the number of recordsets and
write a block of code for each one e.g

' Set rst = rst.nextrecordset
' lLastRow = ActiveSheet.Range("a65536").End(xlUp).Row
' Range("A" & lLastRow).Offset(2, 0).CopyFromRecordset rst
' Set rst = rst.nextrecordset
' lLastRow = ActiveSheet.Range("a65536").End(xlUp).Row
' Range("A" & lLastRow).Offset(2, 0).CopyFromRecordset rst
' Set rst = rst.nextrecordset
' lLastRow = ActiveSheet.Range("a65536").End(xlUp).Row
' Range("A" & lLastRow).Offset(2, 0).CopyFromRecordset rst
' Set rst = rst.nextrecordset
' lLastRow = ActiveSheet.Range("a65536").End(xlUp).Row
' Range("A" & lLastRow).Offset(2, 0).CopyFromRecordset rst
' Set rst = rst.nextrecordset
' lLastRow = ActiveSheet.Range("a65536").End(xlUp).Row
' Range("A" & lLastRow).Offset(2, 0).CopyFromRecordset rst

But since I'm adding to it over time, I'm trying to make it dynamic and
just loop through each recordset. The code I came up with to do that is:

Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandText = "Compare_APX_UDA"
cmd.CommandType = adCmdStoredProc

Set rst = cmd.Execute()

Range("A8").CopyFromRecordset rst

Do
lLastRow = ActiveSheet.Range("a65536").End(xlUp).Row
Range("A" & lLastRow).Offset(2, 0).CopyFromRecordset rst
Set rst = rst.NextRecordset
Loop Until rst Is Nothing

Set rst = Nothing
Set cmd = Nothing
Set cn = Nothing

But the recordset doesn't get set to nothing when it reaches the end and
it gives me the error:

Error 3407: Operation not allow when object is closed.

I've tried Do until rst is nothing : loop as well but there is no change.
I know I can trap the error and just dump out there but I'd like to know
how this is supposed to work. I haven't found any examples that use
copyfromrecordset and nextrecordset to figure out the right way to do it.

How do I break out of the loop when I get to the last recordset?

TIA

Matt

Use:

Do Until rst.EOF

Per this link the method copies until the EOF property is True:

http://msdn.microsoft.com/en-us/library/aa165427(office.10).aspx

 

[Matt Williamson]

Use:

Do Until rst.EOF

Per this link the method copies until the EOF property is True:

http://msdn.microsoft.com/en-us/library/aa165427(office.10).aspx

Thanks Richard. That works when I use loop until rst.eof. I thought for sure
I had tried that already. Apparently not.

 

[Matt Williamson]

Thanks Richard. That works when I use loop until rst.eof. I thought for
sure I had tried that already. Apparently not.

Hmm. I did try it. When I said it worked, it was because I forgot to comment
out my error trap. rst.EOF doesn't do it either. Loop Until rst.State <> 1
seems to work though but I never would have figured that out by reading the
ado help on the state property of the recordset object. I just opened the
locals window and watched which properties changed as I stepped through the
code.