Autonumber is doing my head in!!!

[garyholc]

Never seen this happen before in all my 10 years of using Access. I have a table with a primary key field (record number) which is a autonumber field.

Been using it fine for months, now all of a sudden, after record number 1105 it reverts back to 430, which of course already exists.

I have removed the field, then re-created it, works for a little while then does it again. Its on a front end database so there are 6 users using the back end database.

Whats going on??!!?? Why is it loosing track of the record number and going so far back?

 

[KenHigg]

I'd try a compact & repair then if that doesn't work I'd try importing all of the objects into a new .mdb.

 

[garyholc]

Did the compact and repair thingie, no difference. Infact, I read somewhere that may have been the cause of the problem as compacting and reparing the database may reset the autonumber?? though ive not heard of this before....

 

[KenHigg]

It's been my experience that if you have an autonumber that goes to something like 2000 and the then delete the last 500 records and do a compact auto number will start back at 1501. But I never heard of it starting back in the middle like you describe... ??

 

[gemma-the-husky]

do an insert query, with the autonumber ifeld set to a valid number - you can do this in design pane

this will reseed the autonumber

then delete the new record

its a known bug (on MS knowledge base) caused by compact/repair, fixed in later versions of jet, i was told.

 

[Rabbie]

See this link for advice on reseeding an Autonumber

 

[garyholc]

Thanks all for your replies