Change all primary keys in access table to new numbers

Question

I have an access table with an automatic primary key, a date, and other data. The first record starts at 36, due to deleted records. I want to change all the primary keys so they begin at 1 and increment, ordered by the date. Whats the best way to do this?

I want to change the table from this:

| TestID | Date     | Data |
|  36    | 12/02/09 | .54  |
|  37    | 12/04/09 | .52  |

To this:

| TestID | Date     | Data |
|  1     | 12/02/09 | .54  |
|  2     | 12/04/09 | .52  |

EDIT: Thanks for the input and those who answered. I think some were reading a little too much into my question, which is okay because it still adds to my learning and thinking process. The purpose of my question was two fold: 1) It would simply be nicer for me to have the PK match with the order of my data's dates and 2) to learn if something like this was possible for later use. Such as, if I want to add a new column to the table which numbers the tests, labels the type of test, etc. I am trying to learn a lot at once right now so I get a little confused where to start sometimes. I am building .NET apps and trying to learn SQL and database management and it is sometimes confusing finding the right info with the different RDMS's and ways to interact with them.

Answer 1

I would create a new table, with autoincrement.

Then select all the existing data into it, ordering by date. That will result in the IDs being recreated from "1".

Then you could drop the original table, and rename the new one.

Assuming no foreign keys - if so you'd have to drop and recreate those too.

Answer 2

Following from MikeW, you can use the following SQL command to copy the data from the old to the new table:

INSERT 
    TestID, Date, Data
INTO
    NewTable
SELECT
    TestID, Date, Data
FROM
    OldTable;

The new TestID will start from 1 if you use an AutoIncrement field.

Answer 3

An Autonumber used as a surrogate primary keys is not data, but metadata used to do nothing but connect records in related tables. If you need to control the values in that field, then it's data, and you can't use an Autonumber, but have to roll your own autoincrement routine. You might want to look at this thread for a starting point, but code for this for use in Access is available everywhere Access programmers congregate on the Net.

Answer 4

I agree that the value of the auto-generated IDENTITY values should have no meaning, even for the coder, but for education purposes, here's how to reseed the IDENTITY using ADO:

ACC2000: Cannot Change Default Seed and Increment Value in UI

Note the article as out of date because it says, "there are no options available in the user interface (UI) for you to make this change." In later version the Access, the SQL DLL could be executed when in ANSI-92 Query Mode e.g. something like this:

ALTER TABLE MyTable ALTER TestID INTEGER IDENTITY (1, 1) NOT NULL;