Report slow to format, output or print, exceeds system resources

[TomYC]

I have an Access 2007 report, that contains a couple of subreport (actually, one or more subreports of subreports), and it is very slow to format, to print or even to output to pdf (though that is the fastest, oddly enough); it is even slow to toggle from design to view mode. I could live with all of that except...

It will fail after about 10 pages. The report's source is a query returning departments and one department report usually fits onto one page. If my query returns 10 to 12 records (or more), the output fails shortly after formatting the first 10 or 11 pages, with a system rescouces exceeded error message.

Is there a way to correct this, or even to step through the report and gain insight into what it is that is soaking up all the memory? I have tried this on other machines, I have tried making all objects local (as opposed to on networked drives, etc.), there are no linked tables, etc., just a lot of sum over group text boxes on subreport/report footers, and the like.
Right now I'm having to cut my query effectively into segments, and output ten at a time, but that is confusing to the end user, to receive, instead of one pdf with 50 departments and (roughly) 50 pages, five segments of ten pages, marked 'A', 'B' etc.

I can share either the query (a parameter query, referencing other queries tied to a form), or the report or both, but so far a general search on 'slow report' and 'system resources exceeded' has not returned results.
Many thanks,
T.Y.

 

[lameid]

So you are able to run it for all records piecemeal.... In otherwords it is not data causing it to error out?

I recommend you start by posting you SQL statement for your query under the report and listing the groupings in the report.

The other thought is to MAKE A COPY and try deleting groups of controls or groups until it speeds up... Using process of elimination you might identify something there (iterative making a copy and deleting smaller sets of controls).... Way back using Access 97 that is how I found a control that was giving me grief exporting to Word (I think it was word anyay). It turned out that a particular status code was a capital "M" which in this particular font type was wider than the control where as the other 'codes' fit. I expect that particular issue has been fixed by now but I think the diagnostic process is sound.

Another thought, Access seems to run the same query multiple instances of a query when making a report. If you can offload any of the grouping to the query that might help. Also, you might look into increasing the Max~Locks~Per~File registry key (take the ~ marks out).