Append query (ran from VBA) adds records, despite key violation?

[gmworrall]

Bit of an odd one...

- I have a module which runs queries on linked sales spreadsheets, to merge them in to one Access table.
- To prevent duplication of sales, the primary key merges the sales record and item number fields.

Today, it's found 103 key duplication errors, which is fine. But it's still adding data to the table. The data seems to be fields which aren't even mentioned in the query. It only does this when the query is ran from VBA.

Query:

MergeEbay = "INSERT INTO tblSales ( SalesRecord, SKU, PostCode, Shipping, Quantity, SalePrice, SalesRecordSKU, DateAdded )" & _
"SELECT exEbaySales.[Sales record number], IIf(exEbaySales.[Custom label] Is Null,'0',exEbaySales.[Custom label]), exEbaySales.[Buyer postcode], " & _
"IIf(exEbaySales.[Postage service] Like '*24*','Express',IIf(exEbaySales.[Postage service] Like '*48*','Standard',exEbaySales.[Postage service])), " & _
"exEbaySales.Quantity, exEbaySales.[Total price], exEbaySales.[Sales record number] & exEbaySales.[Custom label], Date() " & _
"AS DateAdded FROM exEbaySales;"

Records (example and incorrect data):

SalesRecord, SKU, Dropship, PostCode, Shipping, Quantity, SalePrice, Picked, SalesRecordSKU, DateAdded
310246702, 12017, BB54 4PZ, Standard, 1, £27.91, No, 31024670251017, 03/12/2013
colin88, , , PayPal, 29-Nov-13, , , No, colin88, 03/12/2013

 

[namliam]

Holy unreadable sql batman :/, though I am glad atleast you found the code wraps

MergeEbay = " INSERT INTO tblSales ( SalesRecord, SKU, PostCode, Shipping, Quantity, SalePrice, SalesRecordSKU, DateAdded )" & _
            " SELECT exEbaySales.[Sales record number]" & _
            "      , IIf(exEbaySales.[Custom label] Is Null,'0'" & _
            "                                              ,exEbaySales.[Custom label])" & _
            " , exEbaySales.[Buyer postcode]" & _
            " , IIf(exEbaySales.[Postage service] Like '*24*'     ,'Express'" & _
            "       ,IIf(exEbaySales.[Postage service] Like '*48*','Standard'" & _
            "                                                     ,exEbaySales.[Postage service]))" & _
            " , exEbaySales.Quantity" & _
            " , exEbaySales.[Total price]" & _
            " , exEbaySales.[Sales record number] & exEbaySales.[Custom label]" & _
            " , Date() AS DateAdded " & _
            " FROM exEbaySales;"

Why based on this code would you expect dups to be excluded? or even be detected?

If we are to help you you will need to post more, possibly the full code involved

 

[gmworrall]

Thanks for the translation namliam!

There's no other code involved - just a linked CSV file and the table design. The duplicates will come up against data already appended to the table - 'SalesRecordSKU' is Indexed (No Duplicates).

It does detect them, comes up the key duplication error, then proceeds to add the records anyway, albeit with the wrong fields...

 

[namliam]

ah sorry, forgot about the duplicate key violation thingy :/

It may sound stupid but why create a concatinated field just for the "key" purpose?
Instead just make a multi column key?

Why rely on the dedup of the key from access anyways, never rely on computers to do your dirty thinking for you.... Instead simply outerjoin your input table and target table to only input the new records

FYI...

            "      , IIf(exEbaySales.[Custom label] Is Null,'0'" & _
            "                                              ,exEbaySales.[Custom label])"

Is much nicer if you replace it by

            "      , NZ(exEbaySales.[Custom label],'0') " & _