[AccessD] Ac2013 running out of resources

Sat Jun 6 11:48:05 CDT 2015

This 'Ac2013 running out of resources' topic has turned into an interesting
read.

Please forgive if some of this is repetitive.

I have this problem a few times and over 90% of the time it is due something
I did wrong in the VBA code.

This may be off the wall, but could you be short on disk space and/or the
pagefile is too small?  

On windows 7 get to Control Panel. When in System Properties select
Performance Options. Then select the Visual Effects tab.  Select Adjust for
Best Performance.  Then go to the Advance Tab, select best performance for
Programs.  For Virtual Memory I have that set for 65,534 MB. Virtual Memory
is the pagefile.

BTW, I use Excel has my report writer this I needs lots of memory.

I do lots of VBA, in 2003, and I follow most of the rules others have
suggested on this blog.  It is old but it works.

Many, many years ago I use to write complex code to impress friends, now I
write to make the customers happy.  Only a couple of times in 20+ year's
customers even wanted to see the code.  So I tend to break the code into its
individual components.   Thus instead of one complex statement, I'll use a
half dozen lines of code, with a few comment lines thrown in.  This makes it
easer to find errors late a night when very tired.

Thus I tend to try to reduce complexity in the VBA code and with the SQL
commands such as removing complex joins or where-clauses.  

Thus to get around complex 'joins' or complex Where-Clauses a pre-defined
temp table is reset.  One pass is used to just extract data from the main
data source.  

If using something like ODBC, etc., try to use server side cursors with the
minimum of where-clauses this keep most of the data on server, else Access
might try to bring everything over to do the selecting and filtering on the
local machine.

For each 'join' do an Update pass filling in the appropriate fields within
the pre-defined temp table.

For complex Where-Clauses have some flag-fields that are turned ON or Off
for multiple passes thru the pre-defined temp table eventually arriving at
the data set you are looking for.  You will eventually have a master select
field like "UseThisRecord."

This is a clunky way to get around some problems, but this is method from
the very old IBM Unit Record Machine days.   Yes I am that old.

Enjoy.

Sincerely,
Ed.

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

David, I was running only one query. I could do what you suggest about
using a where clause for the report, rather than changing the SQ but the
resource creep is due to the outputting of the report, not the report
itself or the query.

Every measure so far proposed, such as closing the querydef, setting
objects to nothing, making the report based on separate queries (I actually
ran make tables and tried running against those temp tables), opening the
report before outputting.... nothing helped and I think a lot of times
seasoned developers can trick themselves into assuming some small tweak has
bearded the lion in its den in old days... I don't know if those techniques
are valuable, best practice or otherwise, but in my case they were of no
discernible improvement to performance.  But I do thank Jim and you for
ideas to try out.
On Jun 5, 2015 1:16 PM, "David McAfee" <davidmcafee at gmail.com> wrote:

> The reason that I open the report in acPreview prior to OutputTo PDF is
> because of the WHERE clause that I am using for the report.
>
> By everything I meant the dynamic SQL.
> Your method might be faster, but I try to use saved queries when possible.
>
> When I loop through recordsets I'll usually use a hidden form (or hidden
> field on a form) to store query parameters.
> From my (bad) memory it looked like you were basically running two
> different queries as the source.
>
> One with FY data and one without?
>
> Where do the input parameters for the sub come from? Are they fields on a
> form and a button is pressed to run the sub?
>
> How many records does this return?
> "SELECT DISTINCT B.ID, B.Name AS [Engagement Manager], B.[first Name]"
> as FirstName, B.Email FROM ([User Information List] AS B INNER JOIN [PDR
> Tracking] AS A ON B.[ID] = A.[Tax Manager]) LEFT JOIN tblCityRegion ON
> B.City = tblCityRegion.City WHERE tblCityRegion.Region=""West"""
>
>
> How many records does the query "Deliverables by Eng Manager" return if
you
> return all records (all managers & FY) for the given dates?
>
> Something like
> SELECT neededFields
> FROM SomeTables
> WHERE [Deal Status] like 'Signed*'
> AND tblCityRegion.Region='West'
> AND [Sign Date] BETWEEN #" & strStartDate & "# AND #" & strEndDate & "#"
>
>
> Why are you deleting and inserting into tblBatch reports near the end?
> Can you just update the record(s) instead of deleting them and reinserting
> them with additional data?
>
>
>
>
>
> On Thu, Jun 4, 2015 at 5:04 PM, Bill Benson <bensonforums at gmail.com>
> wrote:
>
> > If I don't do it all in VBA, then the user will be stuck sending each
> > report individually, no?
> >
> > Why do you open the report first, what is that accomplishing? I don't
see
> > any pause occurring in your code, but what happens when you are opening
> the
> > report in preview view?
> >
> > I don't understand what you are saying regarding changing the WHERE
> clause
> > to a string, is that a way of limiting what the report shows? Isn't that
> > what I am accomplishing by editing the query sql?
> >
> > Would I be better off changing the recordsource for the report, by
> creating
> > a new query each time rather than the same query?
> >
> > I will try opening the report as you do, then printing it, and then
> closing
> > it - so long as this doesn't interrupt the process.
> >
> >
> --
> 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