?
Solved

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

Posted on 2016-07-16
3
Medium Priority
?
181 Views
Last Modified: 2016-07-17
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
0
Comment
Question by:darls15
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 40

Assisted Solution

by:als315
als315 earned 1000 total points
ID: 41715014
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
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 41715025
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...
0
 

Author Comment

by:darls15
ID: 41716112
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 :)
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question