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
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
Who is Participating?
LVL 143
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...
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.
All Courses
From novice to tech pro — start learning today.
