Is there a need to set Objects to Nothing inside VBA Functions

Question

Hi,

I always read that it is recommended to set objects to nothing, once I am done. But I normally use them only in functions inside forms.
Isn't the reference lost when the function scope is left?
So where is the need to do:

Set db = Nothing
Set record_set = Nothing

Cheers,
Ramon

Answer 1

References are supposed to be cleaned up when the variable goes out of scope. Presumably this has improved with later versions of the software, but it was at one time not reliable. I believe that it remains a good practice to explicitly set variables to "Nothing."

Answer 2

Garbage collection is rarely perfect. Even in .NET there are times where you are strongly encouraged to prompt the system to do garbage collection early.

For this reason, I explicitly both close and set to Nothing recordsets when I'm done with them.

Answer 3

VB uses a so-called "reference counting" garbage collector.

Basically, the moment a variable goes out of scope, the reference counter on the referenced object is decremented. Consequently, when you assign the object reference to some variable, the reference counter is incremented.

When the counter reaches zero, the object is ready for garbage collection. The object resources will be released as soon as this happens. A function local variable will most likely reference an object whose reference count never goes higher than 1, so object resources will be released when the function ends.

Setting a variable to Nothing is the way to decrease the the reference counter explicitly.

For example, you read in a file, and set the file object variable to Nothing right after the ReadAll() call. The file handle will be released immediately, you can take your time process it's contents.

If you don't set to Nothing, the file handle will be open longer than absolutely necessary.

If you are not in this "must unblock valuable resource" kind of situation, simply letting the variables go out of scope is okay.

Answer 4

Here's a couple of discussions on the subject.

http://www.dailydoseofexcel.com/archives/2004/06/07/nothing-keyword/ http://groups.google.com/group/microsoft.public.dotnet.languages.vb/msg/16ea054196879d0c

From my perspective, it costs me nothing to use Nothing, so I do it (almost always).

Answer 5

The very last line of the help topic for "Recordset.Close" in the Microsoft DAO help and the Access Developer Reference is this:

"An alternative to the Close method is to set the value of an object variable to Nothing (Set dbsTemp = Nothing)."

http://msdn.microsoft.com/en-us/library/bb243098.aspx

With that in mind, this article from the Microsoft Knowledge Base entitled "How to prevent database bloat after you use Data Access Objects (DAO)", tells you that you should explicitly close if you don't want your databases to bloat. You'll notice that the article is a little vague about the details; the "Cause" section is unclear, almost to the point of being gibberish.

http://support.microsoft.com/kb/289562

SYMPTOMS: A Microsoft Access database has begun to bloat (or grow rapidly in size) after you implement Data Access Objects (DAO) to open a recordset.

CAUSE: If you do not release a recordset's memory each time that you loop through the recordset code, DAO may recompile, using more memory and increasing the size of the database.

MORE INFORMATION: When you create a Recordset (or a QueryDef) object in code, explicitly close the object when you are finished. Microsoft Access automatically closes Recordset and QueryDef objects under most circumstances. However, if you explicitly close the object in your code, you can avoid occasional instances when the object remains open.

Finally, let me add that I have been working with Access databases for 15 years, and I almost always let my locally declared recordset variables go out of scope without explicitly using the Close method. I have not done any testing on it, but it does not seem to matter.

Answer 6

I usually always put this at the end of my procedures, or call a "CloseRecordSet" sub with it in if I'm using module level ones:

Private Sub Rawr()
On Error GoTo ErrorHandler

    'Procedural Code Here.

    ExitPoint:
        'Closes and Destroys RecordSet Objects.
        If Not Recset Is Nothing Then
            If Recset.State = 1 Then
                Recset.Close
                Conn.Close
            End If
            Set Recset = Nothing
            Set Conn = Nothing
        End If
        Exit Sub

    ErrorHandler:
        'Error Handling / Reporting Here.
        Resume ExitPoint
End Sub

That way however the procedure ends, (be it normally or due to an error) the objects are cleaned up and resources are free.

Doing it that way is quite safe in that it you can just slap it in and it will only do what is necessary in regards to closing, or destroying the recordset / connection object, incase it has already been closed (due to a runtime error or just closing it early as ya should, this just makes sure).

Its really not much hassle and its always best to clean up your objects when you're finished with them to free up resources immediately regardless of what happens in the program.