If you need to compare the data contained within two databases, please see my article on how to use the Excel Spreadsheet Compare tool. If you want to compare differences in structure of two databases, you’re in the right place.
We’re going to set up two sample databases using the Desktop Asset Tracking sample database available in the Microsoft templates gallery.
- Start Microsoft Access 2013 and do a search for desktop asset tracking.
- Give this database a name and click Create (Figure 1).
- This database is a good example in that it makes use of tables, forms, queries and reports. Save your newly created database and click File -> New.
- Select Desktop Asset Tracking again and call this database something different.
We now have two databases that are identical. We’ll make some structural modifications to one of the databases and then fire up the comparison tool to see how it works.
- Make sure your second database is open. Dismiss the Getting Started with Assets window that pops up.
- Left click the Navigation Pane so you can see all of the structural elements of the database.
- If you see a yellow warning that some active content has been disabled, click the Enable content button (Figure 2).
- Right click on any open tabs (such as the Asset List form) and close them.
- Right click on the Assets table and select Design View.
- Make a few changes and save the table. I changed the name of one of the field names, added a new field and changed an existing data type.
- Next, let’s make some changes to the Assets Extended query. Right click on Assets Extended and select Design View.
- For this query I added the “Condition of item" field and selected to sort Ascending. Save the query.
- Next, right click on the All Assets report and select Design View.
- Left click the Location field and delete it. Save the report.
- Right click on the AutoExec Macro and delete it.
That should be enough changes for now. Save the database and we’ll start the comparison.
Start up the Database Compare tool by navigating to Start -> Microsoft Office 2013 -> Office 2013 Tools -> Database Compare. On Windows 8, navigate to the Start screen and click Database Compare.
- The Database Compare window will open. Click the “…" button to select each of the databases.
- Select the report options you wish to see (be sure to select the Reports option) (Figure 3).
- Click Compare. Note – if you receive an Unhandled Exception related to Microsoft.ReportViewer.WinForms you may need to download the Microsoft Reports Viewer Redistributable version 2008 first.
The results will pop up in the Microsoft Reports viewer. You can navigate between pages and view the changes made by type of object changed. Up first was our table changes in the Assets table. The Compare tool noticed I added a column and made changes to a few others, although there is a fair amount of data to weed through as even modification date changes are noted (Figure 4).
Moving on to the second page, the Database Compare tool also noted the changes we made to the Assets Extended Query and AutoExec Macro. Again, some extra data is popping in where other objects were modified as a result of our changes (see Module object – Figure 5).
The last page relates to the changes we made in the All Assets Report. As you can see in Figure 6, the Database Compare tool wasn’t able to note the exact change we made, but it did know that something changed.
Although the Database Compare tool knew about all of the changes we made, some were very specific (table, query and macro) while for some, such as Reports, the tool knew a change was made but wasn’t able to detect exactly what changed.
In either case, if you have the need to compare two databases, you can’t beat the price of the Database Compare tool versus having to do a manual comparison.