Import Excel File into exisiting table... ignoring duplicate records

[Cosmicnoodle]

I am creating a small Access application that will allow me to update my Call handling system's site table.
It uses an mdb file, and I wish to add new sites (from new contracts) to the SCSite table.

I recieve regular updates of new sites from existing customers, so want to create a simple macro that will

1: import (from an excel file) the new sites and add these to the existing SCSite table
2: Check for duplicate records using the Site_Num primary key
3: Merge non duplicate records into existing SCSite table.


Is this possible, and if so - how? VBA? Macro builder? Query?


A Swift response would be great, I have had a look through previous posts - but to no avail!

 

[The_Doc_Man]

Just a thought...

Import your data to a pre-defined temporary table. The fields in the temp table should match the fields you are going to import. PLUS you need one more field, a yes/no field.

Write a query to update the temp table yes/no field to YES if the corresponding info already exists in the primary table.

Then write an append query to append records from the temp table to the main table for all temp table entries still marked NO.

Or if you have a true Primary Key, you could do the import in one step and just ignore the errors, discarding the import errors table that gets created. After all, if a general user sees the error, that could be bad... but if you are the only one to do imports, do you care if you see an error you expected to see anyway?

 

[Pat Hartman]

Here is an example of a query that appends new rows and updates existing ones in a single shot.

http://www.access-programmers.co.uk...d=49061&highlight=append+AND+update+AND+query

 

[Cosmicnoodle]

ok, well I got this to work, but it was so simple that it seems something may not be right....
I simply used the SQL:

INSERT INTO SCSite
SELECT *
FROM tbl_Import;

where tbl_import was my excel spreadsheet template with new sites on.
If there are duplicate values it simply ignores them and tells me that x records were not included

Works fine... but any suggestions on how to improve for future would be appreciated