PostgreSQL is a robust and feature rich database that can handle significantly more load than Access. It also integrates much more closely with a web site for dynamic content (such as this one). It allows better than row-level locking for multiple concurrent user read-writes. And it's cheaper than Microsoft Access, because it's free. And being Open Source, there's a rich community of developers and experts who can help you out with any problems you may have.

So why isn't it used more? Well, it's not very well known. And being Open Source, it doesn't have the "user-friendly" elements that make some applications more palatable to the general public, such as a built in GUI. Also, traditionally, MySQL is the chosen database of Open Source. This isn't the place to go into the details of MySQL versus PostgreSQL, as it's been done to death elsewhere.

So let's start digging into the details. First off, you're going to need to install PgAdmin on a Windows machine - trust me, this is going to make the whole process much easier. You can get a copy of at pgadmin.org, and then you'll need to also install the MS Access upgrade wizard. There are details of how to do this on their site, or you can take a look at the article on this site concerning installing PgAdmin. [Update 2005-04-06 - PgAdmin3 (the current version) doesn't support the Upgrade Wizard. If you need to use the Upgrade Wizard, it is available here]

Well, this is all pretty dry stuff so far, so rather than pour through manuals of how to, I thought it might be more useful to go through the steps that I took in a real world situation. So, with that said, let's press on to the Case Study

Here’s the situation I was dealing with. An Access database of health resources, with your basic contact information, some other, more specific information regarding the organizations that were listed in the database, and then a large number of boolean fields of the types of resources they provided.

The boolean fields were beginning to present a problem, or rather, a number of problems. The first of these was that there were too many of them for Access to process in an "UPDATE" query, so whenever I wanted to update the database, I had to run two separate queries. Also, as the project that the database related to was growing, there was the distinct possibility (for which we were actively planning) that the number of resources we would be tracking, and therefore the number of boolean fields in the database, would be growing significantly. And given that there were already problems with the number of fields, this was only going to get worse. On top of all that was the problem with the data structure. As it stood, all this information was in one table, which was not a very efficient way of maintaining the database. So, there was a design element that needed to be addressed, as well as the upgrading to PostgreSQL.

Well, to start off with, I used the very smooth upgrade wizard within PgAdmin to actually get the information from MS Access to PostgreSQL - it's great at importing tables and data, and converting table names to lower case, etc. if necessary. However, there were a few things that I had to do to ensure a completely smooth process.

Foreign keys weren't correctly imported, so the only way that I was able to set this up was to import the information and then run some SQL to generate foreign keys on the tables. I did this by testing out what SQL would work best, and saving it to a text file in Notepad. Then, whenever I was ready to import the information, I could run the import wizard, and then run the SQL directly from PgAdmin by cutting and pasting.

This turned out to be a pretty efficient way of managing the migration process, as it allowed me to do a bunch of other things that I needed to do as well, including altering the table structure. So, what I did here was to generate some SQL that would "SELECT INTO" a new table the basic contact information for all the records I was dealing with. It would then assign the table a primary key. It would then do another "SELECT INTO" from the same table into two different tables of simply the ID number of the record, and then the appropriate checkboxes for the two types - in this particular case, for breast cancer resources in one table, and for cervical cancer resources in another table. I would also have the SQL statement that I ran at this point add foreign keys to these tables, and also create another table for prostate cancer resources (which was being added to the database, and had prompted the upgrade in the first place) with the appropriate foreign key. Finally, I'd use the SQL statement to assign permissions to each table.

So, the upgrade process was, at this point, simply a matter of running through the process of upgrading through pgAdmin, and then running the custom generated SQL script. Of course, once I'd upgraded, I did need to port the web-based application that it was running on from ASP to PHP, but as far as I know, there's no reason why I couldn't have elected to keep it in ASP. Either using ASP on Apache, or connecting to the database via ODBC would have been viable options at this stage.

In any case, the take home message here, is that planning for a migration process in this kind of way, and testing the migration process as well, is a great way to go. And at the end of it, you'll have yourself a more robust database system which can be sliced or diced many more ways than with Access. And for the diehards among you, you can still use an Access front-end with PostgreSQL via ODBC.