[AccessD] Ac2013 running out of resources

[Bill Benson]

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
>