rs.Close

[thebigthing313]

Hello all,

So I just found out that it's not a good idea to rely on Access to clear any memory used by recordsets simply by removing the reference (Set rs = nothing). Upon Google searching, I found that I should clear the used memory by closing the recordset (rs.Close) and then remoe the reference to it (Set rs = nothing). I am now in the process of correcting this in all my code, however I have a question.



On this link, http://allenbrowne.com/ser-29.html, there is a suggested construct for procedures that use recordsets.

While I am more than OK just accepting Allen Browne's suggestions without question, I would like to understand it nonetheless

    
Sub MyProc    
On Error Goto Err_MyProc
Dim db as Database
Dim rst As Recordset

        Set db = CurrentDb()
        Set rst = db.OpenRecordset("MyTable")
        'Useful code here.
        rst.Close            'Close what you opened.

Exit_MyProc:
        Set rst = Nothing    'Deassign all objects.
        Set db = Nothing
        Exit Sub

Err_MyProc:
        'Error handler here.
        Resume Exit_MyProc
End Sub

From my understanding, with the code above, if between opening the recordset and rs.close there is an error, then the recordset would never be closed because of error handling right?

Would it be better to have something like:

    
Sub MyProc    
On Error Goto Err_MyProc
Dim db as Database
Dim rst As Recordset

        Set db = CurrentDb()
        Set rst = db.OpenRecordset("MyTable")
       'Useful code here.

Exit_MyProc:
        If Not rst Is Nothing Then rst.Close            'Close what you opened.
        Set rst = Nothing    'Deassign all objects.
        Set db = Nothing
        Exit Sub

Err_MyProc:
       'Error handler here.
        Resume Exit_MyProc
End Sub

?

A lot of sample codes, especially from Browne, where recordsets are used, have rs.close not within the cleanup code (After Exit_MyProc: in this case), is there a reason for this? Are open recordsets cleared on error automatically?

Is my method valid, or necessary?

[June7]

It's my understanding that variables and recordset objects automatically close when procedure completes. Even if the code execution is branched to an error handler the procedure still completes. I have lots of code where recordset objects have not been explicitly closed and code runs fine - bad practice or not, still works. Even when code execution is interrupted by error and not branched to error handler but just killed in the debugger, I have not found the recordset object is still open. I run the code after fixing and no problem.

Where I find real issues with not closing objects and setting to Nothing is with application collaboration code for Outlook and Excel (manipulating other apps by VBA).