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.
