Updating Specific Fields from Excel to Access

[michaeltchurch]

I consider myself relatively dangerous in regards to VBA, Access and Excel. I have a simple table in Access that feeds a Dashboard and PivotTables in my Excel project. Monthly I receive a new spreadsheet of data that has to be imported into my database in Access. Here lies the problem. Sometimes, the monthly data has just "updated" data for an already existing record in my database. I would like to use VBA or something of the like to analyze my current database for the existing record, and only update the new info for the record. Otherwise, the new monthly data is entered in every month as new records and causing duplicates with different data.

 

[Trevor G]

Welcome to the forum,

Suggestion, place the new spreadsheet into a temp table in Access, then you can run a query to remove duplicates and append the new dataset into your main table. As this can be setup with manual imports and tables and queries you then have a process you can run from either excel to run the queries and import through access macros.

 

[michaeltchurch]

Trevor G-
Thanks for the quick reply. I thought that this might be the only way.........which may take a little bit of messing around to get it right. I thought that there might be a string of VBA code or something that I am missing vice doing the temp table. Many thanks from Hawaii!