Comparison Of Access Files

BitsqueezerDatabase Developer
CERTIFIED EXPERT
Published:
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.

Comparison Of Access Files


CCAccessComparison.zip
 

Table of Contents


1 About CCAccessComparison
1.1 Features of CCAccessComparison
2 How to use
2.1 One time preparation
2.1.1 Entering the path to Meld.exe
2.1.2 Adjusting VBA References
2.2 Entering the needed information
2.3 Additional Information
2.3.1 HTML Message Box
2.3.2 clsCCADO
2.3.3 clsCCErrors
2.3.4 modRenameObjects
2.3.5 Comparing ADPs
 

1 About CCAccessComparison


If you are developing a database (frontend, backend or both in one) with Access you know that it’s not so easy to keep the overview of all the file versions you have saved as copy of your work. Maybe you want to get into an older project and forgot what were the changes between the current version and the last one or someone else has programmed something into your frontend and you want to know what has been changed.

Unfortunately Access has no possibility to compare two Access files to see all the differences. You can of course find commercial tools to compare or freeware tools to compare two text files or a directory of text files, but they cannot look into the code of an Access file.

So if you want to get a quick overview of all the changes between two Access files you need to have all objects of an Access file as text files on your harddisk. This is what this tool primarily does: It exports all objects of an Access file as plain text files and then starts a tool which is able to compare two directories of text files and shows the differences.
After a long search I found the tool "Meld" which does exactly this, it’s free to use. So to use the CCAccessComparison tool you need to download and install "Meld". It was developed using version 3.14.2 and should also work with newer versions. You can get it here.


1.1 Features of CCAccessComparison

 

  • Compares two Access files (MDB/ACCDB/ADP, as long as it can be opened with your version of Access)
  • Outputs all objects (except table data) as text files in separated folders
  • Saves folder and file paths in a project table to make it possible to reuse former file selections
  • Automatically starts "Meld" and starts the comparison in Meld using the output folders of the compared Access files


2 How to use



2.1 One time preparation



2.1.1 Entering the path to Meld.exe.


Open the module "modCompare" in the VBA editor. At the top you’ll find a constant definition with the path to Meld.exe. Enter the path where you’ve installed it in the same way as you see in the example:
Public Const cMeldPath As String = """C:\Program Files (x86)\Tools\Meld\Meld.exe"""

Open in new window


Please make sure that the double quotes remains in the constant. The tool works without Meld, in that case you’ll get only the text output files and an error occurs when it tries to start Meld. You can use any other tool you want to compare the text files.


2.1.2 Adjusting VBA References


The tool was written using Access 2007 and converted to MDB for use with Access 2003. VBA often automatically converts references to the newest installed Office version so to use it you must change the references in VBA ("Tools" - "References") to the version you use on your machine. For example there is a reference to ADO 6.0 contained that works starting with Windows Vista, but if you use Windows XP you must change that to ADO 2.8. The same with Office which is Office 15 here; you must change it to the Office library you use on your machine. Here are the references in the ACCDB version:
References.pngAnd these are the references after converting the tool to MDB:
ReferencesMDB.pngIf you’ve adjusted the references, start "Debug" - "Compile" to make sure the code is compiled and all references are correct. The tool is now ready to use.


2.2 Entering the needed information


When you start the tool the "Projects" form will be started automatically. You can now enter a project name (needed) and optionally a version number. This makes it easier to find the right project later.
ProjectForm.pngNext you need to enter the output path (all paths can be selected using the buttons at the right of the input field). In this path the tool will create two folders for the two Access files using the file names of the Access files. Inside of these paths several additional folders will be created, one for each object type. Also a file containing the VBA references will be created.

Then you only need to select the two desired Access files you want to compare and optionally enter a description. The "Last Compare Date" will automatically be set when you start the comparison.

At the end you only need to save the record and click "Compare Files". Now the tool starts with opening the first Access file and exporting all objects as text files (in case of queries the SQL text will be saved additionally in a separate file). Next the same is done with the second Access file in the other output folder. At the end a compare batch file will be created (because the "Shell" command of VBA cannot handle very long strings) which then will be executed. Here you can of course configure your own compare tool if you want. I found that Meld is one of the greatest as it can handle Unicode characters and also compares folder structures and file contents, also the way it displays differences is really a new idea where the two comparison columns scrolls independently from each other and so on.


2.3 Additional Information


For the export the not officially documented "SaveAsText" method is used which can export all object types (except tables) as a plain text file. You can also use the pendant "LoadFromText" to load such text files back into an Access file. As this doesn’t work with tables the tool uses DAO to export all property information about the tables. Additionally all index information and also all relations to other tables are included in the output file. As this uses the property names these output files could theoretically also be used to recreate the table structures in a new Access file.

The tool contains some add-ons which you also may find helpful and which you can implement in your own projects if you want.


2.3.1 HTML Message Box


The tool contains my HTML message box which was developed to completely replace the MsgBox command of VBA. With this you can display any message in the same way as the MsgBox command does, the parameters are the same as in MsgBox, but there are a lot more.

If you supply a HTML formatted text it will be displayed using the Internet Explorer of your system on which the web control is based on. The additional parameters also allows to display the result of a SQL command or the content of an ADO recordset (it was developed for ADPs so sorry, no DAO recordset supported but you can also open Access files with ADO) as a HTML formatted table which also supports conditional formatting in the HTML table and so on. It should not hard to find out how it works if you look into the documented code.

Without using the additional features the HTML message box can simply be used with any of your "Msgbox" command lines by simply adding a "." Between "Msg" and "Box":
Msg.Box "Test"

Open in new window


The code also contains disabled code blocks for exporting the message to Excel which you can enable if you’ve Excel installed. The tool uses the HTML message box for displaying error messages.


2.3.2 clsCCADO


The tool needs that only because the HTML message box needs some functions of it. This is a library of useful functions if you work often with ADO which I’ve developed over years. The functions are documented in the code.


2.3.3 clsCCErrors


This is a class to handle the errors which uses the above classes. It supports writing VBA errors into an HTML formatted error log saved locally in an "Errors" subfolder which is automatically created in the project’s folder. It prepares the HTML message box output for VBA errors and additionally shows errors which maybe happened in an ADO object. Same as above: It was developed for ADPs so DAO is not used here.


2.3.4 modRenameObjects


This contains two little procedures which I often use to prepare a form after Access has automatically created it from a table or query. One is to correct the naming of all controls which contains a ControlSource and their corresponding label control, the other sets a defined format for continuous forms. I left that tools in the database, maybe you also find it useful for your project. It is not actively used in the tool and can be removed.


2.3.5 Comparing ADPs


If you want to compare ADP files or older MDB files you need to have an Access version installed which is able to open them. So if you want to compare ADP files the last version which supports that is Access 2010. If you have Access 2013 or newer the tool displays error messages as Access 2013 cannot open ADPs anymore. The same is true for older MDB files. So to successfully compare such files you need to run the tool on a computer which has an older Access version installed. Access 2013 must not be installed there because the tool automatically uses the newest installed Access version to open the Access files.

Have fun in comparing your Access files and thanks to Kai Willadsen for the great diff tool "Meld".

Christian Coppes
 
1
2,676 Views
BitsqueezerDatabase Developer
CERTIFIED EXPERT

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.