changes not successful; create duplicate values in the index, primary key, or ...

[stormin_norm]

This is a real mind bender.
I am running a2k. I am merging two databases. table1 is in the backend database. table2 is linked to the second database.
I run an append query to add table2 entries to table1. The append fails for 96 recs saying key violation. Turns out I can rerun the append if I drop the zip code field.
I then try to manually change the zip code and receive the duplicate values in index, primary key, or relationship.

I ran a compact & repair the databases, still no go. I can't edit the value of the zip code. I import the table to a new database. Still can't change the zip code. I drop MANY of the indexes. Even the index on the zip code field. Run compact&repair. still getting the index message. I even re-imported to a new db again. Still same error message.

I may start again by importing JUST these two tables to a new blank db and see if I get the same issues. I'll keep you posted...but maybe there is someone out there who has seen this error before.

fyi-if I enter a new record, the autonumber field correctly increments to the next available autonumber field. (I say this because of another thread on this matter w/ autonumber fields and bug in A2003)

 

[stormin_norm]

Very strange. It appears access believes two rows are "identical" even though there is an autonumber field. And I added an "oldid" field as another index. Still getting the error message.

These four fields can NOT be identical on two records; If I make 1 small change to any of these four, the record WILL update ok. Success!

Last Name: Required=Yes; Allow Zero=NO; Index=YES Dups OK
First Name: Required=No; Allow Zero=YES; Index=YES Dups OK
Address 1: Required=No; Allow Zero=YES; Index=NO
ZIP Code: Required=NO; Allow Zero=YES; Index=NO

Note: Previously Zip Code WAS Required=No; Allow Zero=Yes; Index=YES Dups OK.
Perhaps Address 1 & Zip Code were indexed in this database's former A97 life???? Mmm. Even after compact&repair and importing into a new db! Maybe, just maybe those indexes are still floating around the table definition.

So what can one conclude about access?
It doesn't know a unique record when it has one.

I ponder philosophically---Why bother with a primary key on the autonumber field???? The record is not unique anyway


My quick fix (so I can finish this conversion tonight): Add an extra space in front of Address 1 or perhaps a period on the end of Address 1.
Wow what a wasted afternoon trying to debug this.