Where can I find the documentation for the particular kind of SQL used by the Jet 4.0 engine?

Question

Which I think is the same as the one used by MS Access, which I think is not the same as the one used by MySQL and others. I need the documentation for this particular "kind" or "subset" of SQL language.

Answer 1

Here is the most recent version of the Access SQL Reference (Access 2010).

However, the guides as per @Remou's answer are the most useful resource Microsoft makes available, noting they are over a decade old and do not take account of changes made to Access 2007 ACE era (i.e. multi-valued data types -- shudder!)

---

A word to the wise: the Access SQL Reference contains errors of inclusion and omission too numerous to detail here, so here's a brief example:

CREATE TABLE Statement (Microsoft Access SQL)

CREATE TEMPORARY TABLE syntax has never been supported by Access and Access doesn't even have a temporary tables feature. It looks like someone writing the Help lifted this from the SQL-92 spec thinking that Access was SQL-92 compliant!

WITH COMPRESSION syntax is only supported in Access's ANSI-92 Query Mode**. It would be helpful if this was pointed out.

Quote: "You can use NOT NULL.. within a named CONSTRAINT clause that applies to... a multiple-field named CONSTRAINT" -- again, this is not a feature of Access and attempting to use the syntax generates an error.

DEFAULT: it is very useful to be able to specify a DEFAULT value for a column and Access does indeed suport this syntax yet it is missing from this page of the help.

IDENTITY: this keyword is missing from the Access SQL Reference and this page is the logical place to find it.

CHECK constraint: potentially one of the most powerful features of Access SQL but almost completely absent from all Microsoft documentation, merely gets a brief mention in a couple of articles.

As I said, this is just one page! And yes I have given feedback to Microsoft and the Access Team about these errors (each time they release a new version, in fact) but the errors remain :(

---

This referes to Access's proprietary ANSI-92 Query Mode, not to be confused with the SQL-92 Standard. Access is not, and sadly never will be, SQL-92 compliant, not even entry level SQL-92. It seems the Access team have removed their ANSI-92 Query Mode page from the Help, possibly because that too contained errors of inclusion e.g. LIMIT TO nn ROWS in the ORDER BY is not supported by either Access or its SQL syntax.

Answer 2

Microsoft Jet SQL Reference