Why close a Recordset?

  • Thread starter Thread starter Warrio
  • Start date Start date
W

Warrio

Hello!

Why do we need to close a recordset used by code into a procedure, since a
variable declared into a procedure has a life time of the procedure,
therefore it doesn't exist anymore in the memory.. does it?

Thanks for any good clarifications!
 
Warrio said:
Hello!

Why do we need to close a recordset used by code into a procedure,
since a variable declared into a procedure has a life time of the
procedure, therefore it doesn't exist anymore in the memory.. does it?

Thanks for any good clarifications!
Click to expand...

The automatic house cleaning that should happen in Access/VBA is not 100%
reliable. If you rely on it you might get away with it and you might not. It's
simply a better practice to clean up after yourself.

It has been suggested that this flaw is restricted to DAO objects so perhaps you
could rely on the built in garbage collection for everything else.
 
As a rule, you *can* allow vars to go out of scope.

I have a few long running ms-access applications where I allow my several of
the class objects written in ms-access to rely on scope.

As for the closing of the reocrdsets? Well, ms-access shares the same
compiler as VB6, as a "good" practice, it is suggested that you close
reocrdsets implicit.

There has been a number of bugs, and problems that do rear their ugly head
if you don't clear up things your self.

Two items that come to mind is file size bloat (it seems that JET decides
when to clear out things when you don't close...and often it NEVER gets
around to cleaning out some temp stuff used). The other item was the
following problem:

http://www.mvps.org/access/bugs/bugs0005.htm
 
Most if not all of the problems related to this particular
recommendation are found in vbscript/ASP. If you are using
Access VBA with local dynamic variables this is mostly a
non-issue.

therefore it doesn't exist anymore in the memory.. does it?
Click to expand...

No, it probably doesn't work quite like that. A recordset
should close AFTER the reference count falls to zero, but
nobody ever said that it would close IMMEDIATLY after the
reference count falls to zero. In MS Basic and in .Net
you KNOW that actions like that would be delayed, but in
VB it is less clear. When you single step through your
code you see recordsets closing automatically when the
variables go out of scope, but when it is running at full
speed, who knows what happens?

(david)
 
Thanks for all the advices

but does this rule of cleaning my self the recordsets applies also to the
forms recordsets? when you the form get unloaded?

The problem that I got with this is that the Jet engine wasn't able to open
more than 1024 IDs (can be upgraded to 2048)
but if we want to keep the memory free all the time, will it work if I close
all the recordsets after using them?

Thanks again!
 
Freeing locks is done as a background process: Jet
needs to keep the ID's until the locks are released:
if you want to free the ID's you should use the
Idle method of the application.dbEngine to give
the background processes higher priority.

(david)
 
Thanks for the hint!!



david epsom dot com dot au said:
Freeing locks is done as a background process: Jet
needs to keep the ID's until the locks are released:
if you want to free the ID's you should use the
Idle method of the application.dbEngine to give
the background processes higher priority.

(david)
Click to expand...
 
but does this rule of cleaning my self the recordsets applies also to the
forms recordsets? when you the form get unloaded?
Click to expand...

No, only ones that you create.......

Since you don't create the forms reocrdset...then you don't have to get rid
of it....
The problem that I got with this is that the Jet engine wasn't able to
open more than 1024 IDs (can be upgraded to 2048)
but if we want to keep the memory free all the time, will it work if I
close all the recordsets after using them?
Click to expand...

Gee, I can't say for sure...but give it a try. However, if your code is not
opening the reocrdsets, then likely there is not much you can do here...
 
Thanks again!!

Albert D.Kallal said:
No, only ones that you create.......

Since you don't create the forms reocrdset...then you don't have to get
rid of it....


Gee, I can't say for sure...but give it a try. However, if your code is
not opening the reocrdsets, then likely there is not much you can do
here...



--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal
Click to expand...
 
Back
Top