Unsupported Operators and Functions

When you construct a query to send to the server, Microsoft Jet determines which parts of the query can be handled by the server and which parts must be handled locally. Microsoft Jet uses the ODBC call SQLGetInfo to ask the ODBC driver which operators and functions are available on the server. When Microsoft Jet encounters a statement containing an operator or function that cannot be processed by the server, that statement must be evaluated locally.

The following tables list operators and functions that Microsoft Jet sends to the server in SQL statements if they are supported by your server. See your server documentation for further details.

Most servers don’t support string expressions surrounded by double quotation marks (") in SQL statements such as:

SELECT * FROM Authors WHERE State = "CA"

When using a Microsoft Jet workspace, Microsoft Jet translates double quotation marks into single quotation marks (') before sending the statement to the server. However, when using an ODBCDirect workspace, no translation is performed and an error will occur. To write code that is easily portable between either kind of workspace, make sure to use single quotation marks around strings in SQL statements as shown in the following example:

SELECT * FROM Authors WHERE State = 'CA'

In addition, some servers don’t support the following features:

• Outer joins
  
  
• Expressions, or nonfield references, in the ORDER BY clause
  
  
• The Like operator when applied to Text, Memo, or binary fields
  
  
• Unfamiliar wildcards in Like expressions
  
  
• Expressions that use a mixture of incompatible operators and functions, such as:

  A LIKE B * C

The following features are unsupported on all known ODBC-accessible servers.

• Aggregation of reports constructed in Microsoft Access that contain multiple levels of grouping and totals
  
  
• User-defined functions that use variable arguments
  
  
• Microsoft Jet-specific SQL extensions, such as TOP or TRANSFORM