Key violation error when import from Excel. Please help..

[Lee]

Hi

Is it possible to overwrite or delete the original data which is causing
the key violations in the import.

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 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

 

[Guest]

Why not import to a temp table and then compare to see if
it is duplicate or error. Edit based on what you find.

 

[John Nurick]

Hi Lee,

Import or link to a temporary table. Then use two queries: an update
query to update the records that already exist (i.e. are causing the key
violations) and an append query to add the new records.

 

[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