Renumbering AUTONUMBER fields-existing data

[Guest]

If I have a lot of records in a table (500,000+), and I want to renumber an
autonumber (primary key) field, how can I do that. For example, if the lowest
number is "567,345" and I want to spin thru all records and change the value
to starting at "1" (and incrementing), how can I make that happen? Preferably
programmatically, but via a query/macro would be fine too.

Thanks!

 

[Sylvain Lafontaine]

There is no simple way of dealing with autonumber field under SQL-Server.

The easiest way that I can think of would be to create a second table, make
a Select Into from the first table to this one, delete the old table and
rename the new tabl. You will have to remove and re-install the foreigh key
relations before and after.

S. L.

 

[Sylvain Lafontaine]

Oups! Sorry, but this answer was for SQL-Server, not Access.

It is possible that there is a better answer for Access but I don't know it.

S. L.

 

[John Spencer (MVP)]

First question is WHY? If this is the primary key and is used in relationships,
you are going to break all the relations between this table and the other tables.

If you really have to do this, the easiest way is to create a copy of the table
structure and then import all the fields EXCEPT the autonumber field from the
old table to the new table. Access will auto generate the new autonumber for you.

You cannot change an autonumber fields value once it is assigned (well, at least
I don't know how to do it).

Once you've moved all the records over using an append query, you can rename the
old table (temporarily, for safety) and then give the new table the old table's name.