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

Re-using Old Autonumber Values

P: n/a
I've just had to upgrade to Access 2003. Our company's main sales database
started in Access V1.0 and has progressed through V2.0 and 97 without
problems. I've converted it to 2003 format and have been going through a
process of testing it. To my horror I noticed that seemingly without
provocation, it has started re-using old Autonumber values. For example, I
have an archive routine which removes completed sales enquiry records out of
the 'ongoing' enquiry tables, leaving 'holes' in the Autonumber sequence.
Access is re-using these old values for new records. Even worse, when I run
my archive routine, the re-used value conflicts with the original one in the
archive records and...........Access deletes them both!!

I've had a little sniff around the Internet and this problem (or something
like it) appears to be known for old versions of Jet, especially after
compacting and repairing (eg Knowledge Base Article 291162). However, my Jet
version is very recent (as it came with access 2003!) and I'm not aware that
I have done a compact and repair in the recent past. I have also seen a
number of people saying don't use Autonumber because it is flawed, instead
use a 'manual' incrementing system. This would be an enormous task as my
database relies on Autonumber in dozens of tables.

This has become a meltdown issue for us. Any help much appreciated.

Cheers,

Phil.
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Did you update your Jet versions as suggested in the Knowledgbase articles?
This supposedly will take care of your issue. Note that you'll want to do
this on ALL your machines ...
--
Scott McDaniel
CS Computer Software
Visual Basic - Access - Sql Server - ASP

"Smash forehead on keyboard to continue ... "
"BT Openworld" <ac*********@btopenworld.com> wrote in message
news:c0**********@sparta.btinternet.com...
I've just had to upgrade to Access 2003. Our company's main sales database
started in Access V1.0 and has progressed through V2.0 and 97 without
problems. I've converted it to 2003 format and have been going through a
process of testing it. To my horror I noticed that seemingly without
provocation, it has started re-using old Autonumber values. For example, I
have an archive routine which removes completed sales enquiry records out of the 'ongoing' enquiry tables, leaving 'holes' in the Autonumber sequence.
Access is re-using these old values for new records. Even worse, when I run my archive routine, the re-used value conflicts with the original one in the archive records and...........Access deletes them both!!

I've had a little sniff around the Internet and this problem (or something
like it) appears to be known for old versions of Jet, especially after
compacting and repairing (eg Knowledge Base Article 291162). However, my Jet version is very recent (as it came with access 2003!) and I'm not aware that I have done a compact and repair in the recent past. I have also seen a
number of people saying don't use Autonumber because it is flawed, instead
use a 'manual' incrementing system. This would be an enormous task as my
database relies on Autonumber in dozens of tables.

This has become a meltdown issue for us. Any help much appreciated.

Cheers,

Phil.

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.