Report with Chart Slow on Browsing Pages

[lopiner]

Hi to all,
I have a report that is based on some temporary created tables.
The report has a subform and a chart along with some textboxes. My problem is that the report is incredible slow on clicking the next page arrow (showing the message "Formatting Page, press Ctrl+Break to stop...") or printing. I have read many posts and advices but with no success. I did at one time a "Compact and Repair" without deleting the Temp tables, opened the report by chance and it was much much faster, no time to load at all. I just cant understand why that happened.
Any help is much appreciated. Thanks in advance.

 

[PNGBill]

Did you note the size of your db before and after compact repair?
I know temp tables can blow up the size of your db - don't know why but they do.

If I can, where the temp tables are done in a macro, I add a line to delete the table when it is no longer of use.
This can cause problems if you want to edit a query that needs the temp table it gets all bitter and twisted but the db stays a normal size.

To compact repair is not practical if the db is shared and or the task takes 10 mins.

 

[lopiner]

PNGBill
Thank you for your reply.
In fact after my code runs i get a 35mb database. If i compact and repair, without deleting anything (temporary created tables etc) it goes to 3mb. I cannot understand what is cluttering the database.

 

[lopiner]

I have found the problem, and it was a simple one, i wasn't closing a recordset and it slowed things down a lot. Thank you for your help.

 

[DCrake]

So not closing down the recordset was the issue. Did you also set the recordset to nothing? The normal syntax is

Dim Rs As DAO.RecordSet
Set Rs = CurrentDb.OpenRecordset("Table")
If Not Rs.EOF And Not Rs.BOF Then
    Do Until Rs.EOF
        Rs.MoveNext
    Loop
    Rs.Close
End If
Set Rs = Nothing

Note you cannot use Rs.Close on a recordset that is either EOF or BOF. That is why the Rs.Close is inside the If Not Statement.

Setting the Rs = Nothing removes the declaration from cache, thus releasing more memory.

 

[lopiner]

Thanks DCrake for your suggestion.
I was setting it to nothing. I have never realized that it wouldn't be possible to close a recordset that was EOF or BOF. Does it stay in memory ? A curious thing is that this particular recordset is on EOF when i try to close it and it solved my problem. My piece of code is like this one:

Dim DataSet as DAO.Recordset
 
Do While Not DataSet.EOF
 
Loop
DataSet.Close
Set DataSet = Nothing

Closing it solved my problem, I'm confused.

 

[lopiner]

Can anyone please shed some light on this issue?
Would the following be sufficient to close the recordset?

Dim DataSet as DAO.Recordset
 
Do While Not DataSet.EOF
 
Loop
DataSet.MovePrevious
DataSet.Close
Set DataSet = Nothing