Total Access Detective Product Guide

Total Access Detective is the world's leading product for Microsoft Access for knowing exactly what changed between databases and objects. Originally introduced in 1995 for Access 2.0, Total Access Detective is in its ninth major release incorporating more features than ever with support for Microsoft Access 2016.

Total Access Detective is an object comparison wizard. Use it to determine exactly what's different between two objects in one database or identically named objects in two databases.

This is particularly useful when you need to compare two different versions of your database, or databases in multi-developer environments. Every field, control, property, macro line, line of module, code, etc. is compared and differences are shown. There are even options to compare data in tables, permission settings, relationships, and library references.

As the most popular database program on the desktop, Microsoft Access databases are used for interactive analysis and a wide variety of sophisticated applications. Over time and as more people or locations are involved, situations arise where it is imperative to know exactly what's different between any two databases or any two objects within a database.

This can include the need to understand what changed between versions, what's different between user and developer copies, changes made by multiple developers, or even the same developer's home/laptop version vs. "official" version.

The Problem

The large number of things that can change make it extremely difficult to manually determine exactly what changed. Modifications include adding and deleting objects, changing macro and module code, modifying the layout and properties of forms and reports, updating queries, and changing table structure and data.

By not knowing exactly what changed, it is very easy to overwrite another user or developer's changes, undo bug fixes, lose data, or cause enough damage to make an application crash.

The Total Access Detective Solution

Total Access Detective addresses these issues with a comprehensive, automated tool to reveal all the differences between:

• Any two databases
• Any two objects in one database

Designed specifically for Microsoft Access, Total Access Detective works directly within Access. To compare two objects in one database, simply open the database and launch Total Access Detective as an Access add-in. To compare two databases, open the Total Access Detective database and point to your two databases. A wizard interface makes it easy to choose the objects and type of comparison to perform.

The results reveal every single difference between the selected objects including properties, controls, code, settings, fields, and even data. Results can be viewed or printed. Quickly and easily understand what changed so you can resolve the differences.

Two time winner of the Access Advisor Reader's Choice Award for "Best Debugging Tool and Testing Tool," Total Access Detective is an important utility for every serious Access user or developer. By knowing the differences, fixes can be quickly implemented, and costly (and painful) overwrites of existing work can be avoided.

Here are what some leading Access experts and gurus have to say about Total Access Detective:

"Total Access Detective is well worth every penny, it will quickly pay for itself through savings in time and effort."
Tom Cryan, Denver Access User Group product review

"This awesome development tool lets you track differences between database versions…I highly recommend Total Access Detective, particularly if your work involves making changes over a period of time."
Thomas Wagner, Access Advisor product review

"This add-in will save you many hours of difficulty when managing any Access application with multiple developers"
Michael Gunderloy, Smart Access product review

Total Access Detective detects differences between any two objects in one database or differences between two databases.

Two Object Comparison Overview

Compares any two objects in the same Access database. Simply open your database with the objects to compare, and launch Total Access Detective from the Add-ins menu.

• Compare any two tables, queries, forms, reports, macros, modules, import/export specifications, and table relationships in an Access MDB or ACCDB database, or an Access Data Project (ADP).
• Combine data from two tables into a new third table in your database
• Perform a line-by-line comparison on any two blocks of text.

Two Database Comparison Overview

Compare two Microsoft Access databases. Open the Total Access Detective database from the Windows Start menu, and select the two databases to compare.

• Quick analysis is performed to determine objects that exist in one database but not the other (ideal for flagging new or deleted objects).
• A list of identically named objects in both databases is presented to be selected for detailed analysis.
• Detailed analysis is performed on the selected objects, and the results are presented in forms to view on screen or export into a table in your database.
• A wide range of professionally formatted reports are available for detail and summary information.
• Combine data from two tables into a new table into your database.
• Options are available to compare database level information (including security permissions, table relationships, library references, import/export specifications, and database properties).

Object Differences Detected

Extremely detailed analysis is performed to compare a selected pair of objects. The examination is based on the type of object compared:

Tables

• Structural differences, including new, renamed, or deleted fields
• Table level properties including linked table settings, description, etc.
• Changes to field properties, including data type and size, description, validation rules, default values, input masks, etc.
• Index names, fields, and properties
• New, modified, or deleted records (for un-keyed tables, the first differing record is listed)
• Combine data from two tables into a new table

For more information, visit:

• Table, Field, Index, and Property Differences
• Compare Records (data) Between Access Tables and Queries
• Combine Data from Two Access Tables into a New Table

Queries

• Modified SQL strings, query type, parameters, and other properties
• Query fields and field properties

For more information, visit Finding Query Differences

Forms

• Form level properties, including record source, filters, rights, views
• New, modified, and deleted controls
• Properties of identically named controls and sections
• Embedded macros
• Module code behind the forms

For more information, visit: Finding Form Differences including Code Behind Forms

Reports

• Report level properties, including record source, filters, caption
• Sections and groupings
• New, modified, and deleted controls
• Properties of identically named controls, sections, and groupings
• Embedded macros
• Module code behind the reports

For more information, visit: Finding Report Differences including their Modules

Data Access Pages

• HTML and data source (for MS Access 2003 and earlier versions)

Macros

• Sub-macros (named macros) in one macro but not the other
• Line-by-line comparison of command lines including arguments, conditions, and comments
• Identically named sub-macros:
  Macro comparison is performed using an intelligent comparison algorithm that detects added or deleted blocks of lines, so it doesn't just flag every subsequent line after it encounters the first difference.

For more information, visit: Finding Macro Differences

Modules

• Procedures in one module but not the other
• Optionally ignore indentations and/or case
• Line-by-line comparison for identically named procedures:
  Module comparison is performed using an intelligent comparison algorithm that detects added or deleted blocks of lines. Only the block of code that differs is flagged to make it easy to see what changed.

For more information, visit: Finding VBA Module and Procedure Differences

Command Bars

• Controls on one command bar but not the other
• Differences in actions between identically named controls

Common Uses for Total Access Detective

Here are some common situations where Total Access Detective is particularly useful:

• Multiple developer environments where merging changes to the same database can overwrite someone's work.
• Deployed applications, where you need to know what an end-user "enhanced" in his/her database. This can include new queries and reports, and changes to data and lookup tables.
• End-users and power-users who want to know what changed between the current database and an older (backup) database.
• Modifications made to a database on your laptop (or a copy at home) that need to be applied to the "master" copy in the office.
• Documentation of all the changes between versions of a database.
• Documentation of all the changes in a database over a period of time ("this is the work I did over the past week").
• Examining all differences between a pair of objects (for example, to determine if reports or macros that are basically the same and could be merged into one).
• Text comparison to verify identical code or blocks of text. This is especially useful for comparing code across different databases, database versions, or other VB/VBA hosts.

Supporting Different Versions of Microsoft Access

Due to the different features and properties in each version of Access, Total Access Detective offers a different version for each version of Access. Each version supports every database format supported by the Access version.

For example, Total Access Detective 2010 supports Access 2010 databases including ACCDB database format (from MS Access 2007 and 2010) and the MDB formats from MS Access 2000, Access 2002-2003, and later. It also compares Access Data Projects (ADPs) created in any Access version.

Because support for ADPs was discontinued in Access 2013, Total Access Detective 2013 and 2016 support the database formats its version of Access supports without support for ADPs.

If you have an earlier version of Total Access Detective, see the list of enhancements:

• For Access 2016 and 2013: Total Access Detective 2016 and 2013 (Versions 16.0 and 15.0)
• For Access 2010: Total Access Detective (Version 14.0)
• For Access 2007 and 2003: Total Access Detective (Version 12.8 and 11.8)
• For Access 2002 and 2000: Total Access Detective (Versions 10.7 and 9.7)

A discounted upgrade price is available for existing owners.

From MS Access, open the database with the objects you want to compare, then launch Total Access Detective from the Database Tools, Add-ins menu. The Comparison Wizard appears:

Select the Two Objects to Compare in Your Database

Specify the object type to compare from the drop down list:

Select the two object names, and press the [Run] button to perform the comparison.

Object Comparison Results

The results of the object comparison are shown in a tabular format that can be printed, previewed, or exported to a table in your database.

This form shows the results of comparing two objects. See the differences at the Form level, and controls that exist in one form, but not the other:

Example of Form Comparison Differences

Data Differences

When comparing tables, you can find differences in table data as well. This can be based on identical field names or field order:

Example of Data Differences Between the Records in Your Tables

This form lists every record that is different between the two tables including new or deleted records. For modified records, the fields that differ and their values are shown. The results are also available on a field by field basis showing the pair of differences:

Field Differences

For modified records, a separate list of each field's pair of values is provided on the Modified Fields tab:

For Modified Records, see the Field by Field Differences

Combine Two Tables into a New One

Total Access Detective even allows you to combine the data from the tables that you compared into a new table with a variety of options:

Options for Merging Data from Two Tables

Module Differences

For module and macro comparisons, all code differences are shown. Any procedures that exist in one module but not the other are listed. Identically named procedures are compared to each other line by line. Procedures with no differences are listed under Identical Procedures:

Example of Differences Between the Modules Behind Two Forms in the Same Database

When comparing modules, there are options to control what differences to ignore. The Ignore Comments feature is particularly powerful if you only want to see actual code changes:

For more information, visit: Finding VBA Module and Procedure Differences

Macro Differences

Similar comparisons are performed for macros and embedded macros on forms and reports.

Database Preparation Step

Due to limitations within Access, to compare two ADPs or these objects in an MDB/ACCDB (command bars, library references, and import/export specifications), a preparation step is necessary before comparing databases. To prepare a database, open the database and select Total Access Detective Prepare from the Add-ins menu.

The preparation step is very quick and not necessary if you aren't comparing the object types that require this. 

Database Comparison Wizard

To compare two databases, open the Total Access Detective database from the Windows Start menu:

Main Screen for Comparing Microsoft Access Databases

From the Database Comparison Main Menu screen, select the [Generate] button and choose the databases to compare.

Select the Databases to Compare

Select the two databases then press [Next]:

Specify the Access Databases to Compare

Select the Objects to Compare and Get the List of Unmatched Objects

Total Access Detective retrieves a list of identically named objects for you to select for detailed comparisons:

Selecting Among the Identically Named Objects and Get Unmatched Objects

You can also retrieve a list of objects that exist in one database but not the other under the [Unmatched Objects] button. These are the objects that were added, deleted, or renamed. If that's all you wanted to get, you're done.

Set Comparison Options

After selecting the objects for comparison, press [Next] to specify the comparison options:

Microsoft Access Database Comparison Options

You can choose whether to compare the records in your table, field properties, library references, security permissions, and module code behind forms and reports. There are also options to specify how the data and modules should be compared, plus the properties to compare if you want to exclude some.

Verify Preparation Status

After pressing [Next], a status screen appears to show if the database was properly prepared to compare some of the objects such as references and command bars:

Confirm the Databases are Prepared for the Analysis

Override Output Database Name

After pressing [Next], you can specify where to store the results. By default, the results are always stored in the same output database path and name. If the database already exists, the information on the database comparison in it is shown.

The file has a TDA extension, but it's actually an unprotected Access MDB database that you can open later if you'd like to get to the raw data. Specify another name to retain database comparison results for multiple pairs of database comparisons:

Specify Where the Microsoft Access Database Comparison Results are Stored

Results are Generated

Once you press [Finish], Total Access Detective compares your objects and displays this when completed:

Microsoft Access Database Comparison is Completed

View the results under the [View, Filter & Print] button from the main screen:

View the Microsoft Access Database Comparison Results or Open Previously Generated Results

The results are organized into these categories:

• Object Differences
  Property, field, and control differences for all the selected objects
• Data Differences
  Differences in table data
• Macro Differences
  Differences with macros and sub-macro lines
• Module Differences
  Differences between modules and procedure code
• Unmatched Objects
  Objects in one database, but not the other (new, deleted, or renamed objects).
• Errors
  Objects that could not be opened or analyzed (e.g. locked or corrupt).
• Print
  A variety of reports that can be previewed, printed, or exported:

Select Among Summary and Detailed Reports

Here are examples of all the Access comparison reports

---

Text Comparison

Total Access Detective also includes a Text Comparison feature to find differences between any two blocks of text.

Compare any Two Blocks of Text as VBA/VB6 Module Code

This is similar to comparing two modules in a database, but this feature does not require the text to be in a module. This is particularly helpful if you are comparing two modules that may have name space conflicts if they were stored in the same database, code that doesn't compile, archived modules on disk, code from Visual Basic 6, etc.

You can compare the text as standard words, or choose to treat it as VBA/VB6 code which tells Total Access Detective to parse and compare the text by procedure name.

For more information, visit VBA Text Comparison

Product Packaging

Total Access Detective is available by electronic software download. A physical option is also available with CD, on-line help, and a professionally printed user manual.

Technical Support

FMS has a dedicated, professional technical support staff available via phone and email. Support tickets can be created from our Support Site.

System Requirements

Total Access Detective has the following system requirements:

• Microsoft Access version corresponding to your version of Total Access Detective
• Any operating system and hardware that supports Access
• 10 MB free disk space for installation plus additional space is needed to store the results which vary depending on how many differences there are.

Versions and Pricing

Due to the unique features of each version of Microsoft Access, a different version of Total Access Detective is available for each Access version. Total Access Detective supports all database formats supported by its version of Access (e.g. the Access 2010 version supports all database formats from Access 2000 to 2010 including MDB, ACCDB, and ADPs).

Total Access Detective is also part of the Total Access Developer Suite and Total Access Ultimate Suite.

Availability

FMS products are available directly from FMS or most corporate resellers. FMS also has international dealers in many countries.

Microsoft Access databases are used and enhanced everyday. Ultimately, almost all users and developers need to know what changed between copies or two points in time.

Total Access Detective is the only Access database comparison program on the market today. It has won every industry award for its category since its original release for Access 2.0 in 1995. It's truly a godsend when you really need to know what changed between objects or databases. Each Access object has so many properties and design options that it's virtually impossible to manually view and compare all differences between them.

By trying to reconcile changes without an automated tool, you risk losing the time-consuming changes that users or developers have made. With an easy-to-use, interactive interface, Total Access Detective increases the productivity of Access users, and eliminates the headaches associated with reconciling and understanding changes. We think you'll agree it's a must-have tool for every serious Access user and developer.

Founded in 1986, FMS is a privately-held, Virginia based firm and the world's leading developer of tools for the Microsoft Access community. With tens of thousands of customers in over 100 countries, FMS customers include 90 of the Fortune 100, all US government departments, plus many other organizations and individual developers.

Known for their quality and power, FMS products are winners of numerous industry awards including every "Best Access Add-in" award since 1994. All FMS products are developed by internal staff and undergo a rigorous quality assurance and beta testing process. Our people include world-recognized experts in the Microsoft Access, Visual Basic, and VBA fields, and Microsoft MVP recipients, Microsoft Certified Professionals, book authors, and speakers at industry conferences throughout the US and Europe.

For more information, visit About FMS.

Total Access Detective is a trademark of FMS Inc. All other trademarks are trademarks of their respective owners.