Ian Roberts - GUI Computing
One of the latest tools that I have had the chance to use was the Access 7.0 Upsizing Wizard. Maybe not the most exciting tool, but useful nevertheless. The Wizard installs two Add-Ins to Access 7.0, they are the Upsizing Wizard and a SQL Server browser. This Wizard will only work if you are running '95, or NT, and Access 7.0.
The Upsizing Wizard takes an Access 7.0 database and creates a 4.2, 6.0 or 6.5 SQL Server database, with the same table definitions, indexes, data, rules and defaults. Queries are not able to be upsized automatically but can be exported, simply by using the SQL Server browser (as I will explain a little later). If you want to upsize an Access 2.0 or 1.0 database, you first must convert them to Access 7.0. The Wizard will ask you if you would like to use an existing SQL server database device, or create a new one. If you select a new one, you are then asked for the sizes of the database and log devices. Then the Wizard creates the devices ready to accept the new tables, etc. If the names you have used for tables and fields do not conform to the naming limits in SQL Server, the Wizard will rename them to fit.
Next it exports all of the tables that you have selected for upsizing, then creates the indexes. If validation rules were set up in your Access database, as many as possible will then be exported. For SQL Server 6.x, you have the option of using declarative referential integrity or using triggers. In the event your Access database contains cascading updates or deletes the wizard will default to using triggers to maintain the cascades. If you are not fussed about upsizing your cascades, you have the option to override the default and use DRI anyway.
Once the database objects have been built, you may have the data that resides in your Access tables exported to the new SQL Server database. Then your old Access tables will be renamed to tablename_local and the new SQL Server tables are attached to your Access database. The final task is to create queries or views as necessary, to alias any changed field names back to their original.
The Upsizing Wizard is like most Wizards - a pretty simple tool to use and very effective in its job. What I found most useful, though, was the SQL Server browser. Once you have started the browser you will have an Access-like interface to your SQL Server database, showing tables, views, rules, defaults and procedures. The nice feature was that you could use this interface to create a new table and modify field names, build and edit views, rules, defaults and procedures. With the procedures, the ability to define parameters was as simple as clicking the parameters button - then add the name, datatype and size to the list. All that had to be built then was the core of the procedure, and the browser would build the create procedure header.
Another thing I found useful was the ability to export the SQL Server tables back to Access. This was very handy when, while adding a field to a table, I set the wrong datatype. Being one of my early exposures to SQL Server, it was a lot easier to export the table back, make the changes, and then run the Upsizing Wizard to upsize the modified table. When the wizard discovered that the table existed on the SQL Server, it gave me the option of overwriting. On answering 'yes', the SQL Server table was dropped (deleted), and the new one with changes put in its place. All very quick and tidy.
As I indicated earlier, queries can not be automatically upsized. But, because when you run the browser you also have your Access database opened in the same window, it is a simple matter of opening your query in design mode. Then just copy the SQL to the clipboard, create a new view or procedure, and paste the SQL into the edit window. The only thing needed then is to modify the SQL to conform to transact SQL syntax. For the most part, unless you are using a lot of inner, right and left joins, or are trying to convert a crosstab query, not a huge amount will need to be changed. While the joins can be recast with a little transact SQL knowledge, or a good read of the manual, crosstabs can only be used by the Access Jet Engine.
One last feature of this tool, that I think stands out, is that it is free. It can be found at www.microsoft.com. Once there click the support icon, click Knowledge Base, select Microsoft Access, enter kbfile Upsize95.exe and click GO. Open the article and click the download button.