Changing existing primary key to autonumber

[StageRight]

Hi there,

We have a database that has main and other tables linked based on primary
key. Initially having the primary key be a manual entry was useful, however,
it's now onerous to have to enter unique numbers. We'd like to 'change' the
primary key to an autonumber, but some very intricate queries are and
multiple tables rely on this key. Is there some methodology someone could
suggest for this?

Thanks very much.

 

[KARL DEWEY]

No magic if that is what you are looking for.

BACKUP DATABASE BACKUP DATABASE

Add new field for autonumber and make primary. Add a number - long integer
- to related tables.
Join other tables in a query and run an update query to fill new integer
fields of related tables.
Create new relationship. Test.

BACKUP second copy of database.

Deleted old primary and other unused fields. Test again.

 

[Bob Barrows]

Hi there,

We have a database that has main and other tables linked based on
primary key. Initially having the primary key be a manual entry was
useful, however, it's now onerous to have to enter unique numbers.
We'd like to 'change' the primary key to an autonumber, but some very
intricate queries are and multiple tables rely on this key. Is there
some methodology someone could suggest for this?

Copy the table to the clipboard and paste it back in, specifying
Structure Only, calling it temptable. Open temptable in Design View and
change the field to autonumber. Then create an append query to insert
the data from the original table into temptable. Then delete the
original table and rename temptable to the original name.