Danny Lesandrini

Danny Lesandrini predicts that in the year 2010 we're going to look back and decide that the release of the Microsoft Access 2003 Conversion Toolkit was the most significant event of the decade for Access consultants. Danny explains (along with comments from Jon Sigler, Group Program Manager for Microsoft Access) why, if you plan to make your living delivering Microsoft Access applications, you owe it to yourself to consider the Conversion Toolkit's place in your practice.

On July 27, 2004, Microsoft released Service Pack 1 for Microsoft Office 2003 and, with it, a new tool dubbed the Microsoft Office Access 2003 Conversion Toolkit. In its simplest form, the Access Conversion Toolkit consists of two pieces:

• An Access database scanning/analysis utility

• An HTML documentation application

While I could dissect these applications into their constituent pieces, I'm going to keep my review at a high-level perspective to avoid getting mired down in the minutia. How each individual component works isn't nearly as interesting or as important as what this tool can do for you–which is what I'm going to focus on. This tool is especially important for anyone (including your clients) who's concerned about upgrading to Access 2003.

It's not a Help file...

The Access Conversion Toolkit is free and may be downloaded from the Microsoft Office Web site. Simply go to www.microsoft.com and search for Access Conversion Toolkit. In addition to the free download, you'll find a brief overview of the tool, some screen shots of the product, and a list of system requirements necessary to install the Access Conversion Toolkit.

Before you run off and point your browser to the download page, there are a couple things you should know. First, the setup file isn't a trivial download, weighing in at just under 53MB. Second, the Access Conversion Toolkit doesn't actually convert your databases. It's designed to help you with the conversion process. At first I was disappointed, until I came to understand the purpose and potential of the tool.

The bulk of what you get for your download is the documentation, which includes numerous Flash presentations that walk you through the process, the tools, the management practices, and more. I'm sure the first thing that you'll do is launch the conversion reporting tool and have it scan your computer for mdb files, analyze those files, and produce the reports for you to view. After that, though, you'll simply have to return to the documentation because that's where the real value of this product lies. It's not a Help file, it's a training course in Access database conversion methodology.

I'll return to the documentation module later, but, like you, I love clicking buttons and watching file names whiz by in the progress status bar. So I'll begin by looking at the Scanning/Analysis part of the user interface (see Figure 1). After starting the tool and selecting various shares and/or local directories, you click the Search for Databases button to begin the search and analyze process. The process is fast. Because I'm an Access developer, I have nearly 400 database files on my local network; despite that, the search process finished in just under eight minutes. You then select which databases you want to analyze (presumably, the ones that you intend to upgrade), pick which analysis options you want, and click Analyze Databases to start the process.

Figure 1

Again, this was surprisingly fast (it took only 13 minutes for my 380 databases). When the process is finished, you're presented with a message box that says, "Done scanning, enjoy!" I got a kick out of that, and it's representative of the type of friendly, casual experience you get from the Access Conversion Toolkit. In fact, the process is so casual that the resulting file itself is an mdb, not an mde, and all the source code used in the analysis is available to you for you to review, leverage, tweak, or modify to your heart's content. One of the first pieces of code that I peeked at was the function behind the switchboard form that causes the labels to behave like Web page hyperlinks (I've implemented that UI feature in several of my applications, even writing an article about it, and I was pleased to see that the Access team implemented a solution similar to mine).

After the analysis is complete, you can close the completion form and return to the main switchboard. Your next step is to review the analysis discovery to see how many conversion issues you face. There are a number of stock reports and charts that come with the tool. Because of their obvious visual appeal, the charts were the most interesting to me. As an example, Figure 2 shows the breakdown of database analysis by conversion issue. The databases I analyzed show only a handful of issues, most of which are easily corrected with minimal user intervention (such as compiling the database or dealing with linked tables). It gets trickier if your database references an additional library or uses the DAO 2.5/3.5 Compatibility Layer. Even so, both of these issues are handled gracefully by the conversion features in Access 2003, especially if you have Service Pack 1 installed.

Figure 2

Of the eight non-graphical reports that come with the tool, I found two of them useful: the "Executive Summary" (shown in Figure 3) and the "Database and Issue Details." Since the Access team included the source code, you can tweak existing reports or make new ones to meet your needs. I found some of the label dimensions and the layout formatting to be a little clunky and with too much white space for me. If I were doing this analysis for a client, I'd want to personalize the reports, adding my company logo and contact information before delivering them.

Figure 3

In addition to the formatted reports, there's a data-bound form that allows you to examine the metadata produced for each analyzed database. You can filter the list of databases for specific ratings or by the last modified date. The form gives you a list of database objects by category, including details such as table fields and the SQL behind queries. It's not the documentation tool that Total Access Analyzer from FMS is, but it's free, along with the code used to extract this metadata.

Process, tools, support

I have to admit, I'm at a loss for how to describe the documentation, other than to say, "It's not a Help file." For example, the first option in the documentation is named Process Overview and it uses a fictional case study with the Northwind database as an example of how conversion can be handled. It's a little corny, but reviewing each step in the process helps those with an interest in the conversion project to understand what the issues are, who should be responsible for each piece, and what action items belong to each member of the team.

The section titled "Using the Tools" is a great resource for learning how to get the most out of the scanning and analysis utility. Tutorials and simulations enhance the printed documentation (and the uncluttered presentation is a pleasure to review). For example, the scanning tool may be run from the command line. One of the simulations walks you through the process with screen shots demonstrating each step in the process.

The Sales Support and the Management Support E-Briefings are well-prepared Flash presentations that provide high-level overviews of the Access Conversion Toolkit, its goals, and its components.

Finally, there's the Performance Support Center, which is a searchable database of information important to the actual conversion. A catalog of system messages helps you understand the conversion issues you face and gives you access to an abundance of white papers describing solutions at the click of a link.

How the Toolkit can help you

At the beginning of this article, I predicted that the release of this tool is a significant event for Access consultants. That may have sounded like hype, but here's why I think that's true:

• It demonstrates Microsoft's continuing commitment to Access.

• It eases client fears about upgrading to the latest version of Office.

• It has the potential for generating additional development opportunities.

I recently heard Jon Sigler, Group Program Manager for Microsoft Access, speak at our local Access user group here in Denver, and he made one thing very clear: Access is, and always will be, the best desktop database available on the market. It will not be retired and it will not be morphed into something akin to SQL Server. The release of the Access Conversion Toolkit is the evidence that Microsoft wants businesses to continue using Access as their landing pad for data.

So if Access is clearly the leading database tool, why the need for a conversion toolkit? Feedback from customers indicated that businesses would like to upgrade to Office 2003 but are afraid of the impact it will have on mission-critical applications created with Microsoft Access. I've seen this fear firsthand when I was asked to give a quote for converting two databases for a government office here in the Denver area. The client anticipated a six-month project and, as a result, didn't want to move directly to Office 2003. Instead, they planned to step up from Access 97 to version 2000. As it turned out, they were planning a six-month project for something that could effectively be done in six hours.

Others are concerned about the cost–both the cost of converting their important database applications and the cost of migrating to Microsoft Office 2003. According to Jon Sigler, one company that ran the Access Conversion Toolkit discovered 80,000 mdb files on its network. This company also determined that half of those databases could be archived and removed from active storage, saving the company enough in recovered disk space to pay for the Office 2003 upgrade. Additionally, results indicate that only 1 percent of scanned databases require intervention to be upsized, calming fears about long and painful migration paths for business tools created with Microsoft Access.

Why is this significant to you, the Access developer/consultant? First, because Access isn't going away anytime soon, nor will Microsoft discourage its use. I've heard that Bill Gates is pleased with how Access has performed, both as a development tool and as an asset to Microsoft. Maybe we'll all need to learn .NET by the end of this decade, but if we choose to, we can still be writing fantastic desktop applications with Microsoft Access in 2010.

The release of the Access Conversion Toolkit is significant because it demonstrates that the upgrade process can be painless and affordable. Maybe we, as developers, still love Access 97 as a development platform, but the office productivity landscape is changing and, sooner or later, businesses will want to move into the 21st century.

Which brings me to the final reason for welcoming this tool: It's like Y2K all over again. Consultants will be needed to help businesses make the transition from Office 97 to Office 2003, and Microsoft has given us, free of charge, the tools we need to convince, guide, and assist our clients with their conversions. Yes, I'm convinced that if I'm still creating Access databases in 2010 it will be in no small part due to the release of the Access Conversion Toolkit.