Peter Mac And Associates

Upgrading from Microsoft Access – The options

A few of my clients have had similar experiences with the Microsoft Access database system. As a light-weight non mission critical solution it offers ease of use and in the right hands can be extremely functional. The problems start usually after a few years of use when a database moves from the ‘useful’ to the – ‘oh my God…we’re stuck – the database has fallen over!’

The Database Hobbyist

Microsoft Access in the right hands is a useful tool for data capture and reporting. One of my clients started to develop his own in-house client recording system around 2001. He’s in the criminal law business and has pretty specific requirements such as recording hearings and their results. He then started to move some workflow steps into his home-grown solution.  The problem is that six years later his Access database could take up to 20 seconds to perform an update.

The problem with MS Access is it’s not designed for large datasets especially if the data is text or image based. When you start experiencing insert and update delays and/or you’re left waiting ages for the database to start-up it’s probably time for an upgrade.

Database Upgrade Options

If you’re tied into the Microsoft camp (and I can’t see why you should be), you have a couple of options. You can pay the big dollars for a SQL Server installation and a relatively straight-forward data migration path using SQL Server’s data migration Wizard. You also have the lower cost option of using the Microsoft Desktop Engine (MSDE). This offers pretty much the same functionality as SQL Server but less in terms of development tools and scalability and backup options. MSDE is not for the Enterprise, it’s for the corner-shop.
Alternatively if the concept of paying license fees does not sit well, you can go for the ‘open source’ solutions of MySQL (pronounced My seequel) and PostgreSQL (pronounced post gres Q L) These are database systems that have been developed by teams who provide them free of charge to the community at large. There are commercial options with each solution where you can get support agreements, training and the like, but if all you want is the database up and running on your site for free, these are the options for you.

Both MySQL and PostgreSQL offer migration options supplied by the database vendors (can you call someone a vendor if they give you stuff for free?). There’s also a couple of third-party solutions such as those provided by SQLMaestro and DBConvert.

Don’t migrate bad data design

Whatever solution you go for, you’ve got to be careful you don’t migrate bad database design. There’s a whole lot more to designing an efficient and workable database than using a point and click wizard to build a table to store your contacts in. Of course, at Peter Mac we’d love to help you in this regard, but if you already know what you’re doing there’s a whole lof of online support you can get from the open source communities.