The rule of thumb is that you should close what you opened (but only what
you opened), and set your objects to Nothing before exit.
Therefore your code should end with:
rs.Close
Set rs = Nothing
Set db = Nothing
but you do *not* db.Close because you did not open it.
You should use db.Close if you had opened another database, e.g.:
Set db = OpenDatabase ("C:\MyFolder\MyFile.mdb")
As for why: in a perfect world, the code would reliably clean up after
itself when an object went out of scope, and would fail if you closed the
wrong thing. Neither of these things happens reliably in Access.
Back in the Access 97 days, there was a bug where Access would not close,
and failing to close your recordsets was one of the causes for this bug. It
was a real pain to track down (particularly in large databases with lots of
recordsets, combined with other causes of the bug as well), so many of us
learned the lesson that explicitly closing was worth the effort.
Just as bad, if you close something you did not open (such as Currentdb),
Access just silently opens the default database again and you probably don't
even know your coding is wrong. Sometimes that does cause problems. If you
close the default workspace, Access reopens it and the default database, but
other connections you had open at the time are lost. This causes weird bugs:
e.g. the RecordsetClone of forms that were open at the time have suddenly
gone out of scope, so you get Error 91 if you try to refer to them.
Likewise, failure to set objects to Nothing can cause problems. Each time
you call CurrentDb(), Access creates another database object, and if you
don't set it to Nothing before exiting the routine, you can run into errors
about too many tables/databases open in some versions of Access.
In summary, it's always good practice to close what you open and destroy
your objects after use (or even before re-use.) Personally, I'm too lazy
*not* to do it: I have better things to do that waste my time debugging bugs
that I could have avoided by cleaning up after myself.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
Steven said:
If I do this:
Dim vUserName, vPassword As String
Dim vAutoNumber As Double
Dim rs As DAO.Recordset
Dim db As Database
Set db = CurrentDb()
Set rs = db.OpenRecordset("Users", dbOpenDynaset)
rs.MoveFirst
rs.FindFirst BuildCriteria("[NetworkLogin]", dbText, fOSUserName)
If Not rs.NoMatch Then
If rs("AutoNumber") = 1 Then
[Forms]![Form1]![UserNumber] = vAutoNumber
End If
End If
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
Question: Do I need the:
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
What part of these do I need? What happens if they are omitted?
Thank you for your help.
Steven
