minor changes in Report Design View take a long time

[Paul]

I've noticed that when I try to make minor changes to some reports in Design
View, every step of the process seems to take an inordinately long time.
For example, I have a report that only takes about 3 seconds to open in
Print Preview. But when I open that same report in Design View, every minor
change takes 15 to 20 seconds to register before Access returns control of
the cursor to me. Even if I try to do something as minor as clicking and
dragging a control to reposition it in the Detail section of the report, it
can take up to 20 seconds before Access responds to my next click.

This is a simple report, with no subreports. The underlying query involves
several tables and sub queries, some of which are total queries, but I've
done everything possible to max out the performance of this database, such
as indexing all the table fields that are used as criteria in tables, turned
off Name AutoCorrect and set the table SubDatasheets to None. It only takes
the Record Source query about 3 seconds to display all records in datasheet
view.

Can anyone tell me what's going on that causes this report that opens quicky
in Print Preview to slow down to a crawl in design view, and what, it
anything, I can do to speed it up?

Thanks in advance,

Paul

 

[Allen Browne]

If this is Access 2007, see if these hotfixes help:
http://support.microsoft.com/kb/960307
http://support.microsoft.com/kb/957690
I'm not sure, but the hotfixes should be included in SP2:
http://support.microsoft.com/?kbid=953195

In any version of Access, a report based on a crosstab query can be slow,
since the query needs to run to completion before the field names are known.
You can address this by setting the Column Headings:
http://allenbrowne.com/ser-67.html#ColHead

If there are other users, make sure they are not in the same database file
you are trying to edit.

If the database is over a network, see if copying to C: makes any
difference.

 

[Paul]

Your suggestions solved my problem, Allen.

I was running my development copy of the front end on the network, and after
I copied it to my C drive, the problem went away. Putting it on my local
drive as you suggested sped things up by an order of magnitude, that is,
there is no noticeable delay in editing the report in Design view.

I'm using Access 2003, and there are no crosstab queries in the report, so
the performance hit during development was caused by doing it over the
network.

Thanks for taking the time to help me out.

G'day, my man.

Paul