Hi!
I have an Access 97 report based on tables linked to an SQL back end.
The report has 5 subreports in it (unfortunately unavoidable due to the
nature of the report) and performance is quite slow. The query runs
quickly and the report displays the first page in a short amount of
time, but takes quite a while to format each page. This means it takes
a long time to print the report (up to 50 mins to print around 360
pages!) as it formats each page.
My main report has some controls that hide or change captions depending
on the values of other controls on the report, as well as one level of
grouping (both header and footers for that grouping). I have tried to
minimize the number of changes that are done on the fly in the report
and I have experimented with the 'Keep Together' options of my
grouping. My subreports link to the main report by a key that is a
combination of two fields (calculated in the query for the main
report), but is not indexed since it's calculated at run-time. We only
have read access to the SQL back end (it's part of a proprietary
program).
My question is this: Would it be faster to write the data to temp
tables, thereby allowing me to create an index on my key field, and
then base the report/sub reports on local tables rather than linked
tables? What about putting just the objects required for this report
in it's own front end? Are there any other tricks I could use to
improve the formatting speed? This is a complex report, but certainly
not the MOST complex thing I've ever written, so I'm at a loss as to
how to make it better.
Thanks in advance,
Jana