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

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.

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

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

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".