Results 1 to 4 of 4
  1. #1
    masoud_sedighy is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2011
    Posts
    78

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

    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.

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    The (TSql) insert query must be something like:

    INSERT INTO dbo.YourSqlTable
    SELECT * FROM dbo.YourAccessTable
    WHERE AccessTablePK NOT IN (SELECT SqlTablePK FROM dbo.YourSqlTable)

    Or you splitted your database, and after that you continue keeping data in front-end instead of linking SQL tables to it?

  3. #3
    masoud_sedighy is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2011
    Posts
    78
    thank you, if i had composite primary key in both AccessTablePK and YourSqlTable how should be WHERE clause.

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    On fly!

    INSERT INTO dbo.YourSqlTable
    SELECT yat.* FROM dbo.YourAccessTable yat LEFT OUTER JOIN dbo.YourSqlTable yst ON yst.PK1 = yat.PK1 AND yst.PK2 = yat.PK2 AND ...
    WHERE yst.PK1 Is Null OR yst.PK2 Is Null OR ...

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 03-23-2016, 07:26 PM
  2. Replies: 9
    Last Post: 02-02-2016, 06:27 AM
  3. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  4. Suppress "Error" message following "Cancel = True"
    By GraeagleBill in forum Programming
    Replies: 7
    Last Post: 03-23-2014, 05:40 PM
  5. "Group By" causes "ODBC--Call Failed" error
    By kaledev in forum Queries
    Replies: 1
    Last Post: 03-09-2011, 02:43 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums