Set DB = Nothing (2003 sp2)

Topic

New Reply

Two questions: What happens at the line Set DB = Nothing? I thought it clears memory or something but I am uncertain. What happens if you exit the procedure because of an error before you get to this line? Is something left undone?

Reply | Quote

Replies

A line such as Set DB = Nothing can be used to release the memory used by an object variable.
In general, VBA does this automatically when you exit a procedure (sub) or function the normal way (because the code reaches End Sub or End Function, or because you execute a line Exit Sub or Exit Function.
If you encounter an unhandled error, all variables will be reset and the memory used by object variables will be released.
So in many cases, setting an object variable to Nothing is not strictly essential because it will be done automatically anyway.
But there is a bug in the way recordsets are handled – memory is not always released automatically. So it is a good idea to always set variables of type Recordset (DAO or ADO) to Nothing.
And if you don’t need an object variable any more halfway through a procedure or function, you make its memory available immediately instead of at the end of the function or procedure by setting it to Nothing. This doesn’t matter much for “small” objects, but it might really help if you open a large recordset.

Reply | Quote

Does that mean you dont have to close these object variables, or again is it good practice too?

Reply | Quote

Closing a recordset and setting a recordset to Nothing is not the same.
Closing a recordset means that you break the connection with the database, but the basic structure is still in memory. Setting a recordset variable to nothing releases that memory.
It is always a good idea to close a recordset as soon as you don’t need it anymore, because an open recordset consumes resources and may lock things for other users.

Because of the problem mentioned in my previous reply, I’ve made it a habit in Access to use error handling in any procedure/function that uses object variables, and to close recordsets etc. and set all object variables to Nothing in an exit section that is always executed. Something like this:

Sub Test()
‘ Declarations
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

‘ Activate error handling
On Error GoTo ErrHandler

‘ Assign a value to the variables
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(“tblTest”, dbOpenDynaset)

‘ Code to process the recordset goes here
…

ExitHandler:
‘ The “exit handler” will always be executed, even after an error
‘ Make sure that no new errors occur
On Error Resume Next
‘ Close the recordset
rst.Close
‘ Release object variables
Set rst = Nothing
Set dbs = Nothing
‘ And get out
Exit Sub

ErrHandler:
‘ Display a message box
MsgBox Err.Description, vbExclamation
‘ And jump to the “exit handler”
Resume ExitHandler
End Sub

Reply | Quote

I knew they were different functions.

This is good info to know, thanks Hans.

Reply | Quote