Import only new records from an Excel file into an existing table

Hi Experts

I'm needing to import a number of Excel files (separately) into existing tables in my database using VBA.

I can successfully do this with the DoCmd.TransferSpreadsheet however, I only want to add new records in each table.

As an example, the table "AdvParticipants" has a unique "userid" field and if the value in the column "userid" of the spreadsheet already exists in the field "userid" of this table, then skip it, otherwise import the record. I only need data to the last row and column in the spreadsheet imported.

And if possible, at the time of import, I would like to add 2 other fields which do not exist in the spreadsheet - [ImportDate] = "Now()" and Process Desc] = "Round1".

Any help would be appreciated.

Thanks
darls15

darls15

Asked: 2016-07-16

Advertise Here

Microsoft Access

2 Solutions

LVL 41

als315Commented: 2016-07-16

You can link Excel files instead of direct import and import data with query, where you can compare file with existing records and modify any field

LVL 143

Guy Hengel [angelIII / a3]Billing EngineerCommented: 2016-07-16

alternatively, i would suggest to load the excel sheet into Staging Tables, validate the data there, and then load the data to the final tables. easier in the end...

darls15Author Commented: 2016-07-17

Hi, thank you both for your suggestions. Using staging tables seem to be the way to go for my database. I'll award equal points for both these suggestions as they are both workable solutions. Thanks again :)

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Expand your skills with this month's free Premium course.

Import only new records from an Excel file into an existing table

Join & Write a Comment Already a member? Login.

Featured Post