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.

 

[Clifford Bass via AccessMonster.com]

Hi,

Here is one way. Make a backup (or two) of your database. Copy the
table and paste just the structure in the database. Change your primary key
column in the new table to be an AutoNumber. Create an append query that
appends all of the records from current table into the new table. This will
preserve your current primary key values. When adding new records in the
future it will automatically start with the next value after the current
highest value. Delete your old table and rename the new one to be old
table's name. Recreate your relationships.

Hope that helps,

Clifford Bass

 

[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.