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

It is being misused

[PhrostyMcByte]

If you have that many access databases, you are probably misusing it. Import all that data into SQL Server, and start from there. There is no magic way to make sense of a database schema.. the best you'll do is grabbing a GUI that visualizes it.

Re:Tools

[Maserati]

And there never has been.

Set the wayback machine to Access 1.0 and you'll find me working at a Software Etc. back in college. We got, and sold, 12 copies of Access 1.0. All twelve came back from customers complaining that the program was uselessly buggy.

Wind forward a bit to Access 97. I'm a sysadmin for... well, a company using a lot of Filemaker 3 databases (my first task there was finishing the FMP 2->3 migration). We were looking to migrate off of Filemaker (it wasn't a Microsoft product; just don't ask me about that manager - I have no idea how he got out of there without being charged with embezzling for kickbacks from the consultant he was partnered with immediately prior to joining our company). One candidate to replace Filemaker for production databases with 50 users, 250,000+ records and 2 or 3 people running reports was Access 97 (the other two were CRM products, one from a company that later ran Superbowl ads and the other from a company later acquired by Nortel; and no, management didn't select the company that survived). Our lead FMP developer managed to stretch the Access97 evaluation out to a full hour before he deleted every table in his test implementation without recovery, undo, or prompt. End of evaluation.

So I've seen two versions of Access in business situations. Microsoft usually gets things right in the third version. For Access, you have to start counting with Access 2000 - if that version was useful.

How you handle it is to document the functionality and re-implement the application in something else.