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 (mrstSchemaFields Is Nothing) Then
mrstSchemaFields.Close
Set mrstSchemaFields = Nothing
End If
If Not (mrstSchemaTables Is Nothing) Then
mrstSchemaTables.Close
Set mrstSchemaTables = 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 DAOCloseAndReleaseRecordset(ByRef 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()
DAOCloseAndReleaseRecordset mrstSchemaFields
DAOCloseAndReleaseRecordset mrstSchemaTables
End Sub
