11

I am looking for a definite reference of the SQL as understood by Microsoft Access. All the links I can find talk only about bits and pieces. Ideally I am looking for a grammar specification with details of what all the different keywords do.

Motivation: I am trying to write my own parser for Microsoft Access SQL statements.

Charles
  • 50,943
  • 13
  • 104
  • 142
Jakob Egger
  • 11,981
  • 4
  • 38
  • 48
  • Did you find [Fundamental Microsoft Jet SQL for Access 2000](http://msdn.microsoft.com/en-us/library/aa140011%28office.10%29.aspx) -- [Intermediate Microsoft Jet SQL for Access 2000](http://msdn.microsoft.com/en-us/library/aa140015%28office.10%29.aspx) -- [Advanced Microsoft Jet SQL for Access 2000](http://msdn.microsoft.com/en-us/library/aa139977%28office.10%29.aspx) ? – Fionnuala Jun 29 '12 at 19:06

3 Answers3

6

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.

Ben McIntyre
  • 1,972
  • 17
  • 28
  • Thanks, I can't check right now because I'm not at a windows machine. Can you tell me more about that library? What programming language are you writing it in? (Especially the parser) – Jakob Egger Dec 11 '13 at 14:46
  • All native Access, ie. VBA. I call it the Access Extension Framework and I plan on selling it online for $50. It will be basically open source, because locking it or releasing as a DLL would destroy a lot of its usefulness (ie. deploying to locked down machines is always a drama). – Ben McIntyre Dec 13 '13 at 00:48
  • I see this page: http://office.microsoft.com/client/helphome14.aspx?NS=MSACCESS.DEV&VERSION=14&LCID=1033&SYSLCID=2070&UILCID=1033&AD=1&tl=2 and it doesn't have a 'Microsoft Jet SQL Reference' or similar link anywhere. :( '( – sergiol Dec 20 '13 at 10:44
  • Just read Jakob's comment about not mentioning aliasing. Good point J, I never noticed that before. I think the above are the most definitive docco available, after that you're on your own. However, there are probably other people (me, for example) interested in augmenting the M$ docs to include all the possible permutations. Also, Allen Browne's site is not to be underestimated - I have often found things there that no amount of googling could find in the 'official' docs. – Ben McIntyre Dec 22 '13 at 01:22
  • BTW Jakob, I do have a suite of general purpose parsing libraries in C# that I'd be happy to share. What language(s) are you interested in? – Ben McIntyre Jan 03 '14 at 06:07
  • This looks great, just what I was looking for. I wanted to write an extension to my app MDB Viewer that could execute Jet SQL queries on the Mac. However, I've abandoned the project because I believe that demand for such a tool is too limited for the incredible effort required. – Jakob Egger Jun 30 '15 at 08:04
  • FWIW, the library I referred to is the Access Extension Framework, at arrow-of-time.com – Ben McIntyre Nov 09 '15 at 04:18
5

I just did a quick Google search and found this:

To me this looks like what you wanted, or are these some of the "only bits and pieces" links that you already found?

Christian Specht
  • 35,843
  • 15
  • 128
  • 182
  • Yes, these are the "bits and pieces" I meant. The problem is that they are not precise enough. For example, http://office.microsoft.com/en-us/access-help/from-clause-HA001231451.aspx makes no mention of the fact that you can alias table names in from clauses, so I need to look at lots of examples and work by trial and error to find out what is allowed and what not. – Jakob Egger Jun 29 '12 at 18:06
  • Actually, the fact that you can use aliases **is** mentioned ("The name of the field or fields to be retrieved **along with any field-name aliases**"), but I see what you mean: there is no example for it, so you have to figure out the syntax by yourself. – Christian Specht Jun 29 '12 at 18:37
  • 1
    Both links seem to be outdated. – Reto Höhener Jun 06 '13 at 06:31
3

Looks like I'm late to the game on this: but I'm looking for something similar, and it's a fair guess that you and I are not the only people who will ever look for this information.

To the best of my ability to search, there is NO definitive language reference document for Jet-SQL on any of Microsoft's websites.

There is a list of Jet-SQL keywords here:

http://support.microsoft.com/kb/248738

Predictably this has no hyperlinks to descriptions and specifications of the keywords' underlying functionality.

The closest I've got is a Google result for someone else's search:

http://office.microsoft.com/en-us/support/results.aspx?ctags=CH010072899

However, this is not the answer you wanted: it's just another 'bits and pieces' selection - not a definitive language reference. It may help you (or rather, later searchers, like myself) by filling in the gaps, so it's not entirely useless.

It is possible that your efforts to answer your own question have come up with the missing data in a convenient format. If so, you would perform a valuable service to the developer community by posting a follow-up answer with a link.

It is entirely possible that 'Stop searching, it isn't there' is the most useful answer available today: disappointing, but helpful if others heed the warning and waste less of their time.

I would be delighted if a fellow Stack Overflow memnber were to contradict my assertion that Microsoft do not publish a definitive language specification for Jet SQL. However, I doubt that this is likely: MSDN is a collection of dead-ends and 'shallow graves' - documents that lead nowhere to related information, and information 'documented' by gravediggers who have hidden uncounted bodies of data so they will only ever see the light of day if someone knows exactly where to dig.

Nigel Heffernan
  • 4,636
  • 37
  • 41
  • Thank you for posting. Unfortunately, I don't have anything to add at this point. I have been too busy with other projects to concentrate on my Access endeavors. – Jakob Egger Mar 05 '13 at 10:15