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 Microsoft 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 the Access 2013 Database Compare Tool

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.

1. Start Microsoft Access 2013 and do a search for desktop asset tracking.
2. Give this database a name and click Create (Figure 1).
3. 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.
4. 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.

1. Make sure your second database is open. Dismiss the Getting Started with Assets window that pops up.
2. Left click the Navigation Pane so you can see all of the structural elements of the database.
3. If you see a yellow warning that some active content has been disabled, click the Enable content button (Figure 2).
4. Right click on any open tabs (such as the Asset List form) and close them.
5. Right click on the Assets table and select DesignView.
6. 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.
7. Next, let’s make some changes to the Assets Extended query. Right click on Assets Extended and select Design View.
8. For this query I added the “Condition of item” field and selected to sort Ascending. Save the query.
9. Next, right click on the All Assets report and select Design View.
10. Left click the Location field and delete it. Save the report.
11. 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.

Comparing Databases

1. The Database Compare window will open. Click the “…” button to select each of the databases.
2. Select the report options you wish to see (be sure to select the Reports option) (Figure 3).
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).

Images

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.

Figure 6 Report Results

При подготовке материала использовались источники:
https://support.microsoft.com/en-us/topic/basic-tasks-in-database-compare-bbb1574b-99ea-43f5-88a1-a02ff54cdcdb
https://www.brighthub.com/computing/windows-platform/articles/128516/