Access 97 subreports REALLY slow

[Einsamherz]

I'm working on an Access 97 application.

The problem I'm having is that there is one report that takes >3 minutes to run (~30 pages and "HEAVY" calculations). The speed itself is not the major issue.

The problem is that this report is actually 3 subreports "glued" together on this report and it wants to RERUN the subreports before it prints the report!!

The reports run, and they print FAST (immediately) alone. I'd RATHER NOT do that though (it's all about the keeping it simple for the users... )

Any help on this topic would be appreciated.

[Einsamherz]

^ bump ^

Anyone ? ? ?

[vass0922]

have you ever thought about possibly using a module.. one to call the form with the given recordset and if they call for the report use that SAME recordset to call the report?

[Imanage]

Along the same idea, is there a way to restructure the query's for the main report or each of the subreports to use a single query or a single query written out to a temporary table. Then redo your report to use that one table or query. Use a macro or a module to run the make-table queries first and then the report.

Good luck.

[Einsamherz]

I've written modules for some other reports. (like 10min long reports...that one is usually between 1-12 months worth of data)

There are a number of SUM()s through the process.....I've been looking at ways to reduce the data that it goes through (like grouping past years' items, which is done for other info.)

I was more perplexed by it having to run again to print the report, when it's RIGHT IN FRONT OF YOU, NOW... ( just a little frustrated with M$... )

Thanks for your help!

[vass0922]

ya I know what ya mean Eins.. rather frustrating that Access doesn't use the same recordset in the background
I imagine there's SOME reason coughexcusecough that they dont do that coughthatmakessensecough so its all for the benefit of the consumer

[Einsamherz]

After looking at it (and having a nice restful weekend)....I have a theory to check tomorrow.....(systems-gods willing)

I seem to remember there being 3 levels of sub-reports.....I know 2 works OK, so maybe I can just simplify it by taking one level off and try it again.

I really would like it to run faster for the users. (even though the system running the report most is faster than the PDC.....)


Thanks again for all your help.

[Rhino]

Are they calculatios which you can perform in the queries? I'm not sure if it would make it faster or not.

[Einsamherz]

The calculations _are_ done in queries. They are things like "Add up all the inventory totals for these parts."

Why can't Access have stored procedures instead? (Don't get me started on SQL server...it's installed, but dragging for some yet undiscovered reason.....I suspect data type problems, but can't be sure.)

Guess the users will just have to get adjusted to the 3 report screens.

[Rhino]

What if you first made made a form, performed the calculations in code, and then made the report from the form?