Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

ACC: Running Stored Access Queries with Wildcards via ODBC May Return Incorrect Results


View products that this article applies to.

This article was previously published under Q234525
Moderate: Requires basic macro, coding, and interoperability skills.

↑ Back to the top


Symptoms

In a Microsoft Access Jet 3.5x or earlier database, you have a stored query that uses a valid Jet wildcard character, but when you run it, no records are returned, even though you are certain there are matching records.

↑ Back to the top


Cause

You have the Microsoft Access ODBC driver version 4.0 or later and Microsoft Jet OLE DB Provider version 4.0 or later installed.

↑ Back to the top


Status

This is the designed behavior when you are using the Microsoft Access ODBC driver version 4.0 or later and Microsoft Jet OLE DB Provider version 4.0 or later and Jet 4.x.

The Microsoft Jet database engine is generally ANSI-89 Level 1 compliant. However, certain ANSI SQL features are not implemented in Microsoft Jet SQL. With the release of Microsoft Jet version 4.x, the Microsoft Access ODBC driver, and Microsoft OLE DB Provider for Jet 4.x, Microsoft Jet SQL exposes more ANSI-92 SQL syntax. Conversely, Microsoft Jet SQL includes reserved words and features not supported in ANSI SQL.

Microsoft Jet SQL supports both ANSI SQL wildcard characters and Microsoft Jet-specific wildcard characters to be used with the LIKE operator. The use of the ANSI and Microsoft Jet wildcard characters is mutually exclusive. You must use one set or the other, and you cannot mix them. The ANSI SQL wildcards are only available when you are using Jet 4.x and the Microsoft OLE DB Provider for Jet and the Microsoft Access ODBC Driver. If you try using the ANSI SQL wildcards through Microsoft Access or Data Access Object (DAO), they are interpreted as literals. The opposite is true when you are using the Microsoft OLE DB Provider for Jet or the Microsoft Access ODBC driver and Jet 4.x

↑ Back to the top


More information

Steps to Reproduce Behavior

  1. Install either MDAC 2.1 or a Microsoft Office 2000 suite (to install Jet 4.x).
  2. Create a data source to the sample database Northwind.mdb.
  3. Open MSQuery and create a new query with the data source for Northwind.
  4. In SQL View, type the following line:
    SELECT * FROM Customers WHERE CustomerID LIKE 'a*'
  5. Run the query. Note that no records are returned.
Using the Microsoft Access ODBC driver and Jet Provider that is included with MDAC 2.0, the query would return all of the customer records with a CustomerID that starts with "A".

↑ Back to the top


Keywords: KB234525, kbpending, kbbug

↑ Back to the top

Article Info
Article ID : 234525
Revision : 3
Created on : 1/24/2007
Published on : 1/24/2007
Exists online : False
Views : 333