Interesting issue. You would think it would be easy but I've had trouble getting something simple to work. I am very new to Access so hopefully there is someone out there who can help.


In theory you should be able to do this using an update query and an append query. The append query works good but you need to make sure you only append new records. Not too hard to do. You can use the query wizard to create a query that finds records that don't exist in one table then convert it to an append query.

It's not too difficult to open access, run the query and close access all from within Excel.

Updating records from an external table seems to be more difficult. The issue that I ran into is that, from what I can tell, Access won't run an update query unless there is a primary key IN BOTH TABLES. The problem is with the external table not having a primary key.


As a temporary fix what I've resorted to is deleting all records in the access table then running the append query. Hopefully there is an easier way.