Changing primary key to AUTONUMBER on existing database ?

[Queenie]

I have an existing contact information database which consists of a number of tables, the main table has a primary key which is currently set to NUMBER.

There is a relationship between it and 2 other tables via that key (ContactID).

I would now like to change the primary key (ContactID) to AUTONUMBER, so that any new records added, have a number assigned automatically. I also need any newly created primary key number to be used in the child tables created as a result.

Is this possible ? (without renumbering my existing tables/records)

many thanks

 

[boblarson]

You can't change to autonumber when data is already there. You would have to add a new column, assign it's datatype as autonumber and then delete the old column and then rename the new column the old column name.

You can also do a seach here on Autonumber seed.

 

[Queenie]

Loses ContactID relation to Child table records ?

Thanks,

But unfortunately renumbering existing ContactIDs would mean losing all my relationships to the other tables, which have a one to many relationship.

Not sure what Autonumber seed is, but will start searching.

 

[boblarson]

Autonumbering seed is where you are able to get the autonumber to start from a particular number.

And, yes you are going to have a problem with your current data because there is no way to change a datatype to autonumber after there is data in the table. It won't let you.

 

[neileg]

Well there is a way, Bob. I'm not a proper programmer, so I only do dirty fixes!

Step 1: Backup, backup, backup your database
Step 2: Copy your existing table and paste the structure only back into your database
Step 3: Delete the PK field in the new copy
Step 4: Add a new autonumber PK field to the copy and give it the same name as the field you just deleted
Step 5: Append the data from your original table to the new copy. You will find that the autonumber field will accept the appended existing numbers and start autonumbering from the next record
Step 6: Delete the original table. Rename the copy to the name of the original table. Reconnect the table in the relationships window.
Step 7: Sit back and contemplate your wonderful work.

Managing the PK FK relationship is usually handled in a form/subform set up. It's not done at the table level.

 

[boblarson]

Good stuff to know.

 

[The_Doc_Man]

Though there is a way to skin this cat as asked ( MMMMMEEEEOOOOOWWWWWRRRRR ), you should not do so.

Autonumbers should NEVER EVER have meaning if you are going to use them. (Well, hardly ever.) The properties of autonumbers are not suitable for use as meaningful numbers like sequentially numbered contract identifiers. You need to be able to accept gaps in the numbers because you ARE going to have them. Not a question, not a guess. Autonumbers do that to you.

There are all sorts of topics you could find on this forum via SEARCH function (it's in the forum's menu bar). Look for "Incrementing" as a keyword and use your eyeball to filter out the things clearly not relevant.