Access all areas?

In past columns, I’ve seriously castigated Microsoft’s Access database, calling either for it to be allowed to die naturally or, better still, for merciful euthanasia. Access wasn’t withering for want of attention – each successive version of Office brought it new file formats and features – but Microsoft never undertook a proper refit to fix those older features that are broken, dodgy or downright dangerous. This time, though, in Office 2007, Access has undergone its biggest change yet, with the introduction of the new Ribbon UI and the Workspace model for tools and documents. There are new views, new tools, new ways of working, new data types and, of course, yet another new file format. Microsoft has given Access its own version of the JET database engine, called ACE, extended to handle data types that previously only existed in SharePoint. Access is re-targeted towards lower-skilled “information workers”, making it a natural upgrade from using lists in Excel, or a stepping stone from SharePoint. Is this enough to rescue Access from its reputation as a tool for designing bad databases?

To my mind, the new Ribbon UI sits less well on Access than it does on Word, Excel, PowerPoint and Outlook, because Access isn’t a document-centric application where you need the text-formatting tools all the time. There are only four main tabs on the Ribbon, but many context tabs that become available depending on what task you’re performing. Create a new table, for instance, and you get a context tab for either Table Tools | Datasheet or Table Tools | Design, depending which view you’re currently looking at. Many of these tabs contain few commands, which leaves a lot of unused space on the Ribbon. Occasionally, most of the main tabs disappear and all the commands get greyed out, which can be quite alarming: modal operations (that is, ones you must complete before you can do anything else) really ought be presented via a separate modal form, not as document tabs that disable everything else. This is a fundamental flaw in the way the new UI has been applied to Access.

To show off the new user interface and ways of working, all the sample templates have been replaced with new ones related to Assets, Contacts, Issues, Events, Marketing, Projects, Sales Pipeline, Tasks, Faculty and Students. Each is a fairly complex application that you can customise to suit your own purpose, provided you can understand how it works and how to extend it. Unfortunately, that’s a far from trivial business, as these samples don’t appear to come with any help text aimed at the user, let alone at developers. When you attempt to alter a sample, you’ll find that many of the buttons on the forms have actions defined in the execrable Access Macro language rather than in the far more readable Visual Basic for Applications (VBA). Microsoft recommends using macros rather than VBA “for security”, because macros are restricted in what they can do, and can be further restricted to just those actions that don’t require granting trusted status to the database. VBA, on the other hand, can do just about anything, including deleting all the files on your hard disk. If you add any VBA code to your database, it will trigger a security warning information bar whenever it’s opened and run with restricted functionality until the user accepts the warning. You can avoid these warnings by digitally signing the database so the user can tell Access to always trust your signature.

Sorting and filtering