2nd import always fails

[Turncloud]

I am receiving some Excel data that I need to import in to an Access 2013 database on a weekly basis.



The spreadsheet is really simple, just 3 columns

Reference, Total Qty Shipped, Total Value Shipped

If I clear out all the records from the table it imports fine, but if I try the import again it errors with


Now Reference is an indexed field - it's the factory code. I just want this import to refresh the values for the other two columns, overwriting anything there. There are many other fields in the database for each record, so I can't just replace the data.

I'm sure this is a common issue but how do I get around it?

Simon

[ketbdnetbp]

Turncloud -

IMO, it would appear as though the Reference field's index is set to Yes (No Duplicates). So, when you try to import/append the second time, the values won't be accepted because they would result in duplicate values. Usually, an append query, adds records to the table whereas an Update query changes existing field values. Therefore, at first glance, you have two separate processes to address. 1.) Adding Records (new data that doesn't exist in the table currently) and 2.) Updating (existing field values). If you just want certain field values updated, then create an update query with the fields that require updating.

Hope this helps,

Jim

[Turncloud]

Thanks Jim, I didn't understand quite what you meant at first but that got me on the right track. By creating and update query and an append query and then setting warnings off I was able to achieve the same result of updating existing records and then adding any new ones.

Thanks for your help

Simon