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.
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:
- ' go by steps from version 1.0 to 1.3
- If prefv = "1.0" Then DoStructureUpgrade10
- If prefv = "1.1" Then DoStructureUpgrade11
- If prefv = "1.2" Then DoStructureUpgrade12
- prefv = "1.3"
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.
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...
Simple as 1, 2, 3... From the Main Switchboard
Essentially the same as the above picture...
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.
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://www.kicksfortso.com/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.
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.
A version called CompareEM PRO was available for those who needed SQL code generation in addition to the DAO code generated by Lite. The PRO version also needed you to type your password only at installation, not every time you compare datebases. For the price of a couple of Mochas you couldn't go wrong!
For more information on these products see www.kicksfortso.com/CompareEM-LITE/CompareEM.htm.
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.
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