SQL Support

Significant additions have been made to the Jet SQL parser to improve compatibility with SQL-92 and SQL Server. While Jet does not fully comply with any level of the SQL-92 specification, it now understands many of the more common elements. This includes question-mark parameter markers, GRANT/REVOKE, improved referential constraint support, CHECK clauses, SQL-instantiated transactions, and CREATE VIEW/CREATE PROCEDURE support.

All of these additions are reflected through the OLE DB provider, although they are not necessarily accessible through Microsoft Access or through DAO. Consumers should be aware of the behavioral differences that exist among the various ways to access Microsoft Jet databases.

The OLE DB provider for Jet also changed the wildcards used in SQL LIKE clauses. Wildcards are used to allow more sophisticated pattern matching in queries over text columns. For example, the following query finds all rows in a customer table where the last name of the customer begins with "Smi":

SELECT * FROM CustomerTable WHERE LastName LIKE 'Smi%';

The % character is a wildcard that indicates that 0 or more characters can follow. In Jet 3.5, the equivalent wildcard character was *. The other wildcard character matches a single character of any value. This value changed from ? in Jet 3.5 to an underscore (_) in Jet 4.0. This change matches the SQL-92 and ODBC definitions for these wildcards.

It is important to note, however, that the mechanism for escaping wildcards (so that the literal % and _ can be matched in strings) has not been changed in the 4.0 release. You need to surround the character with brackets ? for example, [%] ? to match these characters explicitly. The following query will look for customers with a last name that is exactly "Smi%":

SELECT * FROM CustomerTable WHERE LastName LIKE 'Smi[%]';