ACC1x: Queries Sent to Remote SQL Server Run Slowly

ID: Q109815


The information in this article applies to:


SYMPTOMS

A query sent to a remote SQL Server from Microsoft Access seems to run very slowly compared to other methods of querying SQL data.


CAUSE

This behavior can be due to either of the following causes:


RESOLUTION

Make sure to explicitly define all the query parameters before running the query, so that Microsoft Access can send the entire query to the server.

To explicitly define parameters, choose Parameters from the Query menu, and then enter the names and data types of all the parameters used in the query. The "Employee Sales by Country (Parameter)" query in the sample database NWIND.MDB demonstrates this method.

Also make sure to use only functions and commands known to SQL in your query criteria or, if possible, provide additional query restrictions to send to the server. This limits the amount of data returned to Microsoft Access for local user-defined function evaluation. For example, the following query will return all records that are not null:


   Like MyFunction(Forms]![Formname]![Fieldname],*) 

Since the sample query above contains MyFunction(), a function unknown to SQL, the query has to be performed locally. Instead, you can get the same results by using the following query, which uses only functions known to SQL:

   Like [Fieldname] or [Fieldname] Is Null 

Note that "Forms!Formname!Fieldname" is effectively a query parameter, and should be explicitly declared. It can be declared as:

   Name=Forms!Formname!Fieldname, Type=<yourtype> 


STATUS

This behavior no longer occurs in Microsoft Access version 2.0.


REFERENCES

Microsoft Access "User's Guide," pages 182-185

Additional query words: Queries


Keywords          : kbusage OdbcOthr 
Version           : 1.0 1.1
Platform          : WINDOWS 
Issue type        : kbprb 

Last Reviewed: April 2, 1999