Access weird index

This is what I posted in discussion when you asked the question there.

Presumably you are referring to an automatic incrementing primary key.
If compact and repair doesn't sort it.
Alter the table, drop the index and change the field to a long or whatever the base type is.
Then change it back.
If that doesn't work create another table with the same structure and an auto index and select into it (not the existing key). Note the numbers may/will change if you do this so you may want to update any other tables that use the key in this one as a reference.
****************************
Needless to say, but back the mdb up before you start cacking about
**************************************

I was looking at this again monday and I may need to recreate the whole db as after looking into it further there are three different tables that all have messed up numbering. what would be the best approach?
Thanks

You will probably have to end up droping the Id field from the table as that other person said. It is possible to sort the records in the damaged table by changing the current AutoNumber field to LongInt (but leave it as the primary key), and then add a new TempId field type AutoNumber. It should nicely number the records in sequence. If it does, you can THEN drop the damaged ID field and rename the new TempId field.

Insert another field at the beginning of the field list with the data type as autonumber. Save then change the new field to the primary key. Current one looks like a calculation based on a time/date value stamp.

In Microsoft Access 2000, the AutoNumber data type has a NewValue property that enables you to select either Random or Increment as the method of creating the next value for the field.

If you select Random, values are assigned as Long Integer. Otherwise, values in an AutoNumber field increment from 1 to 2,147,483,647. The next record receives the minimum negative value for a Long Integer, -2,147,483,648. T

he AutoNumber value for each subsequent record is one greater than the previous value, until it reaches zero. Therefore, the maximum number of records that a table may hold if the primary key data type is set to AutoNumber is 4,294,967,295.

To change your Autonumber field from generating negative numbers you need to:
1. Create a new autonumber field, with a NewValue property of Increment.

2. Save the table and the new autonumbers will be generated.

3. Look up (you can use an update query) records in orther related tables that are pointing to the old autonumber field.

4. Change their values to the new autonumber field's value.

5. Delete the old autonumber field.

6. Rename the new autonumber field to the old autonumber field.

It sounds as if the Primary key has been changed to Random, as noted in Answer 4. This can happen 2 ways: 1) by choice; 2) automatically by Access when you create a Replicated database. This is why several db designers advise never to use the Primary Key as an identifier (such as Client ID)for your data.

Unless you use it for something like a Client ID number, I don't expect it would have an affect on the operation of your db. Trying to "correct" it could make things much worse.

If it was changed through the creation of a Replicated db, you will find the change has taken place in every Primary Key.

This question was closed by the author