What flavor of SQL is used inside Microsoft Access?

Question

I don't have much Access experience, and I took on a new Access project. I can use SQL to query the Access databases.

What SQL specification/flavor is used inside Access? Is it the same as SQL Server?

Answer 1

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.

Answer 2

The SQL flavor MS Access uses is Jet SQL. MS Sql Server uses T-SQL.

Answer 3

Indeed, mostly SQL compatible with a few known exceptions (see the features)

EDIT: More to the point you can flat out upgrade Access databases into MS-SQL Server, the syntax is virtually identical, but you have to be aware of features that Access won't support.

Answer 4

Just wanted to clarify for the newbies like myself, that the useful answer by Vladimir Baranov above, does not mean that SELECT DISTINCT statements aren't available (Thank you ypercubeᵀᴹ).

Answer 5

For those using much older versions of Access, such as the popular Access 97 (released with Office 97 and still in widespread use using DAO or ODBC). Access 97 uses the SQL-89 language specification. I believe this applies to all versions until Office 2003, since when Access has used the SQL-92 language specification supported by most modern databases such as SQLite3.

Combined with some limitations of Access 97, differences between the SQL-89 and SQL-92 syntax include:

• LIKE use UNIX (not SQL) wildcards, as used in the Windows command prompt. So SELECT * FROM MyTable WHERE Name LIKE '*ab?d*;. Modern SQL would use % and _.
• Access 97 does not support batch SQL execution. So SELECT * FROM Table1; SELECT * FROM Table2; is invalid and will not be executed.
• Access 97 does not support IF EXISTS, as in DROP TABLE IF EXISTS MyTable;.

These limitations will not apply to recent versions of MS Access. Every database vendor has some quirk or proprietary feature that needs to be taken account of.

Answer 6

To answer "Is it the same as SQL Server", no.

SQL Server (T-SQL) can scale up to much larger tasks than the Access (JET SQL).

In terms of flavors, think of Access as limited to tablespoons while SQL Server (and other databases), can accept ladles, pots, vats, or barrels of data. (To extend the analogy, Big Data in the tera- and petabytes gets you into swimming pools, lakes, and oceans, and require non-relational database tools).

MS Access limits documented by Microsoft are here. Some key differences:

	MS Access Jet	SQL Server T-SQL
Max Database Size	2 GB	524 TB
Simultaneous Users	255	32767
Number of Objects	32768	2147483647

If you are dealing with tables holding 100K records or more, just a few queries can bump up against the size and object limits. It's possible to work around with intermediate queries, but these can become more complex and difficult to manage than putting the same logic into a scalable database like MS SQL Server.