Access cant append all records -how to stop this displaying?

[KevinSlater]

Hi,

I have the below SQL code in an append query to update a linked access table. The data is coming from a linked SQL table view (dbo_View_Prices). The problem is access pops up a message saying: "Microsoft office access cant append all records in the append query" - See attached screenshot but when I click the "yes" button it still seems to copy all the records fine, but i do not want the message promt to display. Is this possible? (another delete query is run before this table to clear the table first which runs fine)

INSERT INTO [PB-DATA-CURRENT_PRICES]
(itemcode,
cardcode,
price)
SELECT dbo_view_prices.itemcode,
dbo_view_prices.cardcode,
dbo_view_prices.price
FROM dbo_view_prices;

 

[Galaxiom]

That is not an error you want to stop per se. You are trying to append records that duplicate the Primary Key of the table. This is probably the itemcode field.

Outer Join the source table to the destination table and set a criteria Is Null on the key field in the destination table.

 

[KevinSlater]

thanks for you suggestion. I setup and Outer Join on the source table to the destination table but without setting an Isnull as i wanst to sure where to set this and it worked providing the destination table does not have any primary keys but i need primary key set on itemcode and cardcode on the destination table.

I then re-imported the sql view (dbo_view_prices) and selected itemcode and cardcode as the primary keys and set the destination table with the same two primary keys and it seems to work ok.

Previously only cardcode on the sql view was set to have a primary key