Useful refactoring/function for DAO object clean-up

[Steve Jorgensen]

Hi all,

I had just participating in a small thread here about when to set recordset
variables to Nothing when I ran into an interesting case in my own code that
highlighted the need for a formal, centralized function to deal with this.

The case that came up was that I have a class module that handles 2 recordsets
that it must clean up at termination, but the termination should not assume
that the recordsets have necessarily been opened. here's the initial code I
wrote for this...

Private Sub Class_Terminate ()
If Not (mrstSchemaFiel ds Is Nothing) Then
mrstSchemaField s.Close
Set mrstSchemaField s = Nothing
End If
If Not (mrstSchemaTabl es Is Nothing) Then
mrstSchemaTable s.Close
Set mrstSchemaTable s = Nothing
End If
End Sub

Note that the database object belongs to the calling code and is not cleaned
up in the class.
This is an obvious cut-and-paste duplication maintenance problem. It would be
easy to make a change, and end up referencing the wrong recordset in the wrong
place, and have the code not do what it should. Furthermore, the code might
not fail with an error, and the problem would only be detected later when
Access might fail to completely close. In an audit of the code, the problem
would be very obscure and hard to track down. In fact, I once spent several
days tracking sown a similar problem by stopping the code at various places,
then trying to close Access, checking the Windows Task Manager, etc. until I
finally got down to the individual function that caused the problem.

By taking advantage of VB's reference parameters, though, it's possible to
create a central procedure to eliminate this issue. For a single recordset
reference, the procedure does the job of checking for Nothing, closing the
recordset, and setting the variable to Nothing after closing it. It might be
overkill in situations other than the above, but removing duplication by using
this same call everywhere means that anything else that you ever want to
always do when closing a recordset can be added in a single place at any time.

' This procedure closes the recordset if it is open, and sets the
' recordset variable to Nothing to allow the closed recordset object
' to go out of scope immediately. Since the variable is passed by
' reference, setting the parameter to Nothing operates on the variable
' passed from the calling function.
Public Sub DAOCloseAndRele aseRecordset(By Ref rst As DAO.Recordset)
If rst Is Nothing Then Exit Sub ' No object referenced, so nothing to do.
On Error Resume Next ' Don't raise error if recordset already closed.
rst.Close
Set rst = Nothing
End Sub
Here's the new version of the original code, now employing the new function.

Private Sub Class_Terminate ()
DAOCloseAndRele aseRecordset mrstSchemaField s
DAOCloseAndRele aseRecordset mrstSchemaTable s
End Sub