How to Find The Documentation for The Particular Kind of SQL Used by The Jet 4.0 Engine

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

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.

What is the proper way to refer to the SQL dialect used by Microsoft Access?

Microsoft Corporation refers to it as "Microsoft Access SQL" in an Access 2013 help article:

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

-- Comparison of Microsoft Access SQL and ANSI SQL (http://msdn.microsoft.com/en-us/library/office/ff844937(v=office.15).aspx)

Microsoft Jet OLEDB 4.0 SQL Function Reference with ADO?

For question 1, this SO answer lists some good resources, including a link to an Intermediate SQL for Jet 4.0 article.

For question 2, do a search for the Iif and Switch functions.

How to determine Jet database Engine Type programmatically

You'll have to set a reference to ADO and then you can get the property.

From inside of Access

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection

From outside of Access

Dim cnn As New ADODB.Connection
cnn.Open Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Contact.mdb

And finally

Debug.Print cnn.Properties("Jet OLEDB:Engine Type").Value

This .Value will return 1 to 5. If it is 5, it is already in Jet4x, otherwise it is an earlier version.

Here's another example of the upgrade technique you're looking at as well: Convert MDB database to another format (JET, access version)

Where can I find a complete reference for Microsoft Access SQL?

Open MS Access, go to a module and open it, bringing up the Code Editor.
In the code editor, choose Help > Microsoft Visual Basic Help.
Now choose 'Microsoft Jet SQL Reference'.

I will soon be releasing a library that includes a JET SQL parser.
It enhances JET SQL to provide all of the DDL functions not currently available in SQL, and offers automated back end update using a version number and version update scripts.

EDIT: Of course, I forgot that they've probably screwed all this up in later versions of Office.

I checked and the above works in Access 2003 (the last usable version of Access, which I still use for development) and Access 2007. Noting you MUST be in the code window, not the main Access database window!

In Access 2013, this has all moved to the web. However it looks like there is still a 'Developer Reference' link on the web page, and this takes you to a page substantially like the offline one. After clicking some links I'm ending up at: msdn.microsoft.com/en-us/library/dn142571.aspx

Hopefully since this is Access 2013, the link won't die too soon.

Microsoft JET SQL Query Logging or How do I debug my customer's program?

To get your grubby hands on exactly what Access is doing query-wise behind the scenes there's an undocumented feature called JETSHOWPLAN - when switched on in the registry it creates a showplan.out text file. The details are in
this TechRepublic article alternate, summarized here:

The ShowPlan option was added to Jet 3.0, and produces a text file
that contains the query's plan. (ShowPlan doesn't support subqueries.)
You must enable it by adding a Debug key to the registry like so:

\\HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\JET\4.0\Engines\Debug

Under the new Debug key, add a string data type named JETSHOWPLAN
(you must use all uppercase letters). Then, add the key value ON to
enable the feature. If Access has been running in the background, you
must close it and relaunch it for the function to work.

When ShowPlan is enabled, Jet creates a text file named SHOWPLAN.OUT
(which might end up in your My Documents folder or the current
default folder, depending on the version of Jet you're using) every
time Jet compiles a query. You can then view this text file for clues
to how Jet is running your queries.

We recommend that you disable this feature by changing the key's value
to OFF unless you're specifically using it. Jet appends the plan to
an existing file and eventually, the process actually slows things
down. Turn on the feature only when you need to review a specific
query plan. Open the database, run the query, and then disable the
feature.

For tracking down nightmare problems it's unbeatable - it's the sort of thing you get on your big expensive industrial databases - this feature is cool - it's lovely and fluffy - it's my friend… ;-)

The Microsoft Jet database engine could not find the object 'Sheet1$'

I've found the problem.

It seems the spreadsheet was being saved to the wrong location, so filepath wasn't pointed to a file which exists.

I didn't check this at first because I assumed a different error message would appear. Something like "Book1.xls could not be found". However it seems like if it doesn't exist, then the message will just state that it cannot find the Worksheet.

---

Related Topics