It takes SO LONG TIME to open a query in design mode

[Helge V. Larsen]

Why does it take SO LONG TIME to open a query in design mode?
I can understand that it takes a long time to run a query that returns
millions of records.
But why won't it quickly open in design mode?
(MS Access 2003 on Windows XP)

 

[John Spencer]

What type of query? All queries or just some queries or just one specific query?

Where is the data physically located? On your computer? On a network server?

If you are talking about a query that is using a crosstab query as a source
then Access has to figure out what fields are generated by the crosstab before
it can open the query using the crosstab. One thing you can do in this case
is to define the columns that are returned.

In the crosstab query you can specify the field name(s) using an In clause in
the PIVOT statement.

TRANSFORM ...
SELECT ...
FROM ...
WHERE ...
GROUP BY ...
PIVOT MonthFieldNames In ("In Progress","On Time", "Late","Very Late")

In the query grid, you do this:
-- Select View properties
-- Click on the grey area above the grid, so you are looking at the query's
properties
-- Input your column heading values in Column Headings separated by commas
(or semicolons if your separator is semi-colons)

When you do this the specified cross-tab columns will show up and ONLY those
crosstab columns will be visible. If you mistype a value, you will get a
column with that name and no data (all nulls) in that column.

One other possibility is that you have Name Autocorrect options turned on.
This feature can cause performance problems.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Richard Horne]

Have you seprated your front and back ends?

If so, keep one of your tables open at all times to maintain a persistent
connection to the back end tables.

 

[David W. Fenton]

Why does it take SO LONG TIME to open a query in design mode?
I can understand that it takes a long time to run a query that
returns millions of records.
But why won't it quickly open in design mode?

Is the problem query based on a complex source query? If so, the
source query has to be run before the design view can display its
structure.