Edit

Comparison of Microsoft Access SQL and ANSI SQL

  • Article

Applies to: Access 2013, Office 2013

Microsoft Access database engine SQL is generally ANSI-89 Level 1 compliant. However, certain ANSI SQL features are not implemented in Microsoft Access SQL. Conversely, 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. For more information, see Microsoft Access Database Engine SQL Reserved Words and Equivalent ANSI SQL Data Types. Using the Microsoft Access Database Engine OLE DB Provider there are additional reserved words.

  • Between…And

    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 wildcard characters that are specific to the Microsoft Access database engine to use with the Like operator. The use of the ANSI and Microsoft Access database engine 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 Access Database Engine OLE DB Provider. If you try to use the ANSI SQL wildcards through Microsoft Access or DAO, then they will be interpreted as literals. The opposite is true when using the Microsoft Access Database Engine OLE DB Provider.

    Matching character

    Microsoft Access SQL

    ANSI SQL

    Any single character

    ?

    _ (underscore)

    Zero or more characters

    *

    %

  • 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:

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 to limit the scope of a query.