Best Way to Synchronize Multiple Excel Files to MS Access Table

Question

We have a number of Excel-based membership rosters for a non-profit organization and we need to merge these into one master MS Access table. From time to time, we receive updated copies of the Excel-based membership rosters and I need to synchronize these with the master MS Access table.

I'm looking for the optimal way to accomplish this. Since I am not a huge fan of Access VBA, I was thinking of writing a C# console app,leveraging Marcos Meli's FileHelpers library to communicate between Excel and Access. Do you have any recommendations for either a good approach for building myself or something off-the-shelf that could be used to accomplish this objective?

Answer 1

You haven't said how big the membership lists are.

If they aren't too massive, and if you aren't doing anything too complex, then simply linking the Excel files to the Access database would allow you to replace the Excel files with new ones (with the same names) and then the database would use the new data automatically and you don't need any updating software at all.

I'm also thinking that a non-profit wouldn't have much IT budget, so the simpler the better.

Answer 2

Typically, what I've done in the past was import the excel file into a staging table, and then pruned out duplicated, and then copied the resulting list into the master table.

You might have to cull ex-members from the your master table, in which case, maybe it's easier to delete all the members that came from member organisation and reload the entire list. But then you have to record which organisation they came from.