Query runs fine-Report takes forever (1 Viewer)

[ctaylor]

I have an MS Access 2000 database which runs several underlying queries and then pulls rusults via a date parameter. All calculations are done in the query and the query takes about 4 seconds to run.

I then have a report with no special formatting which merely posts the results of the query without performing any additional calculations, sorting, or grouping on the report. The report takes upwards of 6 minutes to run.

What is happening? I thought the report might have become corrupted so I created a new report with the report wizard which duplicates the 6 minute run time and MAXes out the CPU.

I'm confused.....

 

[Pat Hartman]

Just a thought - if you have "page x of y" as the report's page number, try changing to just a simple page number "page x". The "of y" part forces Access to run the query and format the entire report in memory to calculate the total number of pages, and then it runs the query and report for real the second time.

 

[ctaylor]

Thanks for the tip Pat, I think the problem had to do with the database actually becoming corrupted. I re-wrote all the querries in SQL view in a brand new database, imported all the tables, and recreated the reports. This appears to have solved the problem.

Any ideas on how to speed up a database in general would be appreciated though.