Appending Records and Key Violations

[godiva]

Hi,
Here's the problem that I'm up against:
I have an append query. When I run the append query, it says that it
can't add records due to a key violation. I check indexes on the
table and review the data. This set of data does not exist anywhere
in the table and it is not in violation of any indexes on the table.
It will append 5 of the 127 records into the table. If I keep running
it, it eventually appends more and more into the table.
I have even tried appending the records from a select query using one
table instead of the append query looking at joined tables. The error
still happens.
I've copied the structure of the table the records are appending to.
Then the records append without a problem. The current table has
around 125k records in it. I am now wondering if it happens to be the
size of the table that is the problem. Even making a copy of the
structure, then appending the records into the new table from the old
then running the append query still gets the same result. I've even
attempted to move the records using recordsets and moving one by one
just to be told that I was duplicating data (that wasn't even in the
table).
Any suggestions on how I can beat this?
I've run compact and repair on the data side of it as well as the
front end.
thanks
MM

 

[Allen Browne]

Here's some suggestions on tracking down those errors:
Why can't I append some records? Trouble-shooting imports
at:
http://allenbrowne.com/casu-19.html

Post back if you followed through all 5 possibilities and still can't see
the cause.

 

[godiva]

Here's some suggestions on tracking down those errors:
    Why can't I append some records? Trouble-shooting imports
at:
   http://allenbrowne.com/casu-19.html

Post back if you followed through all 5 possibilities and still can't see
the cause.

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.






- Show quoted text -

the only fix I've found for what was happening was to move all the
objects into a clean database, then the append worked as expected. It
has me wondering if some corruption wasn't happening. I had to do the
same with the front end today to get it past an odd error that should
not have been happening (include with this that some code changes and
a couple of new queries were missing).

 

[Dennis]

This issue surrounds the Primary Key, and the fact that no duplicates are
allowed, and that auto-increments. Let's say you create a Primary Key and
start filling records. Okay, record 1 is PK = 1, record 2 is PK = 2, etc.
Fine. Let's say you add 1000 records. The PK value for the 1000th record
would (of course) be 1000. Now let's say you delete record 800-875. Those
numbers (PK values) are now available. When you start importing, one of your
imported columns is to be assigned as the PK. Fine. let's say that starts at
825, and you have 500 records, each numbered consecutively. So.... 825, 826,
827, etc. No problem. Now you get to 1000, and the import will bomb because
of a PK error. All the PK issues are basically associated with this kind of
thing. (I'm not explaining this very clearly, and I apologize for that. It's
easier to SEE it in action than to talk about it.)