Microsoft Access Jan 8, 2002

Today's flamebait-of-the-day topic is the current industry standard in desktop databases: Microsoft Screws Up Again.. er, [ahem], I mean, Microsoft Access.

<flamebait> I recently had the opportunity to work with Microsoft Access for the first time in a couple of years.

Actually, let's be honest here. I recently had the obligation to work with Microsoft Access for the first time in a couple of years. I couldn't remember the reasons why I'd stopped using it when I began today's foray into relational database land, but believe me, I remember them full well now.

First try occurred actually a few days ago, when I first attempted to open the database file in question using the computer I'm typing this on now, which is a Dell Dimension triple-booting Windows 98, Windows 2000, and WinLinux 2001. On this computer, Access had to be used from 98, because that's the only place it was installed. Problem being that Windows 98 is an unstable piece of crap. Especially on this machine. And Access ran very unimpressively. As a matter of fact, it steadfastly refused to do any modifications to the database at all. In addition, it complained about missing components, which I was unable to rectify because the Office CD-ROM was scratched beyond repair.

Now, OK, missing components are a fine reason to not modify a database, but the thing is, I definitely have used Access on this computer before and was able to work with it just fine. Anyway, I tried it again on my dad's old Toshiba laptop, which opened the file but refused to open any tables in the file. Great. It also, incidentally, complained about missing components. This doesn't seem to spontaneously happen to any other programs I know of.

So I tried it on my dad's shiny new Dell Latitude laptop. This time, it was able to open and modify the file just fine. So, to business. My job with this particular database was to clean it up a bit, combine a few fields here and there, and eliminate some duplicate entries. This should not have taken a long time.

However, Microsoft's implementation of SQL, which goes by the ever-so-snazzy name "Jet SQL" (as opposed to what? Propeller SQL?), complies with almost no standards I am aware of. And their documentation is in some places sparse, in some places confusing, and in some places wildly inaccurate. For instance, the functions mentioned in "ODBC Scalar Functions" would have helped me out a lot when dealing with string lengths and so forth. But when I actually tried to use said functions, which I did EXACTLY the way their examples specified, it seemed to think I was typing in a GUID, not a function reference. What the fuck?

For more convincing, less anecdotal evidence that Access does not deserve to be the "industry standard," check out this chart. This is MySQL's "crash-me" benchmark, which does automatic comparisons of different SQL database systems based on ANSI compliance, functionality, performance, and security. I've set it up to compare two free database servers (MySQL 3.23 and PostgreSQL 7.1.1) along with two commercial applications (Access 2000 and Oracle 8.1.6). Access is the second column from the left.

A few things to notice:

  1. Access 2000's feature list is pretty sparse, compared to the others. Between Access and Oracle, this makes some sense, considering Oracle's price tag. But a product that costs upwards of $300 should be able to compete on a feature level with products that are free.
  2. More surprising, though, is that MySQL (free) is more feature-complete in the area of ODBC than Access is. ODBC is a standard invented by Microsoft, for crying out loud... and their main database product doesn't support it as completely as a free product. I am aware, however, that Microsoft SQL Server (which costs several THOUSAND dollars) is much better in this area.
  3. Just because MySQL is doing this test doesn't necessarily mean that the test will be biased in favor of MySQL. Note that PostgreSQL is clearly a better product, based on the results of this test, than MySQL is.
  4. Perhaps the most convincing evidence: Notice the little red-triangle-with-exclamation-point icon under Access 2000. This means that anyone with normal access (not necessarily administrator access) can bring down an Access 2000 server, possibly for good.
  5. The free products seem to outperform the commercial products almost across the board in these tests.

So what does this say about the state of the art in the database industry, if free software can outperform commercial software in most areas? Consider that this statement also pretty much applies to the operating systems industry. What does THIS mean? Discuss. </flamebait>