Cleanup And Migration Of Access Database To Microsoft Sql - Part 4
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.
The Next Step:
Now we have to determine whether to keep the front end reports and forms that users currently use in their day to day operations or to design a new front end application from scratch. In order to determine this I first looked at form design.
In our case the forms used very little code, and the code they did use was autogenerated for buttons. This meant that there was no data validation going on and most of the actions performed by the buttons were handled by macros. This did not lend itself to an easy upgrade for use by SQL server if I wanted to increase the reliability and functionality of the application. The forms also made heavy use of text boxes where combo or drop down boxes should have been used to limit the choices to valid ones for the field. This has caused me no end of grief in trying to normalize the data as it is uploaded to the SQL Server while maintaining the current level of functionality.
Our reports were mainly done outside of Access in Crystal Reports. During the migration saga the company also decided to move to a new reporting software so all the reports were being redesigned anyway by another individual. This person was rewriting them to work with the SQL Server so this became a non-issue.
After weighing the time required to overhaul the myriad of forms and macros and turn them into an efficient reliable user interface, I decided to develop a new front end application in parallel to the current Access Application. Since there are several pieces to this application I needed to devise a way to keep both databases synchronized as I migrated functions from the Access application to the new application that used SQL Server to prevent issues for the various users doing the different functions (TimeKeeping, Accounts Receivable, Accounts Payable, Project Management, and Payroll). This process needs to be in place and working before I begin to design the new program or I will never get anyone to use it.
The next part will go into more detail about the synchronization application and the pitfalls I experienced. I decided to do that in the next segment because I am writing the app as this is being posted to the site. Check back in a couple of weeks for the next part.
http://www.greatnorthcomputing.com A place for sysadmins and programmers to share information, code, tips, and tricks.
Tags: Data, Data Migration, Microsoft Access, Microsoft SQL Server