Returning RecordSet Objects from Functions

[Dr.K]

This is a separate issue, but its also a continuation of my previous post:
http://vbaexpress.com/forum/showthread.php?t=16211

My question is about the persistence of Connection Objects:
I have all of my ADODB code in a separate module, and I've set them up as functions, so that I can pass complete RecordSets back to my Subs.

If I'm using ADODB in a self contained sub, the flow looks like this:

[vba]Sub subADODB()

Dim conDB As Object
Dim rstDB As Object

'Create Objects
'Open Connection
'Set up SQL string
'Open RecordSet

'Do stuff with RecordSet data

rstDB.Close
Set rstDB = Nothing
conDB.Close
Set conDB = Nothing

End Sub[/vba]


Basically, you create the objects, open them, use them, close them, and then destroy them ( set = nothing ).


However, if I use a function to pass back a RecordSet, I can't close EITHER object, or else the RecordSet doesn't work. Once I've passed out the RecordSet Object, I can destroy the local objects, but I can't close them. Here is an example of that:

[vba]Function GetTable() As Object

Dim conDB As Object
Dim rstDB As Object

'Create Objects
'Open Connection
'Set up SQL string
'open RecordSet

Set GetTable = rstDB

Set rstDB = Nothing
Set conDB = Nothing

End Function


Private Sub UserForm_Initialize()

Dim rstPlans As Object

Set rstPlans = GetTable

'Do Stuff with RecordSet

rstPlans.Close
Set rstPlans = Nothing

End Sub[/vba]


My question is this: in the sub that uses the RecordSet, once I'm done with it, I can close it and destroy the local object... But doesn't that leave the connection open?

Is the open connection an orphan, floating around in space?

Should I pass BOTH the Connection AND the RecordSet to the receiving Sub, so that I can properly close both when I'm done with the RecordSet?

Thanks for any input.

[Bob Phillips]

Why don't you just pull the recordset into an array using GetRows and return that array?

[Dr.K]

Well, as far as I know, RecordSets are WAY more flexible then arrays, you can do a lot more... Find method, BOF EOF flags, refering to columns by field/column name OR index numbers, various ways of sorting...

Basically, I don't have a lot of experience with Arrays. I've tried to build Functions that return Arrays before, and there is always a lot of wierdness with how you Dim your arrays... Not that I couldn't make it work, but I've fallen in love with the RecordSet Object, you can do a lot of cool stuff with it.

But at the end of the day, you have a valid point: if I'm using the RecordSet as read-only (ie, not updating the database), then I'm really just using a fancy array, and I might as well pass the data that way.