AutoNumber Reset

[Reggie Laffond]

Background:
I have a database I am rewriting in Access 2000 (split DB) that was
originally written in VB 3.0 with Access 2.0. The database has about 70
tables and was actually designed very well, completely normalized, very
efficient and had autonumber fields for all table primary keys. So I simply
imported the tables into Access 2000 for the back end and I am replicating
the forms and behavior in an Access 2000 front end.

Problem:
After working on this for 6 months including having the users do some
preliminary testing I have just discovered that one of my tables had reset
the next autonumber value to 1. Obviously I couldn't add records to the
table because key value 1 already exists. To recover from this I copied the
table (structure only) and ran an append query to create all of the records
with proper key values. Problem solved!!!

Questions:
How could this have happened? If this happened to one table can I prevent it
from happening to another in the future? I had repaired and compacted the
front and back ends a day or so before this problem surfaced. I have never
seen a repair and compact cause this problem.

Thanks in advance!

 

[Tim Ferguson]

So I simply imported the tables into Access 2000 for the back end and

I have just discovered that one of my tables had
reset the next autonumber value to 1.

Questions:
How could this have happened?

If you import a table _structure_ then the seed will start from scratch.
If you imported the table with its data then the seed will fall back to
(highest existing autonumber + 1). In effect you are just creating a new
table.

I had repaired
and compacted the front and back ends

Compacting a db with one or more empty tables will reset the autonumber
in that table or those tables.

I don't understand how it matters, though. If you want to start with an
empty database, then it's appropriate to let all counters restart. If you
want to start with a seeded database, then you will need some method of
initialising it, which in turn means that you think very carefully about
what data you need to import, in what order, and how. This is a technical
procedure called Data Migration.

All the best


Tim F

 

[Reggie Laffond]

I might have given too much information and made my question unclear.

Basically I had a table with 939 records in it with the primary key (auto
number field) from 0 to 942 (with three missing numbers caused by deleted
records). I compacted and repaired the database.. The next day when I tried
to add a new record to the table the auto number kept coming up 1 which
already was used in the first record in the table.

I just want to know if "repair and compact" could reset the auto number seed
even though there were 939 record in the table. Or is there some other known
bug that could cause this to happen. I just want to prevent it in the
future.

Thanks!

 

[Jeff Conrad]

Updating your Jet file should solve this issue I believe. See:

ACC2000: AutoNumber Field Duplicates Previous Values
After You Compact and Repair a Database
http://support.microsoft.com/default.aspx?scid=kb;en-us;257408

 

[Tim Ferguson]

. I compacted and repaired the database.. The next day when I tried
to add a new record to the table the auto number kept coming up 1 which
already was used in the first record in the table.

As Jeff says, this is a bug. Update Access with latest service packs.

All the best


Tim F

 

[Reggie Laffond]

Will do. Thanks for the quick reply! Great newsgroup!

 

[Reggie Laffond]

Will do. Thanks for the quick reply! Great newsgroup!

 

[Jeff Conrad]

You're welcome, glad to help.
Come back any time.