Cleanup and Migration of Access Database to Microsoft SQL Server - Part 1

Introduction

Everyone who does anything with databases, particularly Microsoft products, will end up at a job or client where Microsoft Access is being used for some mission critical database and it has not been properly designed or maintained. Don't get me wrong, I like Microsoft Access and find it quite powerful and stable to use when the database is designed correctly. But I also believe the database application reaches a certain size or complexity where it could benefit from migration to SQL server.

I am currently facing this problem. I inherited an accounting database application that was full of garbage and bugs. The data was suspect and everyone was afraid to touch it. Just to give you an idea of what we are dealing with, the database contains: 349 tables (no table relationships at all), 1569 queries, 427 macros, 312 reports, and over 50 switchboards. This is a database application for processing accounts payable, receivable, and payroll, not something used to decode DNA!

Now that you know the garbage we are dealing with you may not consider your cesspool of a database quite so bad. So on to the good stuff.

Part 1 - Where Do I Begin???

The first thing you need to do is document every object in the database and how in relates to every other. This sounds daunting, and it can be, but it is necessary for you to understand how each piece works before you can start to remove the garbage. If your database uses a switchboard and has a complicated one, I would also recommend generating a diagram that shows all of the menus that the users actually access.

I recommend using a product by FMS incorporated called the Total Access Analyzer. It analyzes a database and creates documentation on all of the objects. This documentation includes cross referencing objects with other objects that use them. The version I used had one problem. It didn't seem to check the switchboard items table when it was building cross references, so some items may not be cross referenced correctly. That may have been a problem of my own creation, or it may not, I am not sure. Anyway, using that product it got me 90% of the way documented. The other 10 % I did by hand by creating the diagram of the switchboard items that people actually used.

Now that I had the doumentation I could start to generate a list of potentially unused items. I started by listing all of the items that were in the diagram of the used switchboard items as "production" items. I then checked the Total Access Analyzer generated documentation and added any items that cross referenced the items from the switchboard. For example, if the switchboard opened a specific report, the Total Access Analyzer would also list the query that supplies the data to that report under the cross reference. If you start following all of the cross references you can find a chain of items that are all related to each other, but have no relation to the rest of the application anymore. Write down all of these items. Don't worry if you don't get them all the first time, you will make a more fine grained pass over the database as you analyze the business processes that use the application and how they work together.

Make a development copy of your database. Remove these items from your development copy and verify that your application still works as expected. Have one of the actual application users test your database. Repeat this process until you feel satisfied that you have removed everything that can currently be removed.

In the next part we will begin looking at business process analysis and making sure the application functionality fits the business process...not the other way around.