Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Set DB = Nothing (2003 sp2)

    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?
    Thanks
    chuck

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Set DB = Nothing (2003 sp2)

    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.

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Set DB = Nothing (2003 sp2)

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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Set DB = Nothing (2003 sp2)

    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

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Set DB = Nothing (2003 sp2)

    I knew they were different functions.

    This is good info to know, thanks Hans.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •