[AccessD] Ac2013 running out of resources

[Jim Dettman]

Most notably:

1. Turned error handling on.

2. Used .Close on objects where appropriate.   Setting objects to nothing
alone does not do the job, especially with record sets.  You must both
.Close and set to nothing, otherwise you can easily have a resource leak.

3. I also rearranged where you were doing some things.  i.e. a Set = nothing
before a Set.  As a general rule, I cleanup as soon as I'm done with
something, not when I go to do something else.

  You also used multi-statement lines, which personally I've never trusted.
No hard reason for that. The formatting doesn't bother me...I used to work
with Wang Mini's and later NPL, and just about everything was
multi-statement lines.  But in VBA I've always found it best to keep things
simple. 

4. Changed the CurrentDB().Execute to CurDb.Execute so your not creating a
new db object on each of the executes.   You had it that way on one, but not
on the other.

5. Removed the refresh of the QueryDefs.   This is an expensive process and
only needed if your creating a new querydef on the fly.  Since were updating
an existing one, there's no need for it or the over head. 

Jim. 

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Bill Benson
Sent: Friday, June 05, 2015 09:15 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Ac2013 running out of resources

Update: After changing the report to run against a temporary table, instead
of a query, I got up to 66 reports before Access ran out of resources. I am
sure it has nothing to do with the query. I suspect it has something to do
with Access "hanging on" to its memory of what it has outputted to PDF,
rather than relinquishing whatever memory it required to do so. I have yet
to try Jim's changes, I am still trying to work out what the differences
are in the code. The problem, Jim, is that I have actually four (4) of
these different reports, the code slightly different for each.
By Partner - West
By Partner - East
By Manager - West
By Manager - East
So whatever you've done to this one I would have to make the same in the
other. Before I spend time trying to implement these changes could you tell
me what you have changed and why? Not sure what is a substantial change and
what is just extras, like error handling.
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com