key violation message (2000/XP)

Topic

New Reply

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!

Reply | Quote

Replies

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?

Reply | Quote

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.

?

Reply | Quote

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?

Reply | Quote

Required=Yes
Allow Zero Length=No

In both tables.

Reply | Quote

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 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)

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote