4

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?

0

6 Answers 6

4

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.

1
  • For what it’s worth SQL Server has: (a) PIVOT for their equivalent of CROSSTAB queries, (b) both STDEV and VARP and (c) table valued functions which accept parameters. SQL is not meant to be interactive, so you wouldn’t expect a popup to appear, but you can virtually pass parameters to a query in the form of a table valued function.
    – Manngo
    Dec 5, 2019 at 0:41
1

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

0

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.

2
  • 1
    Please define "SQL compatible" Sep 3, 2015 at 2:17
  • SQL enhancements to make Jet conform more closely to ANSI SQL-92 Sep 3, 2015 at 2:37
0

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ᵀᴹ).

2
  • 1
    SELECT DISTINCT was always available. DISTINCT in aggregates, eg COUNT(DISTINCT column1) is not. May 7, 2021 at 19:51
  • Ah apologies, let me revise the answer above so that newbies like myself do not confuse the two. Many thanks.
    – fletchelg
    May 8, 2021 at 20:06
0

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.

-1

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.

2
  • OK, nice info. But what does it have to do with the question? May 8, 2021 at 22:54
  • "Is it the same as SQL Server?" That's a direct quote from the question, this is my direct answer. I"ll edit to make that clearer.
    – wistlo
    May 10, 2021 at 22:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.