Add Opened Recordsets in Collection and Close On-Demand

Question

I have a function in which a recordset is opened using module level declared variable rst. The variable is not closed on the completion of the function as it is used by another sub. It may be possible that before closing of the variable and setting it to nothing, the same function may also be called again which sets rst to new recordset causing the previously open recordset to become unavailable for use in the aforementioned sub.

Is it possible (and if yes then how) to store/add the recordset into collection or dictionary so that it can be used later on even if function is called multiple time. I tried using collection method and the recordset is stored into it successfully but can't be recalled or used. I used in the following manner:

Private m_ColRS as collection

In the Function Used:

If m_ColRS Is Nothing Then Set m_ColRS = New Collection

m_ColRS.Add rst, sTableName

where rs is the declared variable for recordset and sTableName is variable table name for opening recordset.

In the Add Watch window, it shows the collection containing added recordsets but I don't know how to use the recordset from the collection and how to close/set to Nothing on completion of the purpose of opening it.

Best Regards

Answer 1

You can, but as soon as the close the original recordset, so will it be in the collection.

To "keep" it, use the Clone method:

Public Sub TestCollection()

    Dim rst         As DAO.Recordset
    Dim rstColl     As DAO.Recordset
    
    Dim sTableName  As String
    
    sTableName = "Table1"
    Set rst = CurrentDb.OpenRecordset(sTableName)

    If m_ColRS Is Nothing Then Set m_ColRS = New Collection
    m_ColRS.Add rst, sTableName
    
    ' Set rstColl = m_ColRS(sTableName)
    Set rstColl = m_ColRS(sTableName).Clone
    
    Debug.Print "rst", rst.RecordCount
    Debug.Print "rstColl", rstColl.RecordCount
    
    rst.Close
    
    ' Will fail
    ' Debug.Print "rst", rst.RecordCount
    ' Will fail if method Clone has not been used.
    Debug.Print "rstColl", rstColl.RecordCount
       
End Sub

It might be simpler to hold the recordset in a public or static variable.