how to solve error "Violation of PRIMARY KEY Constraint "XXXXXXXX". Cannot Insert Dup

[masoud_sedighy]

how to solve error "Violation of PRIMARY KEY Constraint "XXXXXXXX". Cannot Insert Dup

i am using 3 different append query for inserting records from ms access front-end to migrated sql server back-end. because of slow execution i changed theses queries to pass-through query, now when i run these queries i got error "Violation of PRIMARY KEY Constraint "XXXXXXXX". Cannot Insert Duplicate Key , the statement has been terminated"
when i was executing these queries in ms access front-end and ms-access back-end, i could click yes button for continue the inserting or if it was part of a macro i could define set warning =No and it was continuing inserting records, now it has been terminated.

how i can solve the problem for automatically insert key records without getting error for duplicate values.

 

[isladogs]

Re: how to solve error "Violation of PRIMARY KEY Constraint "XXXXXXXX". Cannot Insert

Omit the AutoNumber field from your query

 

[masoud_sedighy]

Re: how to solve error "Violation of PRIMARY KEY Constraint "XXXXXXXX". Cannot Insert

there is no Auto number field in my table and my primary key is a text.

 

[isladogs]

Re: how to solve error "Violation of PRIMARY KEY Constraint "XXXXXXXX". Cannot Insert

Violation of PRIMARY KEY Constraint "XXXXXXXX". Cannot Insert Duplicate Key , the statement has been terminated

This means the primary key value already exists in the destination table.
So you need to modify the append query to exclude any records that already exist

Add a filter to this query which is something like

WHERE [MyDestinationTable].[MyPrimaryKeyField] Is Null