Export Access data to see record updates (Database Compare)

Applies To
Database Compare 2013 Database Compare 2016 Database Compare 2019 Database Compare 2021

You might want to compare the values in two versions of an Access database. Microsoft Spreadsheet Compare can compare values, but Microsoft Database Compare is designed to find changes in object design, macros, and VBA code, not value changes. You can still use Spreadsheet Compare to find the value changes you're looking for.

Export the Access data from each database file to an Excel workbook. You can export from a table if that's what you need, or from query results. Then run the workbooks through Spreadsheet Compare to see the differences.

Export the Access data to workbooks

  1. In Access, open the database containing the "original" or earlier data, and open either the table or the query results that you want in Datasheet view. The following shows a very simple example.

    Screenshot that shows access datasheet showing original data before updates

  2. Select External Data > Export > Excel. The Export - Excel Spreadsheet wizard opens.

  3. Choose the file name and destination folder for the new Excel file. Then select OK. Select Close to close the wizard.

  4. Close the database.

  5. Open the database that contains the updated or later data, and then open the table or query results just as you did for the original data. In this updated database, "California City" is a new record, and Fresno's high temperature changed.

    Screenshot that shows access datasheet showing updated data with new record and changed value

  6. Run the Export – Excel Spreadsheet wizard in the same way for this database.

Run the comparison in Spreadsheet Compare

  1. In Spreadsheet Compare, click Compare Files.

    Screenshot that shows Compare Files command in Spreadsheet Compare.

  2. Select the browse button next to the Compare box, and then browse to the Excel workbook that contains the original data.

    Screenshot that shows Compare Files command.

  3. Select the browse button next to the To box, and then browse to the workbook that contains the updated data.

  4. Click OK to run the comparison.

Understanding the results

Here's what the results show for the simple example, where a record for "California City" was added and Fresno's temperature changed.

Screenshot that shows Spreadsheet Compare results highlighting added and changed records.

In the right pane, which shows data from the updated database, row 4 now contains California City and its temperature. The entire row is highlighted in green because it was added to the Access table. Fresno's temperature in row 5, column B, is also highlighted in green because it changed from 107 to 111.