Basic tasks in Database Compare

Let's say you have two Access desktop databases, or maybe two versions of the same database, and you want to compare them to find design changes to queries, forms, or reports. Or maybe you want to find the differences in Visual Basic for Applications (VBA) code modules. If you have Microsoft Office Professional Plus 2013 or 2016, you can use Microsoft Database Compare to run a report on the differences it finds.

Note: You can't use Database Compare with Access web apps.

Open Database Compare

In Windows 7    On the Windows Start menu, under Office 2013 Tools, click Database Compare if you are using Microsoft Office 2013. If you are using Microsoft Office 2016, select Database Compare under Office 2016 Tools.

In Windows 8    On the Start screen, click Database Compare.

In Windows 10    Search for Database Compare and select your installed version.

In addition to Database Compare, you'll also find the companion program for Excel – Microsoft Spreadsheet Compare. It also requires Office Professional Plus 2013. (In Windows 8, type Spreadsheet Compare to find it.)

You can also open the Database Compare tool through Windows Explorer. You'll need to locate the executable file and the location can vary based on various conditions such as if you're using a 32bit or 64bit Windows operating system or if you're using a subscription version of Office 365. Here are some possible locations to try.

If you're using Office 2016, try one of these locations:

• C:\Program Files\Microsoft Office\Office16\DCF\DATABASECOMPARE.EXE

• C:\Program Files (x86)\Microsoft Office\Office16\DCF\DATABASECOMPARE.EXE

If you're using Office 365, try one of these locations:

• C:\Program Files\Microsoft Office\Root\Office16\DCF\DATABASECOMPARE.EXE

• C:\Program Files (x86)\Microsoft Office\root\Office16\DCF\DATABASECOMPARE.EXE

Compare two Access database files

1. Browse to find the database you want to use as the "baseline" (or the earlier version) by clicking the browse button to the right of the Compare box.

   
   

2. Browse to find the database that is "changed" (or the most recent version) by clicking the button to the right of the To box.

3. Under Report Options, choose the database objects or items (Tables, Queries, Macros, Modules, Reports, Forms, or Pages) you want compared by checking the boxes next to them.

4. Under Report values, choose Full to see a more detailed report on the changes, or Brief for a shorter report. The same results will be reported with either option, but the report will be in a more concise format if you choose Brief.

5. At the bottom of the window, click Compare to run the report.

6. After the report is finished, you can, you can click the Results tab to see a new window with a deeper, line by line comparison for the items.

Understand the results

In the example above, Table1's design has changed – the FName and LName fields were renamed to First Name and Last Name, the Office field's data type was changed from Number to Short Text, and the Dept and Hire Date fields were added to the table. Here are some things you'll want to know when you look at the results of a comparison:

• Renamed objects or components of objects (such as a column in a table, or a text box in a form) show up as "added" and the originals show up as "deleted."

• When a property of an object is changed, that property is reported as "changed."

• When there are differences between VBA code modules or Access macros, details of the changes are shown in three columns. The original text or code is shown in the Old Value column, the newer text or code is shown in the New Value column, and general information, such as "Module changed" or "Macro definition changed" is shown in the Change Description column.

• To see a line-by-line comparison of differences in VBA code, click the Results tab, click the module name that contains the code, and click Details, or just double-click the module name. A new window appears, showing you the original code in lines with a yellow background and updated code in lines with a red background. You can also view differences in queries and Access modules this way.

What Database Compare can do

Use Database Compare to find design changes between two Access desktop database files or two versions of the same file. Database Compare alerts you to additions, deletions, or renaming of objects such as tables, queries, forms, reports, and VBA modules. It also finds even the smallest design changes in those objects, such as new, removed, or renamed fields in a table, any kind of change to a query, changes to properties of a form or report, or any modified VBA code.

Database Compare doesn't look for changes in the data stored in tables, but by exporting tables or query results to Excel workbooks, you can compare those two workbooks in Spreadsheet Compare. Learn how to Export Access data to see record updates.

Next steps

If you have "mission critical" Excel workbooks or Access databases in your organization, consider installing Microsoft's spreadsheet and database management tools.  Microsoft Audit and Control Management Server provides powerful change management features for Excel and Access files, and is complemented by Microsoft Discovery and Risk Assessment, which provides inventory and analysis features, all aimed at helping you reduce the risk associated with using tools developed by end users in Excel and Access.

Overview of Database Compare