Passing recordset object destruction in VBA

Question

I think I've come to understand the passing of a recordset. It is always passed by reference. What I have a question about is the destruction of the object.

I have a function that acquires a recordset from Access. I call that function from a given routine, passing the SQL statement to it and getting the recordset result from it. See the code sample below.

At the end of the calling routine I close the recordset and set the object to Nothing. What I'm not clear on is whether something needs to be done to destroy the recordset object in the called function. Since the recordset is passed ByRef, I'm inclined to believe that closing the recordset and setting it to Nothing from the calling routine also destroys it in the called function. What's more, there's a part of me that thinks that the local-level variable in the called function gets destroyed upon completion of the function, but I realize that an auto-destroyed variable is different than an object, which must get destroyed manually even at the local level.

So the question is, do I have to destroy the function recordset object somehow, or does destroying the recordset object at the end of the calling routine destroy both instances? Using the code example below, does the GetReadOnlyRecords recordset get destroyed when rstSample gets destroyed, or do I have to do something else to destroy it also?

Another reason I'm asking is because, in addition to the fact that I've wondered the above already, from another function I also call the GetReadOnlyRecords function, but I don't actually need the recordset. (See: Private Function ChkDataNew() As Boolean). In there I only need to know if the resulting recordset contained any records, so instead of creating an instance of a recordset in the calling function, I simply say "If GetReadOnlyRecords(strSQL).RecordCount>0 Then". So in that case I don't have a recordset to destroy in the calling function, which suggests that nothing closes/sets-to-Nothing the original recordset in the GetReadOnlyRecords function. Obviously I could bring in the recordset to the calling function, but it didn't seem necessary since I could get all the information I needed right in the "If" statement. So if destroying the recordset from the calling function does destroy it in the called function, am I then required to create a recordset in the calling function just so I can destroy it, even though I don't otherwise need it there?

Option Explicit

    'create a module-level, Public reference to a Connection object
    Public objConn As ADODB.Connection

    'create a string constant with the dB path
    Private Const strDBPath As String = "C:\MyPath.accdb"

    Private Const strConString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                "Data Source=" & strDBPath & ";" & _
                "Persist Security Info=False;"

Public Sub OpenDB()
    'create a new instance of the Connection object
    Set objConn = New ADODB.Connection
    objConn.Open strConString
End Sub

Public Sub CloseDB()
    objConn.Close
    Set objConn = Nothing
End Sub

Public Function GetReadOnlyRecords(strSQL As String) As Recordset

    'this routine presumes the Database already has an Open Connection
    'we can't risk possibly opening it twice

    Dim rstTMP As ADODB.Recordset

    Set rstTMP = New ADODB.Recordset
    rstTMP.Open strSQL, objConn, adOpenStatic, adLockReadOnly, adCmdText
    Set GetReadOnlyRecords = rstTMP

End Function

Private Sub UpdateData() 

    Dim rstSample As ADODB.Recordset
    Set rstSample = New ADODB.Recordset

    OpenDB
        strSQL = "SELECT * FROM tblExample"
        Set rstSample = GetReadOnlyRecords(strSQL)

        rstSample.DoStuff

        'Destroy the Recordset object
        rstDrawings.Close
        Set rstDrawings = Nothing
    CloseDB

End Sub

Private Function ChkDataNew() As Boolean

    Dim dtNewDate As Date
    Dim strSQL As String

    ChkDataNew = False
    Set dtNewDate = wsMySheet.Range("QDate").Value
    strSQL = "SELECT tblExample.[Date] FROM tblExample WHERE tblExample.[Date] = " & dtNewDate

    OpenDB
        If GetReadOnlyRecords(strSQL).RecordCount > 0 Then
            ChkDataNew = True
        End If
    CloseDB

End Function

Tim Williams Tim Williams · Accepted Answer · 2014-07-17T20:39:53

When setting an object reference to Nothing, you're really just "disconnecting" the reference, and not (directly) affecting the referenced object. A referenced object will be retained as long as there is at least one reference pointing to it: when there are no remaining references it will be automatically garbage-collected (though perhaps not immediately).

Any recordset references in your functions will go out-of-scope as soon as the function completes (assuming they are local variables and not Globals), so you don't need to worry about those.

Technically you do not need to set object references to Nothing before exiting a method: however some programmers consider it "good practice".

On "Close" MS says:

Using the Close method to close a Recordset, Record, or Stream object releases the associated data and any exclusive access you may have had to the data through this particular object. You can later call the Open method to reopen the object with the same, or modified, attributes.

While a Recordset object is closed, calling any methods that require a live cursor generates an error.

So, closing a recordset will not result in any references to it not working: you can still call Open for example, but other methods such as (e.g.) MoveNext will raise an error when the recordset is closed.

http://msdn.microsoft.com/en-us/library/windows/desktop/ms675814(v=vs.85).aspx

Passing recordset object destruction in VBA

1 Answers