By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,998 Members | 2,825 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,998 IT Pros & Developers. It's quick & easy.

Help - Autonumber field corruption

P: 5
I have table of Jobs which has an autonumber field (JobID). Recently, the client had a network glitch (I think) which corrupted a couple of job records (left gibberish in the fields). Compacting the database eneabled me to delete the two corrupted job records, but since then any new jobs entered generate a VERY long autonumber. Before the corruption, the autonumber was at 710, now, on entering a new record, it generates an 8 digit number(45547561). This is causing an 'Overflow' error on the Jobs form when the user selects it.

This looks serious and I can't seem to stop this happening - any ideas people?

Feb 8 '12 #1
Share this Question
Share on Google+
4 Replies

Expert Mod 100+
P: 2,321
If I recall correctly (Access 2003 behaviour)
When a database is compacted, any table will reset its autonumber counter to the last USED value. So if you made records 1,2,3,4 and then deleted records 1, and 4, the next autonumber would still be 5. Doing a compact will (I believe) reset the autonumber to make the next value be 4.

Its been a while since I've used this myself, and im not 100% sure if it only works on tables that have been emptied completely.

Anyways, try it, and if it doesn't work, there is another method allthough its a bit more complicated.
Feb 8 '12 #2

P: 5
Hi - makes sense. the corruption caused the autonumber field to generate large values on the corrupted records, so that's why it's carried on that way. I've just solved it - copied the table not including the data, appended the jobs table data to it and all is well!

Feb 8 '12 #3

Expert Mod 100+
P: 2,321
Yup, that was the second option I hinted at. Without knowing your level of expertise, I just did not want to risk adding confusion :P, and wanted to start with the simplest approach.

Welcome to Bytes
Feb 8 '12 #4

P: 5
Thanks - appreciate your help and support.
Feb 8 '12 #5

Post your reply

Sign in to post your reply or Sign up for a free account.