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?"

IMHO

[ManoSinistra]

I have also done extensive work with LAMP and Microsoft (Access in particular). I actually started out with ASP and Windows before I learned all the LAMP stuff. IMHO, Access databases are by no means secure and what's more, they're very clumsy animals. It might be in your clients best interests to convert to LAMP/Linux, etc.

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)

ADO OpenSchema?

[dtfinch]

I've used ADO's OpenSchema method in the past to get raw lists of table schemas and relationships, including field descriptions.

Access != Good && Access Enterprice Apps

[jrmiller84]

The company I work for runs all of it's POS systems off of MS Access and it's given us nothing but problems. I would suggest switching to anything but Access. If you knew the number of corrupt databases and table property dropping we deal with over a months period (and that's only 72 stores worth, or 72 instances of Access) you'd think twice or think about switching immediately. We are constantly compacting and repairing the databases and it just generates a lot of extra work and takes up time that would better be spent fixing the POS code itself. Steer clear of Access if you want to keep large amounts of data (as it seems you do with that many mdb files and tables). People have argued with me over this, but Access just isn't meant for large scale enterprise applications. If you were a small business I could see it being appropriate but nothing above that.

In one of my courses...

[Secret Rabbit]

... I was forced to learn access.

The course was taught by "the new guy" who was a *NIX person (of course he got to teach the M$ classes and not the *NIX classes which would have made sense).

Anyway, one class he was showing us how to use objects, etc with what he had made prior to class... it didn't work. He made some comment like, "This worked 10 mins ago in my office." and tried to figure out what was wrong. A few french curses later and we got, "THAT IT! Class is over." And he walked out.

After working against access during the course I found that his reaction was appropriate. In fact, a class-mate that I worked with and I found out that access decides almost randomly when an object exists or not. In the end, we figured out that it had some relationship to the running context (which it shouldn't have), but I digress.

Since then I have purged all access knowledge from my mind. I think I'm the better for it. Or at least my sanity.

So, my advice would be to figure out how the system works, and convert it to a different DB. Maybe PostgreSQL? It's not like management will be able to figure it out.

Re:Access isn't all bad - ignore the /. bias

[sd4l]

THE major advantage of replacing Access for the casual users is that you then no longer need to deploy Access, which will save you both licence money and support time.

Although I disagree with most of your post (having developed applications in Access at the start of my career 10 years ago and, reluctantly, quite recently) the above is the point I'd like to take to task.

If you are deploying to a large number of people you'll likely deploy the royalty-free Access Runtime which allows you to run an access database but not edit it. It used to be part of the Access Developer's Toolkit (think Access 2.0 and 95 sort of time), then became part of the Microsoft Office Developer product (think Access XP) and now is part of Access 2003 Developer Extensions which comes with MSDN Universal. For a professional developer doing a large deployment, it's invaluable.

I'd also just like to say, don't underestimate the speed of an experience LAMP developer. I can developer web apps far faster in PHP than in Access (and they don't auto-submit amended data back to the database if you click on Previous Record by default).

Re:Access isn't all bad - ignore the /. bias

[cruachan]

The problem with deploying Access for widespread use on a casual basis, even with the runtime, is that the various dependencies cause issues. Different versions of libraries etc can rapidly end you up in a support quagmire. True if you've limited yourself to vanilla Access and not made use of anything non-standard it may not be too bad, expecially in a standardised environment, but in my experience it's rare for a business application not to make use of other Office products and environments in all but the smallest companies are to some degree heterogenious. These problems may or may not surface to a greater or lesser extent, but it's always going to be more of an issue to support than a browser application. Hence IMHO there is a certain degree of functionality that is required by a user to make it cost effective to deploy Access.

On the other hand if your user does need complexity then the deployment overhead is often worth it. I'm sure you're a wizz with PHP, and indeed I can throw together a nice PHP bases web application myself with some speed - slapping up a simple Access data entry form and a simple PHP data entry form is indeed probably of similar order of development time. The problem is that in virtually all the reallife business applications I've come across a simple data entry form won't cut it.

We're talking here of such things as dynamic drop down lists (i.e. context sensitive to other data entered on forms), calculations on the fly (i.e. enter net price, then lookup database to determine a gross price depending upon item classification and commission), showing/hiding areas of a form based on data entered, dynamically checking data entered against a database, non-standard controls, integrating graphics and charts, creating or merging Word/Excel documents etc etc. All of which needs to happen responsively - i.e. you'll need extensive Javascript and Ajax coding as the form display/submit cycle is not acceptable.

I'm not saying that you can't do (most) of these with LAMP or find a work-around, just it's a lot more hassle than using Access as a front end, a tool that was designed specifically for business data processing. Cases need to be examined on merit with regard to circumstances and budget to decide what approach is appropriate. What does bug me though is the multiple ill-informed knee-jerk comments on most of this thread along the lines that Access is Microsoft, it's VBA, it's not Open Source and therefore you should throw up your hands in horror, refuse to touch it, and run for the hills. Oddly enough I'm just about to start a project for a major global charity recoding an Access/SQL Server application to WISP precisely because they need to be able to deply an application more widely than they currently have and don't want the Access overhead, so you'll see my comments are based on pragmatism, not ideology :-)

Finally I'm fascinated by the line that you disagree with most of my previous post. What's to disagree with? SQL Server isn't one of the best databases going (you can't honestly propose to run a back-office business system on MySQL?) MDB vs ADP issues? That Access isn't a good front-end application to a back-end database? Identifying groups of users and deplying appropriate tools for each? None of this is contentious, it's common sense.

Re:Tools

[afidel]

I don't have mod points today so I'll just respond. What you have said is VERY correct. Access isn't a horrible design tool or presentation layer, it IS a horrible database. Developers who base commercial products off it should be shot and those who base internal projects off it should be educated. Anyone who's doing real development work probably already has a license to redistribute MSDE, and internal developers don't need one. I have a friend who's a programmer/DBA and probably 90% of his workload is taking messes made with Access and converting them to Access frontends to SQL/MSDE.