Append query key violations

[sal]

I have spent hours trying to append some data. The structure of temporary data table was originally copied from the main table and populated with the same exact look up tables so all data types and indexes were identical.

In desperation, I copied my main table and renamed it, established all the relationships to all the look up tables that the original main table had, then updated the append table to this copy.

It worked without incident. Why?

I would still like to run the query to append to the original main table.

 

[Galaxiom]

Your append is failing for key violations because you are trying try to append records that have values in the primary key that already exist in the data.

If the copy was a Structure only copy there would be no existing values to conflict with.

BTW, using lookups at table level is not recommended by most experienced developers.

 

[sal]

Your append is failing for key violations because you are trying try to append records that have values in the primary key that already exist in the data.

If the copy was a Structure only copy there would be no existing values to conflict with.

BTW, using lookups at table level is not recommended by most experienced developers.

No, the copy was structure AND data. And it worked. That's why it was so frustrating. There was no difference between the original table and the copy that successfully appended the records.

I then closed, compacted and re-opened the database and was able to append the records to the table. I have had this problem before. It seems like a ghost in the machine.

 

[Galaxiom]

Sounds like possibly an autonumbering issue. Sometimes the autonumber seed can get corrupted and it will start repeating previously used numbers. I gather from the reports I have seen that it mostly happens after records are inserted with a query especially if the autonumber field is populated from the query too. This was the case when it happend to me. I had restored records from a backup.

By copying the table you reset the autonumber seed. Indeed this is one of the techniques to overcome the problem though generally it is recommended to copy the structure and insert the records with a query.

Prior to Access 2003, compacting also reset the autonumber to the highest value in the field but Microsoft advised that this "might not" fix the the autonmumber seed any more.

I am somewhat suspicious of autonumber and would avoid it if records are being inserted in the normal use of the database and prefer to generate my own key.