To speed up reports on access tables, you need to look at how you have things set up. Some things to look at:
1. Are the tables indexed? Indexing fields you sort/group on will tremendously help with speed.
2. How many tables are linked in the query the report is based off of? (and how much data are you dealing with?) For example, having 10 linked tables in the query will produce a slow query/report (especially if any of the tables are joined by 2 fields (clustered join).) What I've done in the past to speed up reports where I had multiple, multiple joined tables was break it down. It actually speed things up to run some queries which reduced the recordsets returned by creating temp tables and then link to those temp tables with other tables. I made a procedure which ran when the user requested the specific report filtering specific data. Although it still took a few seconds going through the procedure of creating the temp tables, the overall time was reduced verses having all the tables linked into 1 query.
3. Also, although this is arguable, I would store some data reduntantly (yes I broke the rules of normalization). But having a "grouping" type field of data in the main table helped with queries so I wouldn't have to link in the relational table for the queries. I was dealing with several million records and linking an additional relational table in a query meant slower queries. I thus not only stored the ID number of the relational table in the main table but also stored the text field with it (I kept the relational table for design purposes of data entry). This though is a design decision where you have to weigh how fast you want your data returned verses staying with the rules of normalization. Obviously having 1 big flat table does not follow the rules of normalization, but it does produce faster results with queries (and flexibility) when you need to sort and group on multiple fields as you pay a penalty for each relational table you have linked into a query. Weigh this option carefully though with your design.
4. If you're using Access tables, seriously consider upsizing to SQL Server. I won't go into all the benefits but it does make your life soo much easier.
I wouldn't rely on cursors being the answer to speeding up your reports. I think you'll find that you're most likely opening a new can of worms and the results won't necessarily satisfy your want for speeding up reports. I would instead think about how you can design your tables/fields to minimize the number of tables you need to link into a query by what fields are mostly grouped and sorted on.
Even though it's also arguable, but are you doing a lot of calculations to produce the query results? Storing calculated fields in a table is argued by many as a no-no but if you are totalling millions of records and summing, averaging, etc... on all those records, this can be a time killer for reports on massive records. I had to calculate total kilowatts saved on energy savings for appliances. I had a lot faster reports by storing the total kilowatts saved with the appliance record verses storing only the aspects of the equation and doing the calculation to produce total kilowatts saved when the reports ran. Storing the summed calculation verses cursors to me is a no-brainer but I don't want to start a debate on it (perhaps when I'm healthier).
SQL Views are great and I utilized them a lot. Although I'm not a fan of Crystal Reports, I can't help to wonder though if you might achieve some faster results with Access reports. From the little experience I have with Crystal Reports, I've often wondered if there isn't some overhead with Crystal Reports which you wouldn't necessarily have with Access reports. There wasn't a single thing I wasn't able to accomplish with Access reports that I could with Crystal Reports but I'm guessing some advanced Crystal Report designers would argue that point.
Sorry about the long reply here but I spent a lot of time figuring out ways to speed up my queries and reports. I even went through the trouble of rebuilding all my indexes (I used SQL Server). Indexing, restructuring the way data was stored, and cutting out time-consuming calculations was ultimately the best way I found to speed up any reports (regardless if they were Crystal or Access) but there are also so many other contributing factors such as network speed connection, memory, etc.. which all play into the equation of which I'm guessing you've already looked into. Seriously though, consider cursors as one of your last resorts if even considering it at all (just from my perspective.)
Last edited by pkstormy; 03-04-08 at 13:44.
Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)