Re: Do I need to set object = nothing?

It is good programming practice to close what you opened (by only what you
opened), and to dereference your objects.

In a perfect world, this would be unnecessary. Access would reliably close
anything you opened and dereference any objects you set as soon as the
procedure terminated and the objects went out of scope. We don't live in a
perfect world. There were bugs in Access 97, such that it was impossible to
close Access if you did not explicitly close the recordsets you opened.
Access would minimize to the taskbar when you tried to close it, and the
only solution was to use the task manager (Ctrl+Alt+Del) to kill it.

Even after Microsoft fixed that bug, later versions still had problems with
running out of databases if you did not explicitly set your database
variables to Nothing if they had already been set to CurrentDb(). Haven't
tried recently, but it was easy enough to do. Just run a loop that call a
function a few hundred times, and in the function set a Database type
variable to CurrentDb.

So, is it worth the effort to explicitly close what you opened and
de-reference your objects? Up to you, but the pain we had with the A97 bugs
(other causes as well as the Recordset one), and the difficulty of tracking
and fixing those left many of us rather fastidious about cleaning up after
ourselves instead of expecting the software to do it perfectly.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Paul H" <nospam@xxxxxxxxxx> wrote in message
news:W92dnbnL3ep_h3LZRVny1Q@xxxxxxxxxxxxxxxxx
A typical chunk of code......

Set db = CurrentDb
Set rs = db.OpenRecordset("tblFoo")

<Do some stuff here>

'How much of the stuff below do I need?
'Do I need to close the recordset?
rs.Close
'Do I need to close the database object?
db.Close
'Do I need to set the recordset to Nothing?
Set rs = Nothing
'Do I need to set the database to Nothing?
Set db = Nothing

I have been doing it this way for years, is this the right way?

Thanks,

Paul


.

Relevant Pages

  • Re: Connection types and speeds
    ... All recordset inserts seen to consequently have 0ms execution time, ... actually written to the database while the code has regained control ... Every time you use CurrentDb, ... RecordsetClone of a form) when a transaction was rolled back, ...
    (microsoft.public.access.queries)
  • Re: Scope of Db and Rst objects: General questions
    ... created by CurrentDb. ... object and know for sure that we are able to dereference it. ... > a Recordset object is opened like this: ... > Private Sub Example ...
    (microsoft.public.access.modulesdaovba)
  • Re: SELECT Queries. A straight answer.
    ... Of course, I use a cached database variable wrapped in a function, ... rather than repeatedly using CurrentDB. ... Lyle Fairfield (not necessarily an exact clone, ... released, and the returned Recordset ...
    (comp.databases.ms-access)
  • RE: Recordset Tutorial
    ... when i enter CurrentDb in my AfterUpdate event property i find that when i ... > A recordset can be either a table or a query - simple as that. ... > reference to a database object: ... > Dim dbf As Database ...
    (microsoft.public.access.formscoding)
  • Re: It record exists then...
    ... Dim rs As Recordset, db As Database ... Set db = CurrentDb ...
    (microsoft.public.access.formscoding)
Loading