Review Of SSW Data Renovator to Convert Backend Databases

A number of weeks or months after your Microsoft Access system goes into production there will come a time . when you have to change the backend database and make some software improvements to support those changes. At this stage the information that has been added to the database has probably become become far more valuable than your software solution. Managing this change is quite difficult especially if you do not work in the same physical location as the backend database. This article provides an overview of this process and reviews a software tool called SSW Data Renovator that was designed for this particular task.

Changing A Live Backend DataBase

There are many different things that you can do to manage the process of backend database changes but here are some of the methods that you might use.

· You will ask the client to stop using the database, they will send it to you and you will make the changes to the backend database and rapidly make the changes to the software to match. This process could also happen on site. · You make changes to the structure of a "Master database" that will now be out of sycnronisation with the "Live database". You will need to record these changes in a document file so that you know what to do when the Live Database is sent to you. You will then make changes to the software in your own time. When the software is ready, the client will send the Live Database to you and you will make the changes to the structure manually. · Another method is to develop queries to transfer the data from the old structure to the new structure. You will have these ready and tested to use when the client sends you the live database. These queries tend to be delete queries to clean out the data in the Master Database followed by Append queries to transfer the information from the Live Database to the empty Master. There are usually some update queries here to change some data as well . Figures 1 and 2 show the instructions and queries that I developed for one conversion project. · Finally the most complicated approach is to develop a combination of queries and visual basic that will change the database from the old structure to the new structure. This approach requires a very good understanding of DAO especially if you have to modify relationships and field properties and change field sizes. With this approach you or your client can import a form with all the conversion code into the Live database and run it to make the changes.

Managing this process of Back End conversions can be really easy through to the most complicated individual task that you will ever have to do in Access. Of course there are 4 other tasks that you must perform and they are Backup and Backup followed by Testing and then Testing.

Figure 1 -Transfer From A Live Database To A New Master Database

Figure 2 - A series of queries that must be run in order to transfer from Live to a clean Master.

SSW Data Renovator

Into this minefield of complexity and valuable live data steps an Access utility called SSW Data Renovator. The program is designed to transfer information from an old database structure to a new database structure. It does this by analyzing both databases and working out what has changed between the old and the new. The software was written by a company called Superior Software for Windows (SSW). SSW is managed by Adam Cogan who runs the Sydney Access /SQL Server /ASP Users group. As the software is easy to run, the best description of what it does is showing it in use. Preparation and Defining Your Project

Backup your databases and prepare your testing queries. Then tell Renovator where your old and new databases are collected. This information is saved as a project.

Selecting Your Options

Now you have three choices, clear the database, compare the structure and transfer the data (see figure 3). Unless you are a kamikaze programmer, you will most likely select Compare databases.

Figure 3 - All the available software options in SSW Data Renovator

Comparing The Database Structures

The software will produce a number of reports as shown in figure 4. You will naturally want to run all the reports but I will describe the compare database structure as this requires some input. In figure 5 you will see a list of the fields that differ in some way between the databases. At this stage you will need to have some documentation as to what has occurred so that you can decide whether a table/field has been deleted or renamed. The renaming feature is really quite neat because when you choose which field operation has been set to Renamed To, the Add field operation for the corresponding field is modified to Rename From. As it states at the top of the form, look carefully at the Delete field operation. Here is the one modest issue that I have with the software. Once you have changed the field operations from "Delete" and "Add" to "Rename", the software unfortunately does not remember your changes if you close down the project or go to the Automatic Data Upgrade option. This would be a concern on a large conversion project.

The compare indexes and relationships are both reporting functions that will prove very useful if you are managing the conversion process with your own software. You will be able to use these to check that you have added all the conversion steps in your own project.

The count records option is an absolute must to run before you clean the Master database and after to compare the records were transferred correctly. This step must be undertaken with care as it is a key to ensuring that the software works well.

Figure 4 - Data Renovator Comparison Options

Figure 5 - Confirming The Changes To The Database Structure

Cleaning The Master Database

One of the main tasks involved in a project like this is emptying out the Master (Target) database prior to running the transfers (see figure 2) . Anyone how has done this manually will know that this involves finding the tables at the lowest level in the relationship diagram. SSW Data Renovator refers to this as the population order. This software does all this in one step. The software does allow you a choice a choice of what tables to empty and what tables to leave data in. Unfortunately the software does not remember your empty table settings with your project here.

Clear And Copy - Automatic Update

The clear and copy option (figure 2) is very similar to the steps in compare tables. Once again you have to manually define which fields and tables have been renamed and deleted. Then you press the Next button and the software handles all transfers for you. This is the magic bit and it works.

Figure 6 - Converting The Database - Illustration of the changes to be during the transfer.

How I Tested The Software

One of my clients runs software that I developed for rugged field laptops that can be dropped in the mud. They currently run a number of copies of the software and all of them have different data on them. My task was to change the data structure on these laptops using software as it was impractical to return all the databases back to my office via email. I did this with a conversion program which I have used as the basis for testing that Renovator works out the changes and transfers the data correctly. The testing would be considered OK if at the end of this test, both databases contained the same information. The other things I was looking for was that the database comparison reports identified the changes that I undertook in this transfer project.

The changes in my database included adding 11 fields in different tables, resizing 20 fields, dropping a number of relationships and changing properties on 30 fields. I also had to drop and add many indexes and relationships. The reports reflected all these changes.

Testing The Conversions Using Metrics

Firstly return to the number of records comparison option and compare the number of records in each database.

Now it is time to write some testing metrics to analyze the fields in the database. You should do something like the following for every field that changes or is renamed in the process of transfer. This query displays counts for a field called Fabric_1. This used to be called Fabric in the old database.

SELECT lithology.fabric_1, Count(lithology.fabric_1) AS countFabric FROM lithology GROUP BY lithology.fabric_1;

You will need to write a similar query for the old Live Database.

Why SSW Developed The Software

Adam Cogan from SSW says that they initially wrote the software to help manage 30 separate copies of a database for Tennis Australia. Each of these databases were being sent into SSW offices on an irregular basis and it was taking SSW a long time to resolve the differences in the data structure between the current version of program and the older version. This program helped them automate the changes to the backend databases. SSW now manages the process using a separate application called SSW DataPRO! for applications where they have full control of the development environment.

Downloading And Getting Started

You can read about the software from http://www.ssw.com.au/SSW/DataRenovator/

The software can be downloaded from http://www.ssw.com.au/SSW/Download/default.asp

More information on transfers can be found on http://www.ssw.com.au/SSW/database/databasedocs&links.asp

The download involves registering your email address but considering that you have 50 uses of the program before registration then that is pretty fair. Once you have registered your email, you choose the products that you want to download and they are emailed to you quite quickly. Warning that the email are 4.5 megabytes after MIME encoding so you may need to download one product at a time. Do not even bother with Hotmail or other free email accounts as this will blow your file size limits. The software is available in 97 and 2000 formats.

Installation was a breeze as it involved running a Wise Install Executable to install the software in the usual way. Purchasing the software could be enhanced by using an On-Line secure payment system. At the time of writing the price was US $199 dollars.

Help Guide And Security

The help guide is provided in a step by step guide in HTML format. Quite a simple step by step user guide that is adequate. If you use security on tables in your database, you will need to trial the software before you purchase it. I did have trouble with one secure table which I resolved by fixing the security on the table.

Conclusion

This software is easy to install, easy to run and it handles what can be a very complex task very well. I would recommend the product for a lot of transfer exercises as it will allow you to concentrate on the job of getting the newest database design correct. Of course there were some issues with remembering some of your input but even on my quite reasonable transfer project, these changes were easy to implement again. Of course this is an important job for a database administrator and time saved with this program can be spent wisely on more rigorously testing that the transfer was completed OK. And documentation of the renaming and deleting of fields should still occur as the program requires this input.

Thinking back this program could have saved me about 30 hours in the last year and as the program is delivered to you fully operational for 50 trials, then you should have no trouble deciding if it OK for you.

Author Bio. Garry Robinson is the founder of GR-FX Pty Limited, a company based in Sydney, Australia. If you want to keep up to date with the his latest postings on Access Issues, visit his companies web site at http://www.vb123.com/ or sign up for his Access email newsletter here. When Garry is not sitting at a keyboard, he can be found playing golf or feeding the kangaroos in his backyard. Contact details … accessat gr-fx dot com +61 2 9665 2871

Other Pages On This Site You Might Like To Read

Consolidation Queries

Setting Up A Scatter ( XY ) Graph In Microsoft Access

Data Mining Using MS Access