How Do I Make Sense of Microsoft Access?

Anthony Boyd asks: "I have a pretty good tool-set for LAMP work, but as I get into Microsoft jobs, I've started to wonder if I'm working with the best tools. In particular, I'm exploring an 'out of control' Microsoft Access setup, which has about 200 tables in 30 .mdb files, including some duplicated/outdated tables. I'd like to print the properties of each table (with the comments for each field), print the table list for each database, get info on the field types & relationships, and so forth. What tools do you suggest for trying to grok a large Access mess?"

Comments

Visio

[hbo]

If someone else is paying for your tools. Visio will analyze any database that has an ODBC driver. (That includes MySQL and PostgreSQL.) Of course, then you end up struggling with two bloated Microsoft tools. But Visio at least can be used to draw pretty pictures. 8)

Access is frequently abused in the way you describe. Companies that have Office licenses often restrict distribution of the Access component, even if they are otherwise entitled to it, because of such abuse. Access is a very handy tool for a quick-and-dirty database design, so people use it for that - a lot. Pretty soon, you have little information islands all over the place, designed by amatuer DBAs, and containing gobs of misplaced but critical business data. I believe it is all another Nefarious Microsoft Plot (NGP) because when you switch to the solution for cleaning it all up - SQL server - your need for the software is so severe that you won't kick about the price, and expectations for performance are so low that SQL server easily passes muster. Of course, that's just the snide opinion of Yet Another Microsoft Detractor. 8)