[AccessD] Ac2013 running out of resources

[Bill Benson]

Changed all code as you suggested, in all procedures. While debugging other
things along the way, I found myself re-initiating routines at various
points, and found the resources issue to be an accumulative thing. This is
to say, if I had an error running one report due to a syntax issue, and I
reset VBA, then ran again and ran into another debug error (still not out
of resources) - stopped VBA and fixed - then finally ran again with no
errors ... I encountered out of resources error much earlier in the final
running than I had when opening the database fresh and running all routines.

I saw no reason to close a QDF which was not Opened, but I went with your
code anyway. Personally I hate error handline because it doesn't show the
line of code where the failure occurs. In any case, same problem occurred -
out of resources. There are schools of thought which say to leave out error
handling and let the code fail where it fails, allowing the developer to
resolve as they see fit in future editions. Anyway, I left your error
handling in but still ran out of resources, as I said.

I do not know what you meant by my use of multistatement lines.

So I am back to where I was, for all both our efforts - no closer to a
solution, and as I have no hair left to pull out, I have started pulling
the caps off my teeth.

Grrrrrrrrrrrr

I really, really think this has to do with outputting to PDF. That strikes
me as something that could be a RAM intensive task, since it has to run a
report, holding both the query results and the formatted report in memory,
before executing a specifically-formatted file save. If somehow Access
keeps all that in memory in between each outputting and doesn't relinquish
the resources, I could see this as a potential source for wear and tear - I
just don't know what to do about it.



On Fri, Jun 5, 2015 at 9:35 AM, Jim Dettman <jimdettman at verizon.net> wrote:

>
> 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
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>