Corrupted Auto Number - Help!

[PiedPiper70]

As part of a larger multi-user system there is a time recording part with a simple table called tblTime with an autonumber field. Today at 11:45 users started reporting receiving a 3022 error. On looking at the table I noticed that if I tried to manually add a record then the ID field number inserted automatically was not the next number in sequence, but one that already existed, hence the error.

Can anyone shed any light on how this might have happened, and what I can do about it?

Can I reset the seed, or what?

Thanks
Dave

 

[pr2-eugin]

UtterAccess has a very good document on what AutoNumber are and what they are not : http://www.utteraccess.com/wiki/index.php/Autonumbers

Autonumbers are not meant to be ID fields, if they mean something to you. If you need the ID to be a meaningful (sequential) then you might be better of with some technique as DMax+1

 

[plog]

I don't know if I can post a link to stack overflow, so I am plagirizing the answer I found there (you can google "access adding autonumber that already exists" and its the first result):

1. Copy just the structure of tblTime and name it newTime

2. Make an APPEND query based on tblTime and append all the data in there to newTime.

3. Copy newTime and paste it (including data) back into your database calling it tblTime and over writing the existing tblTime.

That should replace your table and fix the autonumber issue.

And as always, step 0 is make a backup your database in case anything goes wrong.