Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    append query key violations (access 2000)

    Hi.

    I have a fairly simple append query where I want to append the records from one table into another, only if they don't already exist in that table. The table that I am appending to is the Staff table and the primary key is Staff ID.

    This is the sql I have

    INSERT INTO Staff ( StaffID )
    SELECT zOperaII.wn_ninum
    FROM zOperaII LEFT JOIN Staff ON zOperaII.wn_ninum = Staff.StaffID
    WHERE (((Staff.StaffID) Is Null));

    By the nature of the query, I'm only appending records where the key field does not already exist in the destination table. However, I am receiving the error message "can't append all records..... didn't add 140 records .. due to key violations.

    There are no other unique fields in the destination table (I checked by looking at the indexes).

    Does any one have any suggestions?

    Many thanks

    Amanda

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: append query key violations (access 2000)

    Does the Staff table contain other fields whose Required property is set to Yes, or fields with a Validation Rule?

  3. #3
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: append query key violations (access 2000)

    Hi Hans,

    That's the thing, there's no validation rules and no required fields. There are a couple of fields that won't allow zero length, could that be it?

    Many thanks

    Amanda

  4. #4
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: append query key violations (access 2000)

    I was just working on something like this yesterday and had the same problems.

    Create a query using the "Find Unmatched" wizard. It will ask you to put in the two tables, create the link property, and then you can view the result.

    Next, create yet another query, this time an Append query. Put the query from the wizard inside the append query. Put all the fields in that you want appended. It should work without errors.

    Mark

  5. #5
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: append query key violations (access 2000)

    Just tried it but it didn't work. Thanks for the suggestion, but exactly the same thing happened.

    Any other ideas?

    Amanda

  6. #6
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: append query key violations (access 2000)

    Well, once you create the find unmatched query using the wizard, try running it to see how many records it comes back with. Then check to see if any of those are in the destination table, there shouldn't be.

    Thanks,
    Mark

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: append query key violations (access 2000)

    I don't think we can solve this without seeing the database. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  8. #8
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: append query key violations (access 2000)

    Do you have any fields which have their index set to No Duplicates?
    This one sometimes gets overlooked because the setting is near the bottom of the table design screen.
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  9. #9
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    La Verne, California, USA
    Posts
    312
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: append query key violations (access 2000)

    If there is an AUTONUMBER field, try removing it from the query.

  10. #10
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: append query key violations (access 2000)

    Hans,

    Sorry I didn't reply, I've been away. Unfortunately the problem remains. Something weird happened when I tried to send you a stripped version of the database. I created a new database file. Imported the 2 relevant tables and the append query, however when I ran the append query in the new database, it worked no problems. It still is not working however in my database. This leads me to think that it might be something to do with relationships, but I don't know why.

    Will I send you the stripped version, which is working fine?

    Many thanks,

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: append query key violations (access 2000)

    There's not much point in attaching a version without the problem.

    You could try the following:
    - Create a new blank database.
    - Import all database objects from the problem database into the new database.
    - Set the startup options, and if necessary, non-standard references in the Visual Basic Editor.
    If the problem doesn't occur in the new database, use that one from now on.
    If it does occur, try removing elements one by one, and try to find out which step makes the problem disappear.

  12. #12
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: append query key violations (access 2000)

    Thanks for your help. I've just got to the bottom of it.

    The source data did not have a department field, where as the destination table does have a department field - which is linked to a department table with enforced referentiall integrity. When I changed the link to remove referential integrity it worked fine.

    Thanks again.

    Amanda

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •