Results 1 to 6 of 6
  1. #1
    Al77 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    20

    Question Key Violation - APPEND Query

    I keep receiving 'Key Violation' warning messages when trying to append data from one Table to another. This is despite NOT attempting to include / append the ID field of the donor table.



    After reading through some Forum entries I decided to try removing the Primary Key designation on the ID fields of the receiving Table and it now seems to work!

    This is not, however, acceptable because I NEED the ID in the recipient Table to be designated as a Primary Key. It does not work when 'no duplicates' are specified in the ID fields properties either. Unique ID's are, however, crucial here.

    Surely this is not an 'acceptable' bug?

    Any clues?

    Thanks

    Al

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Do tables have Autonumber fields? Are you including Autonumber field from donor table in the UPDATE? Or is the ID field you are NOT including an Autonumber? If you are NOT including Autonumber, and the ID field is a custom unique ID, and the receiving table has this field as PK, then must provide a unique ID value with the record.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    From your description, because you are not including the ID in the donor table, the receiving table is not getting a value for the ID. So the key violation error is because the data is missing, not because it is a duplicate. But, as June7 asks above, is the ID an autonumber? If it is not autonumber , that is the cause of your error. What you can do is add a constant value to the donor table ID in the append query, such that it is greater than any existing ID in the receiving table.

    For example, if the maximum existing ID in the receiving table is 100, then your append query would append ID+100 to the ID in the receiving table.

    HTH

    John

  4. #4
    Al77 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    20
    Thanks folks, but I'm still struggling with this.

    I like the idea of adding a constant, but would prefer to add the IDs (donor) to the highest value ID (latest entry) in the recipient table - would this be possible and, if so, how?

    If I just add a constant then the records cannot be sorted in the correct order, because appended records would ALWAYS be stored at the end of the Table, after any entries subsequently made in the recipient table - unless I am missing something?

    I would like this APPEND process to be a regular means of transferring records from a Provisional table to a Permanent one, hence the requirement.

    Any further advice appreciated.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The sequence records created should be unimportant. Sort by real info such as date, customer number, SSN, etc.

    Why do you have a 'Provisional' table?

    Still need to know about data structure. Are there Autonumber fields in the tables? What are the PK fields in each table? Show sample data, table structure, attach project.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Yes, by the suggestion I gave you, new records would be added at the "end of the table", bearing in mind that there is really no such thing as "end of the table" in MS Access - where records appear depends entirely on how you sort them for display in a form or report. So, if sorting by ID does not put the records in the right order - what does? Under these circumstances, the actual value of ID doesn't matter, since all it does is uniquely identify each record, but with no particular meaning. (You do realize that you can sort on any field you wish, I hope?)

    As June7 said, we need more information. One important question - is there any other field, or combination of fields, in your data that will uniquely identify each record?

    John

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

Similar Threads

  1. Append Query Key Violation
    By oleBucky in forum Queries
    Replies: 6
    Last Post: 12-05-2012, 03:55 PM
  2. Replies: 3
    Last Post: 07-27-2011, 02:07 PM
  3. Replies: 7
    Last Post: 07-21-2011, 01:01 PM
  4. append, Key violation
    By klapheck in forum Queries
    Replies: 0
    Last Post: 09-17-2009, 11:50 AM
  5. unable to append... key violation... message box
    By Coolpapabell in forum Access
    Replies: 4
    Last Post: 08-31-2009, 02:11 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