Returning RecordSet Objects from Functions

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.

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

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.