Overwrite Access data from an Excel Spreadsheet
Posted on 2014-04-08
We have an Access DB that houses imported data from a 3rd party vendor. Unfortunately, some of the 3rd party data is incorrect. Users have the correct data on an Excel spreadsheet.
What we need is a method of overwriting the Access data with the data from the Excel spreadsheet.
The column headings are identical in the Access table and Excel spreadsheet.
We can't simply truncate and load the spreadsheet data because it's only some of the data that needs to be updated.
Walking through the scenario, the script or macro would have to compare the key field from the Excel file and match it with the key field in the Access DB. If it finds it, overwrite the row data with the row data from Excel.
It sounds simple but the only thing I've been able to find on Google is how to just import data and overwrite an entire Access table. Not just specific rows of data.