Find differences between Microsoft Access databases and objects with Total Access Detective

Microsoft Access Object and Database Comparisons

Detect differences between Microsoft Access databases, object designs and data with Total Access Detective

Total Access Detective Keeps Up with Changes

This awesome development tool lets you track differences between database versions

By Thomas Wagner
Access Advisor, January 1997
Copyright © Advisor Publications. All rights reserved.

FMS calls its Total Access Detective the "Database and Object Comparison Wizard," which is really an inadequate description for this valuable add-in tool for Microsoft Access. It would be more to the point if the description read "Special tool to get you out of tight spots in a hurry." Total Access Detective is a special piece of software that can help Access programmers be more productive.

One of my clients is helping to manufacture numerous components for the International Space Station Alpha. The primary project I'm working on is an Inventory and Traceability database system that's used by several sites in the U.S. and Canada. As we change the code to fit new business requirement, in addition to the occasional modification of a table, it becomes imperative that all sites use the same code and table structures. It's in this deadline-oriented situation that I've come to appreciate Total Access Detective.

By now you may be asking what in the world do they mean by Database and Object Comparison Wizard? The answer, in short, is that this add-in compares objects and data in one or more .MDB files. Object comparisons include modules, tables, forms, macros, reports - any object you like.

When was the last time you needed to review changes in queries or tables from one version of your database to another? Or even better, when was the last time you had to quickly review the changes made to various subroutines and functions spread across a number of modules?

Most of us use some sort of version control to manage the front end of an application. That's typically not a difficult undertaking. However, what do you do when you're faced with a set of back-end files that are several "versions" old? This is presently my favorite use for Total Access Detective, especially since I'm working with Access 2.0. Rather than pouring over the output of the database documentation wizard, I simply run both files through the Total Access Detective. It provides more information than I would ever retrieve in a manual process, and it does so at a great rate of speed.

Have you ever worked on a project where things got a little out of hand and you were faced with multiple programmers, simultaneously making multiple changes to the code and database design? One may be working in forms, another in reports, and the best of the bunch is busy changing several modules to fit with the latest user requests. How do you quickly find the differences in these modules and forms? Use Total Access Detective. Table 1 shows a few of the differences that can be detected between two .MDB files.

Table 1: Comparing Apples to Apples - Total Access Detective can detect the differences listed with each object type.

Object Type	Differences Detected
Tables	All field properties: name, data type, validation rule, etc. Fields in one table that aren't in another. Indexes, index fields, sort order, ignore blanks, etc. Data keyed tables with identical fields. New, modified, and deleted data. For modified records, fields that differ.
Queries	Query type, fields, parameters, and other properties.
Forms	Form properties, controls and control properties, sections and section properties. Properties of identically named controls. Controls in one form, but not in another.
Reports	Same as forms.
Macros	Command macros, including arguments; uses an algorithm to detect multiple
Modules	Module lines. Procedures in one module, but not in the other. Like macros, uses an "intelligent" algorithm to determine changes and presents only changed blocks of code.
Permissions	Security permissions.
Relationships	Table relationships, including linkages, linked fields, referential integrity settings, and relationship types.

Total Access Detective has two primary modes of operation. One mode compares objects within the same database file, or within two separate .MDB files. Some of the finer points in running comparison include such items as:

An optional selection that lets you include or exclude data comparison of tables.
An optional selection to compare form modules (code behind forms or CBF) and report modules.

When comparing modules or CBF, Total Access Detective lets the user adjust the number of lines to resynchronize. When a difference is detected in a module, Total Access Detective shows the new lines of code, plus the line where the code is resynchronized between two modules. You're able to fine tune which changes are detected and shown.

In addition to synchronizing module or macro lines, you can ignore or include indentation in the comparison. If you want to know every possible difference between two modules, turn this option off. Even differences in spacing or tabs will be flagged.

While Total Access Detective can report on security settings, keep in mind that you need to have all rights to the objects your interested in. Ideally, you'd want to be the owner of those objects, or have Admin privileges, or be a member of the Admins group. If you don't have permissions on certain objects, you won't be able to run Total Access Detective against those objects.

It's possible to create queries based on the output tables generated by Total Access Detective, which lets you manipulate the data in the files that were documented.

When comparing two separate Access files, you must run a preparatory step before Total Access Detective compares modules between both files. This preparation is needed, since Access doesn't allow a wizard or add-in to retrieve modules that aren't located in the current database. In addition, because of an undocumented feature in Access (a bug, really), you might have to open the database exclusively for this preparatory step. When comparing two databases, Total Access Detective creates a separate (preparatory) file with the extension .DDB.

The following illustrations show the output generated by the Detective in a sample session. I've added several fields to tables in the Access sample application NWIND.MDB, as well as changed one procedure very slightly and added an AutoExec macro. The purpose of this example is purely illustrative; if this was a production database, you may easily have pages of information to view. If that happens, Total Access Detective provides provides filters for each and every property that was found to be different, letting you concentrate on just the change you want to deal with.

Having selected two database fields for comparison, Total Access Detective shows the user a list of objects contained in each file:

Start comparing Microsoft Access database objects
Figure 1: Getting Started - The Total Access Detective switchboard reflects the FMS attention to detail.

Selecting Microsoft Access Databases for Comparison
Figure 2: Selecting Databases - The Browse buttons take you where you need to go.

Select Microsoft Access Objects to Compare for Differences
Figure 3: Comparison Options - Total Access Detective gives you a lot of comparison options.

At this point, you can see differences in the object inventories of the database files. By selecting the Show Unmatched button, the user is presented a list of differences:

Microsoft Access Objects in One Database but not the Other
Figure 4: Comparison Under Way - Total Access Detective easily detects objects in one database, but not in the other.

This quick overview doesn't show detailed differences between objects having the same name. To obtain that information, Total Access Detective must run an analysis that compares each object in Database 1 to the object of the same name in Database 2. The result of this analysis is shown:

Microsoft Access Object Differences at the Property and Control level
Figure 5: Details! Details! - Total Access Detective easily detects objects in one database, but not the other

In addition, you can filter the results in a variety of ways:

Microsoft Access Object Difference Filter Options
Figure 6: Filter Options - More filtering options to further refine the output.

One of the most useful features of Total Access Detective in a multi-programmer environment is the ability to pinpoint code changes. As part of the example session, I've added a statement to one of the utility functions in NWIND.MDB. As you can see, Total Access Detective had no trouble picking up the change:

Microsoft Access Module Code Change Differences
Figure 7: Code Changes - Total Access Detective reveals changes in code modules.

Even more impressive, you can set the sensitivity of module comparison by un-checking the "Ignore Indents" setting. When you do this, even changes in indentation are picked up as differences.

At the beginning of this review, I mentioned that the basic purpose of Total Access Detective is to perform comparisons between objects in one or more database files.

Microsoft Access Table Design Changes
Figure 8: Table Changes - Sample output showing differences in table objects.

Figure 8 shows the output generated after the Customer table was copied to Customers_Old and several fields in the original Customers table were deleted.

When running comparison between objects within the same file, the Detective is activated just like any other Wizard from within the Add-ins men.

Conclusion

Similar to other products by FMS, Inc., Total Access Detective fills a definite need. It isn't quite a full featured version control program, yet it's much better suited to the task of change control than the Access documentation wizard. it's fast, efficient, and at the risk of sounding corny, "a very cool tool." I highly recommend Total Access Detective, particularly if your work involves making many changes over a period of time.

A consultant and author in Los Angeles, Thomas Wagner's clients include Walt Disney Studios, First Interstate Bank, and AlliedSignal Aerospace. He's co-author of The Visual Guide to Microsoft Access for Windows 95 (Ventana Press).

Other Total Access Detective Reviews and Awards