Export Access data to see record updates (Database Compare)

You want to see the changes in values between two versions of an Access database, but how can you do this? You've seen Microsoft Spreadsheet Compare, and you know it can compare values. Maybe you've used Microsoft Database Compare – if you have, you know it's designed to find changes in object design, macros, and VBA code, not value changes. There is a way, however, to use Spreadsheet Compare to spot the changes you're looking for.

By exporting your Access data in both database files – either from a table, if it's exactly what you need, or from query results – to an Excel workbook, you can then run your 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.
    Original data in Access table
  2. Click External Data > Export > Excel. The Export – Excel Spreadsheet wizard starts.
  3. Choose the name and destination folder for your new Excel file, and click OK. Then, click Close to close the wizard.
  4. Close the database.
  5. Open the database that contains the "updated" or later data, and open the table or query results like you did for the original data. In this updated database, "California City" is a new record, and Fresno's high temperature was changed.
    Updated data in Access table
  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.
    Compare Files
  2. Browse to the Excel workbook containing the original data by clicking Browse next to the Compare box.
    Compare Files command
  3. Browse to the workbook containing the updated data by clicking Browse next to the To box.
  4. Click OK to run the comparison.

Understanding the results

We'll zoom in on the results of our simple example, where a record for "California City" was added, and Fresno's temperature changed.

Results of the comparison from exported Access data

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

 
 
Applies to:
Database Compare 2013, Office 365 Enterprise, Office 365 Enterprise admin, Office 365 Midsize Business, Office 365 Midsize Business admin