If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Changing existing primary key to autonumber
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. |
#2
|
|||
|
|||
Changing existing primary key to autonumber
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. -- Build a little, test a little. "StageRight" wrote: 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. |
#3
|
|||
|
|||
Changing existing primary key to autonumber
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 StageRight wrote: 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. -- Message posted via http://www.accessmonster.com |
#4
|
|||
|
|||
Changing existing primary key to autonumber
StageRight wrote:
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. -- HTH, Bob Barrows |
Thread Tools | |
Display Modes | |
|
|