[AccessD] Open Recordsets when Out Of Scope?

Dan,

See:

ACC2002: To Help Prevent Database Bloat, Explicitly Close Recordsets

http://support.microsoft.com/default.aspx?scid=kb;en-us;289562

  Basically it's a bug.  Access isn't always great with its housekeeping.

 It was a good article, but I do disagree with a few of the points in it.
In the "ounce of prevention" section, there's one point:

" Avoid memo fields in a table with a lot of records; too many memo fields
quickly bloat a file"

  I think they meant to say use text fields if possible rather then memo
fields, but the implication of the statement is that I shouldn't use Access
to store data!

 and on:

" Avoid basing a combo or list box on table data.  Doing so creates a hidden
query".

  I'm not sure what the point of that was.  The alternatives are to base a
combo on a query (no difference), base it on an SQL statement (still creates
a hidden query), or use a callback function, which I think would take up a
lot more space then a query def.  But really, the whole purpose of the
hidden query is to boost performance and except in very limited cases, you
do want it, not matter how much space it takes up.

    Also on the point of the single record VBA project file vs. multiple and
the inability for compaction to occur: that's only true with the DAO compact
method.  Calling compact from within the Access UI does indeed compact the
VBA project file.  No need to import everything into a new MDB for that to
occur.


Jim Dettman
President,
Online Computer Services of WNY, Inc.
(315) 699-3443
jimdettman at earthlink.net

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Dan Waters
Sent: Friday, June 20, 2003 2:26 PM
To: Database Advisors
Subject: [AccessD] Open Recordsets when Out Of Scope?


To:  Susan Harkins and Charlotte Foust

Authors of 'Preventing and recovering from database bloat' in the latest
issue of Inside MS Access.

Thanks for this article!  More stuff I didn't know. . .

But - If an recordset or querydef object variable is defined inside a
procedure how can a recordset or querydef stay open after that procedure is
out of scope?  Is this a known bug?  Happens under certain circumstances?

I had read in the ADH that all recordsets are closed when you go out of
scope, so now I don't add code to explicitly close them (except when closing
is needed inside the procedure).

Thanks,
Dan Waters


_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com