Error in allocating Autonumber

[Chalkie42]

Hi Folks,

I am looking for some hope that my database is not corrupt.

The first sign of trouble was when users could not add new records via the input form. No error messages - just lack of a new entry. I tried entering a new record directly into the table but the autonumber allocated (automatically) is one that has already been used, so I get the error that results. I compact and repair at every closedown.

Any help will be gratefully received.

Access 2000 in Windows XP.

 

[ColinEssex]

Autonumbers are not able to be issued twice. I'm guessing you'll need to delete the duplicate entry and re-enter it with a new autonumber.

I'm hoping you don't use the autonumber for anything other than linking records in different tables.

Col

 

[Chalkie42]

Absolutely! The autonumber is only there to link all my tables and queries. I am not entering anything in the autonumber column. Access is allocating the duplicate number itself even though it is set as 'sequential - duplicates not allowed'.

Chox

 

[Chalkie42]

OK, I assumed that the Table was corrupt. I copied it and renamed it. I severed all the relationships with the original and deleted it. I then re-named the copy to that of the original and re-established all the relationships. Hey presto! It now works fine.

Chox.

 

[gemma-the-husky]

Look on MS - there IS a reported cicrcumstance and fix for this - the autonumber can get corrupted - you need to write a manual query to reseed the number

ie insert into table new autonumber value etc

hope this helps

 

[LPurvis]

And just to make clear - you weren't getting an error because of the autonumber datatype encountering a duplicate. Rather because it was (almost certainly) a primary key field - and it is the PK which enforces uniqueness.

An autonumber field alone can certainly produce the same value again - and were MDB file size not a limitation eventually would do so when the sub Long datatype wraps back around to your initial values.
(Naturally you can always seed the value, as mentioned by Gemma, to speed this process up a bit ;-)

 

[gemma-the-husky]

no LP

there's definitely an issue in some circumstances with autonumber sequences - however Access manages it, it can start issuing duplicates, and the recommended solution is to reseed the table with a larger "safe" number

its documented on MS knowledge base

 

[LPurvis]

I didn't say there wasn't.

 

[gemma-the-husky]

sorry, i thought you said the the OP error wasnt to do with the autonumber, but was to do with a duplicate PK

eg - here's one reference to the autonumber problem on the MS Knowledge Base
http://support.microsoft.com/kb/291162/en-us

 

[LPurvis]

Yep. I was just highlighting why there's an error raised.
Otherwise the AN (for whatever reason) would happily go on its way - duplicating it's little heart out.

FWIW that particular KB article refers to the issues before Jet4 SP5 (as I recall) whereupon MS removed the reseeding compact that Jet 3.5 used to perform by default.
But users (/IT) should always look to have Jet4 SP8 installed anyway.
The article linked to in your link is the one which has the excellent definitive history of releases.