In the code example below, how can you be sure the variable mrs is no longer
in memory?

Thanks.

Dim mdb As ADODB.Connection
Dim mrs As ADODB.Recordset

Private Sub Command1_Click()

    Dim sSQL As String
    sSQL = "Select [Store] From [Visits]"

    '   Open 'Sub Level' Record Set
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    rs.Open sSQL, mdb, adOpenDynamic, adLockReadOnly

    '   Open 'Module Level' Record Set
    Set mrs = New ADODB.Recordset
    mrs.Open sSQL, mdb, adOpenDynamic, adLockReadOnly

    '   Does The Next Statement Invoke A Complete Cleanup Of The 'Module
Level' Recordset?
    Set mrs = Nothing

    '   The Next Statement Will Cause A Cleanup Of The 'Sub Level' Recordset
As It Falls Out Of Scope
    Exit Sub
End Sub

Private Sub Form_Load()
    Dim sOpen As String
    sOpen = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\CA1.mdb;"

    Set mdb = New ADODB.Connection
    mdb.CursorLocation = adUseClient
    mdb.Open sOpen

End Sub

There has been a *vast* amount of "lively debate" (euphemism) on this issue
of cleaning-up objects, ie. ensuring that objects are derefrenced and
resources released explicity.

IMHO VB does a pretty good job of cleaning up objects all by itself, so I
like to take a "less is more" approach. When writing my code, I usually make
an effort to include 'Set Obj=Nothing', but I'm not gonna worry if I forget
a few here and there. The only environment I know of where you must
explicity release objects & resources in this manner is when working with
Connection Pooling.

If you want to know for sure whether your objects are being released, you
just have to ask yourself this: In the VB IDE, do you have to click the
"End" button to halt execution ? In other words, does your project exit
run-time, and return to desing-time, **of it's own accord**, when the last
form is unloaded ? If it does, you really don't have a problem - resources
are being released. An exception to this rule is when you are working with a
an ActiveX EXE project, in which case you have to compile it & see whether
it terminates properly when run directly under Windows.

NB Don't use the END keyword to terminate execution. Your project &
resources should unload/terminate without END. Clicking the <STOP> button in
the VB IDE is the equivalent of END - you should not have to do either.

Otherwise, if your project will *not* unload - ie. it stays memory resident
when you've unloaded the last form - you may have issues with Circular
References or violations of VB's component lifetime rules. If that is the
case, then check out the articles in the VB Documentation section of MSDN,
that talk about "Component Lifetime" and "Circular References."

HTH
--
Kind Regards,

Robert A. Ellis, MCSD
Software Developer