2008-10-16

Microsoft Access Runs Slow

*** Check the printer (see below)

Why MS Access?
Ugh. Most of the research that I am involved with has the requirement that data be collected in isolated settings. Which means no internet, no network, no support and sometimes not even any power. Still, the principle investigators would like data to be entered on site. I like using a web front end on a nice enterprise rated database management system, however this just doesn't cut it for the remote sites.

Inevitably, after examining the offerings of Epi Info or EpiData, we fall back to Microsoft Access. Not because MS Access has any particular inherent quality over these other packages, but more because if there is any local support then they may very well be able to handle MS Access rather than EpiData. Plus most investigators know of MS Access and have seen some of the slick databases that we have produced as opposed to the almost hideous but functional results one gets from other software.

What is wrong with Microsoft Access? Many things. The worst of which is that the databases tend to bloat themselves full of air rather quickly, esp if I start dynamically modifying forms; in 2007 you can tell access to compact itself on exit ... but if you're running the database on a network drive then you might just find the entire database has deleted itself, I kid you not. 250 fields per table is a very sad limitation with regards to questionnaires despite what some normal form nazis would say. Occasionally MS Access will put itself in a state where it will crash for no readily apparent reason when editing forms, although I have to say that version 2007 does this far less frequently. The object oriented nature of things means that vital options are splattered all over the place in hard to locate places. Visual Basic for Applications (VBA) can make discovering that vital little property a rather lengthy investigation (but nothing as bad SAS). Form construction can be a right dog compared to HTML. The creation wizards (forms, reports, tables etc) are almost worse than useless.

However the results can be pleasing, and the level of control is well above that of most other packages. Once you have built up a set of standard VBA modules then you can become quite efficient at producing solid forms which can produce codebooks, analysis conversion code, some statistical output and so forth.

When Microsoft Access runs slow
Occasionally you run into a nasty problem; like when Microsoft Access (2007) starts operating exceedingly slowly when you are editing forms or reports. Clearing temporary files, removing and reinstalling Microsoft Office, cleaning the registry (thank you CCleaner) just doesn't seem to do squat. Then just as you are about to defrag the drive and are contemplating wiping the whole system to reinstall everything from the operating system up, you realise the network printer server is down. Set the default printer to something local and MS Access speeds right back up again. What the hell?!