Dr.K
11-21-2007, 01:06 PM
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:
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
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:
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
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.
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:
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
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:
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
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.