how to solve error "Violation of PRIMARY KEY Constraint "XXXXXXXX". Cannot Insert Dup (1 Viewer)

masoud_sedighy

Registered User.
Local time
Today, 05:34
Joined
Dec 10, 2011
Messages
132
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

MVP / VIP
Local time
Today, 12:34
Joined
Jan 14, 2017
Messages
18,186
Re: how to solve error "Violation of PRIMARY KEY Constraint "XXXXXXXX". Cannot Insert

Omit the AutoNumber field from your query
 

masoud_sedighy

Registered User.
Local time
Today, 05:34
Joined
Dec 10, 2011
Messages
132
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

MVP / VIP
Local time
Today, 12:34
Joined
Jan 14, 2017
Messages
18,186
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
Code:
WHERE [MyDestinationTable].[MyPrimaryKeyField] Is Null
 

Users who are viewing this thread

Top Bottom