AutoNumber field AND unique index field contain a duplicate value in my table

[BoulderRidge]

I can send screen shots if you need proof In my table, the first column is an AutoNumber field and the second column is long integer with a unique index (Primary Key=No, Unique=Yes, Ignore Nulls=No). I can see two records with the same first column value and the same second column value.

How could this be?

The only flaw with this table is that it doesn’t have a Primary Key defined, and I plan to fix that by making the AutoNumber field a PK. However, that doesn’t explain how either the AutoNumber field got a duplicate value or why the field having a unique index allowed a duplicate value. Anyone see anything like this before?

Background: Access 2010 application, backend database with tables lives on server, front end application with links to tables lives on each users laptop or tablet. We do have several users entering records via a data entry form at the same time and we have a timer event running once per minute that updates other values in existing records (no update attempted to the fields in question.)
Any insight is welcome. Do you think adding the missing Primary Key to the table will prevent any future such anomalies?

Thanks,


-- BoulderRidge B-)

 

[lameid]

I think I've seen this mentioned before long before there was an Access 2007. It is simply a rare corruption. I have not experienced it.

I would fix the problem and add the appropriate indexes. If you see the issue again, then I'd start to be concerned.

To fix, I would import all the other tables into a new database ant then recreate the problem table definition manually. Lastly, I would link to both databases (original and new) for the problem table and write queries to put the 'correct' data in.

My expectation is that you will not see the issue again unless there is something odd going on in your network (I don't trust Novell as it has some sort of file caching that could do evil things).

Oh and congratulations, you could have used the same amount of luck to win the lottery instead of seeing this problem Or maybe it is a balance thing and now you are destined to win the lottery. Then of course, maybe I'm wrong and there is a repeatable problem.

 

[lameid]

One other thought, sometimes the screen doesn't redraw correctly and you can see the same record twice. In this case updating display drivers usually helps.

 

[BoulderRidge]

Hi lameid,

Thanks for the comments and detailed advice...I think I'll go for the "rare and not likely to repeat" option but if it turns some other bit of luck in my favor that will be a bonus.

I had deleted the offending record and added the primary key with two successful repair and compact steps this morning so my users could get back in. We'll see if it dares to surface again. Then I will indeed be concerned!

-- BoulderRidge B-)

 

[MichaelRed]

This was an issue a long while ago. Access 2003? Supposedly fixed ... The Autonumber field could be corrupted on multiuse networks due to the timing issue between users traffic. A workaround was posted hese (Tek-Tips fora) however I do not rercall its' propper name. Basic premise was to have each users attempt to add a record use a 'single entry' routine - forcing other user(s) to wait until the previous user was complete (e.g. had successfully added trheir record). I believe this was postged as a FAQ (still not sure where other than Tek-Tips and somewhaqt re;ated to MS Access and / or vba.



MichaelRed

 

[BoulderRidge]

Actually just last week I had a major "ah-hah" moment on this issue. I believe it was indeed corruption...and I think I now know what triggered it:
1. Multiple users were running the same physical copy of the front-end Access application from the network instead of running from their own individual copy (the person managing the deployment mistakenly thought giving them a copy of the shortcut was the same as giving them a copy of the actual application--don't ask)
2. This nasty multi-user scenario was causing corruption in the backend shared database
3. The corruption in the backend shared database was causing the primary key on the main table Autonumber field to drop (after restoring this PK three times it became clear that it wasn't just a memory problem, as in "I thought I already fixed that...")
4. The loss of the the PK plus the influence of corruption plus the right combination of multiple users editing two adjacent records using popup forms with a timer event running created what appeared to be inexplicable results in the saved data.

I have now restored the PK again, imported everything into a clean database container, and corrected the deployment problems. Time will tell, but I am cautiously optimistic that my support calls will finally go away!

Thanks for all the good comments...

-- BoulderRidge B-)