OFF97: Data Is Returned Very Slowly from External Query

ID: Q172731

The information in this article applies to:

Microsoft Office 97 for Windows

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:

Q172475 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

queries, background

and then double-click the selected text to go to the "Run a query in the background or while you wait" topic.

Additional query words: OFF97 msquery


Keywords          : kbtool xlquery 
Version           : WINDOWS:97
Platform          : WINDOWS 
Issue type        : kbprb

Last Reviewed: May 26, 1999