CompareEM Lite

Simple and free! Database Change Propagation for Microsoft Access

Contents

What is it?

CompareEM is a very simple to use utility to analyze a pair of related Microsoft Access databases, determine what has changed from one to the other, and then generate VBA code that will transform one into the other.

In more detail: it checks Tables, Indexes, and Relationships associated with the database. It does not check Queries, Forms, Reports, Pages, Macros, or Modules.

Why do I need it?

You need it to automate propagating database schema changes to your users.

It's pretty straight forward to put up your system for a bunch of new users. Glossing over customization and user training, all you really have to do is install your system on their machines with nice clean empty tables and turn 'em loose.

Suppose that over the following few months you add a couple of fields to a few of the tables. Maybe add a whole new table. Drop enforcement of a relationship. Maybe something else - you can't remember. Finally it's tested and all the users are clambering for it. But in those months they've all been busily putting data into their databases. You can't just send them a nice clean set of tables to work with your new code: Somehow you have to get their 'old' data into your new schema!

There are a variety of ways to handle this situation. My favorite is to have the code recognize it is pointing to an 'old' style database and automatically do the upgrade the first time it gets run. Perhaps it checks a version flag, sees it's back level, does the structure upgrade, then sets the version flag to the new level.  Simple vba code that might look like this:

Pretty straight forward. Except, how do you do the "structure upgrade"? Especially if you're not even certain what all the changes were? How do you come up with, say, the subroutine DoStructureUpgrade11?

CompareEM ! To generate DoStructureUpgrade11 you point CompareEM to a backup (1.0) copy of the old database, and to a backup (1.1) copy, then tell it to determine what changed and have it generate code to automate the changes. It makes a VBA subroutine you could rename to DoStructureUpgrade11 and paste into your code! You could make the DoStructureUpgrade10 and DoStructureUpgrade12 subroutines the same way.

Of course this isn't the end of the story. You still have to test to ensure the generated code does what you want in every situation. You still have to backup your user's data before the schema changes, and you still have to be prepared to restore their data if something goes wrong.

Requirements

Microsoft Access 2000 or XP. CompareEM is distributed as a compiled MDE so will not function with older versions of Access. If you want to use it with an older version you could (a) install the Access 2000 runtime, widely available for download on the internet. Note that CompareEM has not been tested with older versions of Access and may not work correctly or at all.

Windows 2000 or Windows XP. May work with others versions but untested - you're on your own...

How do I use it?

Simple as 1, 2, 3...  From the Main Switchboard

  1. Select the old database. When you press this button you get a file select dialog. Just navigate to the old database (.mdb file) and click open.

  1. Select the new database. When you press this button you also get a file select dialog. Just navigate to the new database (.mdb file) and click open.

Essentially the same as the above picture...

  1. Compare them. When you press this button you will be asked to enter your password. Assuming you do so the comparison will run with progress shown in the status area (bottom left of the Access window border). When it is finished a small alert will display.

  1. Generate the VBA. When you press this button you get an output file select dialog. Decide where you want this text file to be and what you want it to be called and enter that information as usual. After you click Save the code will be generated and (depending on you preference settings) the generated code will open in NotePad for your inspection.

  1. Quit the CompareEM utility, paste the generated code into whatever app you need it to be part of, and try it out. For example suppose your OLD database was just an empty.mdb (just save a new empty database in Access) and your NEW database was the venerable NorthWind. If you were to paste the generated code into the empty.mdb and run it the result would be the NorthWind tables, indexes, and relationships. What does the code look like? Try it and see!

Preferences

The Preferences form (below) shows what version of CompareEM you are using and the OLD and NEW databases currently selected, provides a means to enter your license key (see Password) and allows you to select a couple of options. The first, Show generated VBA in NotePad, determines whether Notepad is launched to show the generated code when code gen finishes. Delete tmp files when done causes the temporary files to be deleted after code gen - unchecking it is probably only useful in the PRO version of CompareEM when you may want to generate both DAO and SQL versions of the code. Consider Switchboard a 'system' file removes Switchboard changes from consideration, and Query 'PartialReplica' property forces checks of that property which are ordinarily bypassed (as they are extremely time consuming).

The area at the bottom of the form with the disabled controls is only used in the PRO version of the utility.

Licensing

This free version of CompareEM is called Lite. The individual who was emailed the license key for a copy of CompareEM Lite is licensed to use it personally but not to transfer it to others. Those who need a copy of CompareEM Lite should obtain it and a license key directly from http://home.gci.net/~mike-noel/CompareEM.htm.

There are no restrictions on the use of the generated code other than that the notice the code was generated by CompareEM should be retained.

Password

CompareEM uses passwords as a distribution control. The theory is that, especially given the low cost of these products, you would rather persuade your friends and acquaintances to get their own copy than pass out something readily identifiable as yours.

When you register for CompareEM Lite your email address is encrypted and emailed to you as a license key. You copy the key in the email and paste it into the Preferences field called Paste License Key (emailed) here. Subsequently, every time you compare datebases you will be asked to enter your password, which is your unencrypted email address.

Other Versions of CompareEM

A version called CompareEM PRO is available for those who need SQL code generation in addition to the DAO code generated by Lite. The PRO version also needs you to type your password only at installation, not every time you compare datebases. For the price of a couple of Mochas you can't go wrong!

For more information on these products see http://home.gci.net/~mike‑noel/CompareEM.htm..

Limitations

There are a couple of situations where CompareEM might not do exactly what you want.

The most significant is when an object is renamed. CompareEM sees this as a drop and a create. So as a result of a field being renamed CompareEM will generate code to drop the column (and any data in it!) and add a new (empty!) column. Similarly if you rename a table the generated code will drop the entire table (and all data) and the add a new (empty) table. The best way around these problems is to handle them manually. Look at the generated code. When you see the drop/create ask yourself "was this because I renamed something?", and if so either delete the generated code to do the drop/create and replace it with something to really do a rename - or establish your own process to backup and restore the column or table. More fundamentally, avoid renaming things...

Access's Jet database support for SQL is limited. You can't use SQL to do all the things DAO can do. For this reason SQL code gen in the PRO version will not fully replicate a Jet Database. In particular, many field attributes (caption, description, format, ...) can only be set with DAO. Also Jet SQL only supports RI enforced relationships. You should always use DAO code gen unless your target database is something other than Jet.

Support

Finally, CompareEM is fairly new. There are probably other things it should do that it doesn't (yet). And of course there are bound to be some bugs left over. Please send me an email to let me know about any problems or shortcomings you find and I'll try to get them addressed.

Mike Noel


Copyright 2005, 2006, 2007, Mike Noel, All Rights Reserved Worldwide. Last modified 8/22/2007.