[FIXED] When should a recordset be closed?

Issue

Let's say I will run various queries using the sub below. Should I set and close the recordset each time?. Or should set and close it just once together with my connection(cn)? Do I need to close my recorset at all if I close my connection?

Sub QueryPC(ByRef ReturnArray() As Variant, COB As Date, PC As Variant, FileCode As String)
    Dim srtQry As String, tmpArray() As Variant

    Set rs = CreateObject("ADODB.Recordset")

    srtQry = "whatever"

    Set rs = cn.Execute(srtQry)

    If Not rs.EOF Then
        tmpArray = rs.GetRows: TransposeArray tmpArray, ReturnArray
    Else
        ReDim ReturnArray(0)
    End If

    rs.Close

End Sub

Solution

Closing the rs in the query as in your code is good. Better still would be if you did the same with your Connection!

The rule - for quite a few years now - is to open a connection as late as possible and close as soon as possible. You've obviously got the connection as a mudule level or even more global variable: this is bad practice. You should create it where you use it and the close it.

Not too many VBA samples do this, possibly because VBA code samples which people copy are usually quite old or because while the people writing the samples are good at VBA they aren't full-time developers.

Cheers -

Answered By - simon at rcl

Answer Checked By - Senaida (PHPFixing Volunteer)