Calling DAO Close

[BRZ-Ryan]

Should this be called after I run a query in VBA? Will this affect Access's access to this db, or is the close only in reference to the instance called\created by VBA?

    Set db = CurrentDb()
    db.Execute "UPDATE Security SET Role='Deleted' WHERE ID=" & intID & ";"
    db.Close
    Set db = Nothing

[June7]

Only pertains to the declared object and the Close is not required. Access will discard the object when the procedure ends, although explicitly closing and clearing objects is considered 'best practice'. However, don't even need the db variable.

CurrentDb.Execute "UPDATE ..."

[ssanfu]

The rule I learned was:

If you open it, close it
If you create it, destroy it.


Example:

   Dim db As DAO.Database
   Dim rs As DAO.Recordset

   Set db = CurrentDb()
   Set rs = db.OpenRecordset("SavedQuery")
   If Not (rs.BOF And rs.EOF) Then

      ' do something here

   End If

   rs.Close               ' opened it, so close it
   Set rs = Nothing   ' created it, so destroy it
   '   db.Close          ' Did not open it, so do not close it
   Set db = Nothing   ' created it, so destroy it

End Sub

BTW, "Currentdb.Execute " is faster for action queries because it bypasses Access and is executed in/by Jet. But if you have a lot of calls to open or manipulate recordsets, it is faster to use the "Set db = CurrentDb()" construct.



My $0.02.......