Comparison of Microsoft Access SQL and ANSI SQL

Microsoft Access database engine SQL (Structured Query Language (SQL): A database query and programming language widely used for accessing, querying, updating, and managing data in relational database systems.) is generally ANSI (ANSI SQL query mode: One of two types of SQL syntax: ANSI-89 SQL (also called Microsoft Jet SQL and ANSI SQL), which is the traditional Jet SQL syntax; and ANSI-92 SQL, which has new and different reserved words, syntax rules, and wildcard characters.) -89 Level 1 compliant. However, certain ANSI SQL features are not implemented in Microsoft Access SQL. Also, Microsoft Access SQL includes reserved words and features not supported in ANSI SQL.

Major Differences

• Microsoft Access SQL and ANSI SQL each have different reserved words and data types. Using the Microsoft OLE DB Provider, there are additional reserved words.
• Different rules apply to the Between...And construct, which has the following syntax:

expr1 [NOT] Between value1 And value2

In Microsoft Access SQL, value1 can be greater than value2; in ANSI SQL, value1 must be equal to or less than value2.

• Microsoft Access SQL supports both ANSI SQL wildcard characters and Microsoft Access-specific wildcard characters (wildcard characters: Characters used in queries and expressions to include all records, file names, or other items that begin with specific characters or that match a certain pattern.) to use with the Like operator. The use of the ANSI and Microsoft Access wildcard characters is mutually exclusive. You must use one set or the other and cannot mix them. The ANSI SQL wildcards are only available when using the Microsoft Access database engine and the Microsoft Office Access 2007 OLE DB Provider. If you try to use the ANSI SQL wildcards through Office Access 2007 or DAO, then they will be interpreted as literals.

• Microsoft Access SQL is generally less restrictive. For example, it permits grouping and ordering on expressions.
• Microsoft Access SQL supports more powerful expressions.

Enhanced Features of Microsoft Access SQL

Microsoft Access SQL provides the following enhanced features:

The TRANSFORM statement, which provides support for crosstab queries (crosstab query: A query that calculates a sum, average, count, or other type of total on records, and then groups the result by two types of information: one down the left side of the datasheet and the other across the top.).

• Additional SQL Aggregate Functions , such as StDev and VarP.

The PARAMETERS declaration for defining parameter queries (parameter query: A query in which a user interactively specifies one or more criteria values. A parameter query is not a separate kind of query; rather, it extends the flexibility of a query.).

ANSI SQL Features Not Supported in Microsoft Access SQL

Microsoft Access SQL does not support the following ANSI SQL features:

• DISTINCT aggregate function references. For example, Microsoft Access SQL does not allow SUM(DISTINCT columnname).
• The LIMIT TO nn ROWS clause used to limit the number of rows returned by a query. You can use only the WHERE clause (WHERE clause: The part of an SQL statement that specifies which records to retrieve.) to limit the scope of a query.