OFF97: Data Is Returned Very Slowly from External Query (172731)
The information in this article applies to:
- Microsoft Office 97 for Windows
This article was previously published under Q172731 SYMPTOMS
When you return data to Microsoft Excel 97, the process may take twice as
long as it does in an earlier version of Microsoft Excel. For example, when
you run a query in Excel version 7.0, it takes 30 seconds. When you run the
same query in Excel 97, it takes 3 minutes.
CAUSE
This problem occurs when you return data to the worksheet and you do the
following:
- You create a query to return data from a large database (for example, a
database with over 1000 records).
-and-
- In the External Data Range Properties dialog box, you click both Enable
Background Refresh and Save Query Definition (to perform asynchronous
queries).
-and-
- After you run the query, you don't perform any tasks in Excel.
-or-
- Enable Background Refresh and Save Query Definition are not selected in
the External Data Range Properties dialog box.
RESOLUTION
To resolve this problem, do either of the following:
- Use asynchronous queries and leave Microsoft Excel active in the
foreground.
-or-
- Use asynchronous queries and position the pointer over the Microsoft
Excel toolbar if you are not working with Excel in the foreground.
STATUS
Microsoft has confirmed this to be a problem in Microsoft Excel 97 for
Windows. This problem was corrected in Microsoft Excel 97 for Windows,
Service Release 1 (SR-1).
For additional information about SR-1, please see the following article in
the Microsoft Knowledge Base:
172475
OFF97: How to Obtain and Install MS Office 97 SR-1
MORE INFORMATION
Asynchronous queries are executed more quickly than synchronous queries.
You can make asynchronous queries available when you click Enable
Background Refresh in the External Data Range Properties dialog box. When
you use asynchronous queries, Excel stops fetching data if you do not
perform any tasks in Excel.
When you use synchronous queries (Enable Background Refresh option is not
selected), the cells are formatted when data is returned. The data is
returned quickly; however, formatting the cells can take up to two or three
times longer than fetching the data.
REFERENCES
For more information about running queries in the background, click the
Index tab in Help, type the following text
and then double-click the selected text to go to the "Run a query in the
background or while you wait" topic.
| Modification Type: | Major | Last Reviewed: | 11/22/2000 |
|---|
| Keywords: | kbprb KB172731 |
|---|
|