Query to identify key violation error when import from Excel. Pleasehelp..

[Lee]

Hi

Is it possible to identify which records are causing the key violations
during an import of an excel spreadsheet by comparing the original
records to a temporary import table and then deleting those records in
the original table which have a matching primary key in the import
table. ( and then deletes the records in the temp import table as well!)

I am trying to import data from Excel which contains the following
fields: Division, CostCentre, Period & Value

Division, CostCentre and Period are all text fields and I have also
defined these 3 fields as the primary key for the table. This is so that
I can ensure the Value for each CostCentre in each Period is unique.

If the values for a specific period are updated for whatever reason, how
can I ensure that any record that causes a key violation is deleted and
replaced with the record from the most recent import. I do not want to
link the spreadsheet data.

Thank you

Lee

I am also eventually going to put the import vb code into a 'button' to
automate so any help or pointers you can give I'd appreciate

Thanks again

 

[Jeff Boyce]

Lee

Another approach would be to update existing records, rather than delete
them and re-append.

You could:

join your permanent table and your import data table on the key fields
where there's a match, use the import data table Value to update the permanent table field
create another query that appends, but only those records that DON'T

match

 

[Lee]

Thanks Jeff

I'll give that at a try (sounds much more sensible)
Lee