Using a VBA sub to close DAO recordsets as part of end-of-job clean-up 1

[PGAC]

Dear all,


As part of Access (2003) VBA (Win XP), I am aware it is good practice to close any objects to explicitly open in any sub/function so that these objects don’t end up “floating around” in memory.

The current way I do this is:

==============================================

Public Sub Example1

Dim rstA As DAO.Recordset
Dim rstB As DAO.Recordset

etc
etc
etc

ExitCode:
If Not rstA Is Nothing Then
Call rstA.Close
Set rstA = Nothing
If Not rstB Is Nothing Then
Call rstB.Close
Set rstB = Nothing
End If

End Sub

==============================================

What I wanted to know if there were any cons to the below example (which has been edited to the 'bare bones') and whether it is acceptable or not:

==============================================

Public Sub Example2

Dim rstA As DAO.Recordset
Dim rstB As DAO.Recordset

etc
etc
etc

ExitCode:
Call CloseDAORecordset(rstA)
Call CloseDAORecordset(rstB)
End If

----------------------------------------------

Public Sub CloseDAORecordset(ByRef rst As DAO.Recordset)

If Not rst Is Nothing Then
Call rst.Close
Set rst = Nothing
End If

End Sub

==============================================

The reason for my asking this comes from the concept of re-usability of code. Likewise, by declaring the recordset variable as ByRef, it is referring to the object in memory, not a copy of (like ByVal). This also makes codes in other functions/subs tidier as I only have to use one line of code per recordset to ensure the object is cleared from memory which is very useful as I have a sub that needs 6 DAO.recordsets to carry out the job at hand.

I have tried the above and it does set the recordsets to nothing, which is what I would expect from a ByVal declaration (and trying to view records after the external sub has set them to nothing produces an error which is what would be expected since it is an attempt to access something that effectively no longer exists).

The point is – is this good practice or bad? I’d appreciate any criticisms, pros and cons. Many thanks!

 

[PHV]

Seems good for me, except the spurious End If ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[RomeERome]

From my experience, you don't have to call a function or create a separate subroutine to close the recordset.

You can just type the code below at the end of your subroutine.

rstA.Close 
rstB.Close

Set rstA = Nothing
Set rstB = Nothing

 

[Andrzejek]

I believe if you declare your rst as procedure object, the scope is only for that procedure. Which makes it available to that procedure only and not anywhere else. So as soon as you are done with that procedure, any variable declared there is no longer - it is out of scope.

Public Sub Example2

Dim rstA As DAO.Recordset
Dim rstB As DAO.Recordset

etc
etc
etc

rstA.Close
rstB.Close[blue]
Set rstA = Nothing
Set rstB= Nothing[/blue]

End Sub

So the [blue]BLUE[/blue] part of code is not really necessary, it is going to Nothing at the End Sub anyway, but I do Set object = Nothing, too. Just makes me feel better about my code

Have fun.

---- Andy