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

In the Beginning

Now that you have removed everything obvious, its time to move on to normalizing. You can do this one of two ways: either normalize the data in the existing database; or create normalized tables in the SQL database and force the data into the proper format using VBA code when you push it into the database.

Which Method

In my case there is a second database that receives nightly data dumps from the main accounting database. This second database is used by other employees solely for the purpose of running reports. One of their chief complaints is that the data is only up-to-date as of the previous business day. This second database uses duplicates of many of the tables in the main database. For this reason I chose to migrate the secondary database to SQL first since it is less mission critical and I can get the kinks worked out of the process.

The Surgery

I began by listing all the necessary tables for the reporting database. I then looked at each table and determined what needed to be done to normalize the data. This took quite a bit of time and research, not to mention a lot of scratch paper.

Once I had determined the best table design I then proceeded to create the tables and relationships necessary. The original access databases had absolutely no table relationships, and because of this, data was constantly being corrupted by people directly manipulating tables without updating the tables containing related data.

The next step was to create an interface for getting the data into the sql database. Since I am using stored procedures this is a two step process. The first step was to create the actual stored procedure that will do appropriate data checks and lookups before writing the records into the database. The second step is to create the VBA procedures to actually push the data using ADODB. There are two vba functions: The first is to do bulk uploads of all records and the second is to update records when they are modified in the acccess database.

Function number one is pretty straight forward. Create the ADODB objects necessary. Get a recordset from access containing all the records to upload. And finally, loop through the recordset and call the stored procedure for each record. The stored procedure will return the record index of the record and write it back into a newly created field in the access table. This will provide the link between the data in the access database and the sql database for future updates.

Function number two will be called from forms whenever they create or modify a record. If they create a new record the RecIndex field will be empty and the code will know to add a new record and return the record index so it can be placed back in that field. If they modify a record that already has a RecIndex then it will call another stored procedure that updates the corresponding field.

The Work Begins

This process will take some time to complete since I have to do this for over 10 tables as well as creating the hooking functions in the forms to keep the data up-to-date. The next article will look at modifications to the existing database and reports so it can use the data in the database.