OFF97: Data Is Returned Very Slowly from External Query

• 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:

   ARTICLE-ID: Q172475
   TITLE     : 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