Compare Access record updates with Spreadsheet Compare

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

Spreadsheet Compare can help you find record-level differences between two versions of an Access database. First, export the table or query results that you want to compare from each database to an Excel workbook. Then, use Spreadsheet Compare to compare the workbooks and highlight changed values and added or removed records.

This method compares the exported data, not the databases themselves. Other comparison methods might be more appropriate if you need to compare database objects, design changes, relationships, or properties.

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, select 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. Select 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.