Does the recordset close itself when called from within a function?

Question

In VBA Programming I am trying to understand the state of a recordset when it is passes data from within a function to another recordset

for example

Sub Test()

Dim Recordset1 as new ABODB.Recordset

Set RecordSet1 = BringDataFromRecordset2()

Do while not Recordset1.EOF
'data do something

Recordset1.movenext
Loop
End Sub

Function BringDataFromRecordset2() as ADODB.Recordset
dim RecordSet2 as new ADODB.Recorset

RecordSet2.Open "Select * from DUAL", Connectionstring

BringDataFromRecordset2 = RecordSet2 

End Function

What happens to RecordSet2 when it passes the data to RecordSet1 in line "Set RecordSet1 = BringDataFromRecordset2()?

Does it close alutomatically? if RecordSet2 is still open how do I close it?

See http://stackoverflow.com/questions/517006/is-there-a-need-to-set-objects-to-nothing-inside-vba-functions — Fionnuala, Feb 04 '13 at 22:47
I don't know how to close this question... Since you have added the answer as a comment. Please suggest — Fraiser, Feb 04 '13 at 22:56
I am afraid it means the question should be closed as a duplicate, because it has already been answered. — Fionnuala, Feb 04 '13 at 22:57
Your code as written has a couple of issues. In the Function you need to use `Set BringDataFromRecordset2 = RecordSet2` since your return value is an object type. In `Test()` you don't need the `New` in the `Recordset1` declaration, since the Function takes care of creating the recordset, and then passes it to Test. `Recordset2` (or at least the object it points to) is still in scope even after the function completes, because now the `Recordset1` variable in test points to the same object. — Tim Williams, Feb 04 '13 at 23:44
Hi @TimWilliams please go ahead and add that as an answer, if you have time. — Fionnuala, Feb 05 '13 at 11:36

score 2 · Accepted Answer · answered Feb 05 '13 at 15:58

Your code as written has a couple of issues. In the Function you need to use

Set BringDataFromRecordset2 = RecordSet2

since your return value is an object type.

In Test() you don't need the New in the Recordset1 declaration, since the Function takes care of creating the recordset, and then passes it to Test.

Recordset2 (or at least the object it points to) is still in scope even after the function BringDataFromRecordset completes, because now the Recordset1 variable in Test points to the same object.

Does the recordset close itself when called from within a function?

1 Answers1