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

  • Thread starter Thread starter Lee
  • Start date Start date
L

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
 
Why not import to a temp table and then compare to see if
it is duplicate or error. Edit based on what you find.
 
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.
 
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
 
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
Click to expand...
match
 
Back
Top