Printing reports

[Ashley Sutton]

The following code fragment is used to open a report based on one record from a recordset of around 2500 entries.

DoCmd.OpenReport "AVPS_Quote-DD_version", acPreview, , "[Quote Number]=Forms![AVPS Quotes issued]![Quote Number]"

It now takes around 40 seconds for the report to open and with the subsequent processing of various controls on the report, printed output can take several minutes to appear.

Previewing the report from the database window so that all 2500 records are included in the report also takes around 40 seconds to appear. This suggests that the above code is also including all records although only the requested one is actually produced.

Any suggestions on how to get around this problem?

 

[Ashley Sutton]

Thank you for your suggestions. It set me testing and thinking again. You were correct in that the back-end resides on a server. However performance was no better when transferred to the local machine and also when most of the records were deleted.

The problem turned out to be an OLE object that generates the page heading for the report. Although all the OLE properties looked correct I deleted the object and reinserted it. Bingo! The report now opens in approx 1 second.

 

[BarkerD]

I have also been experiencing performance problems with my database. Pat's comment about the whole database being returned from the WHERE clause has got me thinking.

I am using a Front-End/Back-End configuration.

I have quite a few functions in my application which return a recordset, perform calculations and then close the recordset object. Every time I request a recordset, is Access actually retreiving every record, and then performing the SQL where clause in my OpenRecordset method?

If this is true should I also clean up my SQL a little and stop using SELECT * ?

Should I create a whole bunch of queries on my Back-End and use them as the recordsource for my recordset objects? (Seems this may eliminate the needless traffic across the network)

[This message has been edited by BarkerD (edited 02-01-2000).]