Cleanup and Migration of Access Database to Microsoft SQL - Part 5

The Saga So Far:

In episodes one we covered the basic pains of cleaning up the database tables, queries, macros, etc in order to get a clearer picture of the project scope. In episode two we covered business process analysis. In episode three we covered the process I am using to normalize the data. In episode four we covered basic design criteria for the basic interface.

Access Strikes Back:

I started writing the application to synchronize the access database with the new SQL database in realtime. It used an XML file to control how data changed formats, and even include dynamic sql calls where needed to do lookups (example: converting employee names into employee Id numbers to link into the normalized tables on SQL).

The application worked great. I had added boolean fields to the tables on both access and sql to notify the app when a record was changed and that limited the number of rows that were being returned in the select statements. I added the hooks in the access forms to update these fields since I didn't have triggers available for use. Everything looked fine....until I started running it in production.

I started out simple, adding one table at a time to the live sync. I had narrowed it down to a necessary 15 tables. Unfortunately some of these tables had over 90 fields. Anyway, after I started synchronizing the third table, the access database began to get corrupt and I had to do a weekly "compact and repair" to fix the issue.

As a result of the regular database corruption I switched back to using the nightly push from access to sql and modified the plan for rolling out the new application. Instead of migrating users one job function at a time, we are developing the new system in parallel and will let them "play" with it until they sign-off on the app and we put it into production.

During this process I got together with the guys who wrote our web base time entry system and we decided to design a common middleware piece for our two applications to interface with. I will be using .NET Remoting for my accounting users and they will be using XML Web Services to access their data. This will allow us to develop common classes for the different business objects and apply business rules that will be consistent across all the applications.

We'll see how it goes. We have the diagram for the middleware piece done and are starting the coding of the base object classes (timesheet,employee, blah blah blah).

Check back later for the next update to the saga!!!

Some days I hate computers ;)