Monday, March 27, 2006

Microsoft Access SQL: Ancient mystery solved

Don't get me started on Microsoft Access. It's hard to avoid colorful language and impolitic metaphor. Suffice to say few tools combine such power and such misery in a single package.

One abiding mystery with Access is the documentation for its flavor of SQL (not to mention the occult and bizarre functions, abandoned bastard children of VisualBasic, one can embed into queries). I've often searched on "Microsoft Access SQL" and found nothing [2]. Recently, reading the very good Wikipedia article on Access I came across a clue. Since the default database engine is "Jet", maybe a search on "Jet SQL" would work better.

It does: Microsoft Office Assistance: Microsoft Jet SQL Reference

[1] For example: Create View.
CREATE VIEW can be executed only through the ADO library.
Access itself uses DAO, so any attempt to CREATE VIEW from the query design
window will fail.
If you try to create a view within Access itself, you get a very helpful error message: "Syntax error in create table statement". Sigh. I do love my Mac.

[2] It's mostly in the help file but Microsoft's brilliantly helpful implementation obscures this. BTW, I think Vista and Office 2007 will be, both, catastrophes.


grovelli said...

Hi Gordon, you might love your Mac as much as you want but you use Access because there are no comparable products in the Apple world. ;-)

JGF said...

Yes, there really is nothing like Access anywhere. It's kind of a dinosaur of a prior age, but I appreciate it.

Fortunately it runs very well in Fusion or Parelells on OS X/Wind2K