Unique ID not unique!

[Chalkie42]

My database has been running perfectly for months but suddenly stopped recording new entries via my input form. Couldn't find anything wrong with the form but when I tried to enter a new record directly onto the table I realised that Access was trying to allocate a unique ID that had already been used with a previous record. The table is set up to allocate the unique ID as 'autonumber' and 'increment'.

Any wisdom appreciated.

 

[WayPay]

That's strange. Try to do a "Compact & Repair", that should reset the AutoNumber to use (highest ID + 1) for the next record.

 

[Chalkie42]

Yeah, I thought of that too but I have the database set to compact and repair on every shutdown - so no joy.

 

[Chalkie42]

Update - I got around this by severing the relationships and deleting the primary key (Unique ID) field. I then re-inserted the field and re-established the links. So, the database has a whole new set of autonumbered unique Ids and everything works fine again. trouble is, I don't know what caused this nor what I can do to prevent it re-occurring

 

[Rabbie]

Which Version of Access are you using? Do you have all the latest patches (including any hotfixes)?

 

[boblarson]

Perhaps this will help:
http://allenbrowne.com/ser-40.html

 

[Chalkie42]

Bob, thanks for that signpost. I think I will review my structures in the light of the information given in the post. I do have a number of subforms attached to the main form. Looks like that could be part of the problem.

Rabbie, Access 2000 on XP. It's on a Government system so I'll be able to make absolutely no changes to setup etc.

Thanks again.

 

[gemma-the-husky]

is this sorted now?

there is/was a known bug in some Jet versions (see MS knowledge base) with autonumbers - see MS knowledge base for inof re reseeding the autonumber when this happens

sorry, i dont have the link to hand

 

[LPurvis]

Introduced in Jet4 SP5 if I recall offhand correctly.

Access 2000 won't reseed an autonumber upon compact. That was Access 97 (Jet 3.5) behaviour (and again with SP5 I think - around Access 2002 time).

I doubt you've inadvertently implemented the technique highlighted on Allen's site to cause autonumber standstill.
Bear in mind that Autonumber's are free to be duplicated. If you enter over 4 billion rows (and obviously delete some as a Jet db can't hold 4 billion rows of data ;-) then the Long sub datatype of the Autonumber will have wrapped around - and come back to zero... after which you could encounter legitimate duplicates.

As mentioned by Allen, the seed can be specified. And since you are free to Insert autonumber values then it can be done so through a SQL statement and not just via ADOX.
If you have one of the suspect Jet SP's then a) shame on the "Government system" ;-) and b) avoid poor practices - fix the problem and be vililent for it again.

(There's really no reason for a critial IT system not to be running with the latest Jet SP's - it's not like they're waiting for the newer ones to come out... Jet 4 SP8 has been around for about 6 years maybe? There won't be another one!

Cheers.

 

[gemma-the-husky]

I think the reason that you get a suspect Jet version, is that you install Access 97, and use it.

then
a) The autonumber problem is very rare - I've only seen it once

and
b) its not immediately obvious that the wroing version of Jet causes this, ir that you do not have a current version of Jet, or indeed how you would update your version of Jet

like a lot of things, its often better to leave well alone (or in this case ever so slightly not well)

eg - my son recently got a worm/virus thing on his PC, (some rogue anti virus thing he loaded which was a mistake) - I just couldn't get it off - and I had to reinstall the os from the rescue disks. I wasnt sure what this would do his existing data etc etc, as I had never done anything like this before

What happened was that the restore process seemed to reload the C: partition, but not his other partitions, I had to reinstall the programmes he had lost, but that was all - his data was intact. This was a pleasant surprise - I really did expect the whole disk to be refomatted

I just mean its not knowing exactly what will happen that dissuades users from amending os type things.