What flavor of SQL is used inside Microsoft Access?

For full details read the docs: Microsoft Access SQL reference

MS Access definitely is not the same as SQL Server. The overall syntax is similar as any flavor of SQL, but it doesn't have a lot of features that were added since SQL Server 2005.

It doesn't even have some pretty basic features, like:

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

On the other hand, it has enhanced features, like:

  • The TRANSFORM statement, which provides support for crosstab queries.
  • Additional aggregate functions, such as StDev and VarP.
  • The PARAMETERS declaration for defining parameter queries.

Window functions that were added since SQL Server 2005+, such as ROW_NUMBER, OVER clause don't exist as well.

Set of built-in functions for string or date/time manipulation are similar, but they are not the same.

Tags:

Ms Access