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.