Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    key violation message (2000/XP)

    I'm trying to get some data from one (temporary) table to another using an append query but I'm getting a key violation error.

    The primary key in the table I want to append to is a composite key, comprising a field called GISURN (text) plus a field called ID (long integer - not auto).

    My temporary table has the same key structure.

    The GISURN field forms the many end of a one-to-many relationship with a parent table - I have checked and all the values for this field exist in the parent table.

    No other tables have a relationship with the table I want to append to.

    I don't understand why I'm getting a key violation error when I try to run the query, any ideas?

    Edit: I have just read the message more clearly, it didn't want to add 116 or 119 records so I ran it to see which ones it would add. I then tried to run it again and it didn't want to add 116 or 119 records. I ran it again and it added another 3 records. Once again for luck, similar message, thought '115' this time - it added 4 records.

    I'm really confused now!

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

    Re: key violation message (2000/XP)

    What happens if you create a select query based on both tables, joined (with an inner join) in GISURN and ID? Does this query return any records?

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: key violation message (2000/XP)

    I've just done the select query you suggested.

    With the few added records the select query returns the same number. With the destination table cleared and empty the query returns nothing.

    I've exported the 2 tables plus the query to another db - same behaviour.

    ?
    Attached Files Attached Files

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

    Re: key violation message (2000/XP)

    OK, so the problem is not a conflict with existing records.
    Just a very long shot: are zero-length values allowed in GISURN in the temp table but not in the target table?

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: key violation message (2000/XP)

    Required=Yes
    Allow Zero Length=No

    In both tables.

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

    Re: key violation message (2000/XP)

    I'm afraid it's impossible to know what causes the error without seeing the database. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions. (We'd only need to see the two key fields in the two tables, and only a limited number of records, as long as the append query still displays the error message)

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: key violation message (2000/XP)

    Hans

    I posted the db a few posts up. It has all the records (119) and fields but is still fairly small.

    I've been repeatedly running the query (getting an error each time) and can eventually populate the destination table with all the records from the source table. Knowing me it's probably something obvious, but it just seems really odd.

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

    Re: key violation message (2000/XP)

    Oops, sorry, I hadn't noticed the attachment. The Reconnaissance table has a unique (but not primary) key on the Modified field. Since the Reconnaissance1 table doesn't have the Modified field, this field will remain null in the appended records. This causes the error. If you remove the index on Modified, the append query runs without a hitch.

    Added - no, the field is not null, it is set to Now(). If you try to append all records at once, Now will be the same for most or all records. If you keep on running the query, Now() will change each time. so you'll be able to append some records each time. The solution remains the same - remove the unique key, or set its Unique property to No.

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: key violation message (2000/XP)

    Ah, that was rather obvious, I should try opening my eyes.

    I suspect leaving it set to 'No Duplicates' was an oversight and that whoever changed it never imagined data being appended on-mass.

    I've just found the same setting it in a couple of other tables.

    Thank you for your help <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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