Indexing Messed Up

[TonyU]

I have an application running under Access 2000/2002. The index (an
integer) for each record is automatically assigned and incremented.
Suddenly, the program decided to start assigning a number already assigned
previously in the middle of the file and is incrementing from this point
forward. Obviously, I get error messages, since I am not allowing
duplicates. My operating system is Windows XP Home and Access is part of
Office XP Professional.

I installed Office XP Service Pack 3 a few days ago. Might this have caused
the problem, or is something else wrong?

Your help is appreciated.

Tony Uythoven

 

[John Vinson]

I have an application running under Access 2000/2002. The index (an
integer) for each record is automatically assigned and incremented.

Don't confuse an Autonumber Field - a piece of data stored in your
table - with an Index, a usually-hidden construct within the database
which makes searching more efficient. There will usually be an Index
assigned to an autonumber field, but that's a separate issue. See
below.

Suddenly, the program decided to start assigning a number already assigned
previously in the middle of the file and is incrementing from this point
forward. Obviously, I get error messages, since I am not allowing
duplicates. My operating system is Windows XP Home and Access is part of
Office XP Professional.

This was a known bug in Access2000, and it's not clear whether it's
been solved...

I installed Office XP Service Pack 3 a few days ago. Might this have caused
the problem, or is something else wrong?

That should have *FIXED* it not caused it! But I've heard of weirder
things happening...

I'd suggest three escalating attempts to fix:

0. BACK UP YOUR DATABASE!!!

1. Try Tools... Database Utilities... Compact and Repair Database.
2. Open the table in design view; use the Key icon to remove the
Primary Key status of this field (if it is a primary key); also view
the field's Properties and delete any Index on the field. Compact the
database; open the table in design view again and reinstate the index.
3. Create a new, empty database. Use File... Get External Data...
Import to import everything BUT this table. Import this table, *design
view only*; then use File... Get External Data... link to connect to
the old database, link to this table, and run an Append query to
migrate the data into the new table.

John W. Vinson[MVP]