CODEWITHSUNDEEP
excelms-accessvba
Fraiser
Fraiser

Reputation: 310

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

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?

Upvotes: 1

Views: 892

Answers (1)

Tim Williams
Tim Williams

Reputation: 166511

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.

Upvotes: 2

Related Questions