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.
   

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.
   

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.
   

2. Browse to the Excel workbook containing the original data by clicking next to the Compare box.
   

3. Browse to the workbook containing the updated data by clicking 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.

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.