Everyone always has trouble with this whole idea, so I try another tack:
"Microsoft has promised that VBA will always free up object variables and
all resources associated with them when they go out of scope, and that
therefore you do not strictly need to do this. Thus, if you trust Microsoft
that much, never bother to close or set to nothing and everything will be
If you believe there may be one or more bugs in a feature that Microsoft has
implemented, you may decide it is in your best interest to explicitly close
what you open and always set object variables to nothing, without exception,
just in case one of these bugs would keep Access from being able to close or
would tie up system resources while your app is running. Its up to you."
Maybe people will listen to it this way? :-)
Terry Kreft <terry.kr...
@mps.co.uk> wrote in message
> Hi Bill,
> In theory all objects are set to nothing when they go out of scope, in
> practice it doesn't always happen.
> IMO, always set to nothing, if you hit the problem where the ROT isn't
> cleared properly it's the devils own job finding where it's happening, far
> easier in the long run to get in the habit of removing the objects
> Bill Berry wrote in message ...
> >I want a rule-of-thumb on whether every procedure that uses a local
> >recordset (or other DAO) object variable needs to have Set
> >at the end.
> >I understand that there are many dark corners and details to this issue,
> >I'mmainly concerned with the simple scenarios exemplified below:
> >I'm writing a function (that may be called hundreds of times per day)
> >uses a recordset object to look up some data. I'm just returning a string
> >value from the function, not any objectreferences. All variables in the
> >procedure are local-to-the-procedure in scope. If one of those variables
> >a recordset, will it automatically be "set to Nothing" when it goes out
> >scope (i.e. the procedure finishes) or do I have to manually do a
> >Set<variable>=Nothing ?.
> >I want to do the right thing to keep memory "clean and tidy", but I don't
> >want to have (lots of?!) Set <variable>=Nothing statements which are
> >functionally redundant.
> >Please help me with the straight scoop on this basic situation. TIA
> >Bill Berry