Changing Primary key to Autonumber in existing DB

[Sokan33]

MerbershipID (8 digits) is the common denominator between most tables in my
DB. It is the primary key in TableA with 1 to many relationship to TableB
and TableC. MembershipID is foreign key in the other tables.

I would like to convert the field from text to autonumber. How can I do
this without lossing the relationships between the tables?

With much gratitude!

 

[KARL DEWEY]

You will have to rebuild the the relationships between the tables. Backup
the entire database first so if all fails you can start over. You will
probably need to update your queries also.

Add the autonumber field to TableA. Add a number field - long interger to
the other tables. Run an update query on TableB and TableC to update the new
number field from TableA autonumber field.

Delete the relationship between TableA and TableB & TableC.

Change TableA primary key to the autonumber field. Set new relationships
for TableA to TableB & TableC.

Do not delete the MembershipID field in TableB & TableC. Make another
backup at this time so you now have three versions.

I would wait several months unless you really know your database in's and
out's.

 

[Sokan33]

Thanks Karl,
I will do exactly as you recommended. My DB is still in development so I'll
rather make the changes now. One thing that's not clear is autonumbering of
8 digits field. I set the field to autonumber but it is just populate the
fields from 1,2,3..... How can I make the first ID 30100001

 

[KARL DEWEY]

You would need to start from scratch (Nothing in the table at all) with just
the table structure and append the records.

This would change the sequence of action I gave you previously.

 

[KARL DEWEY]

I read a lot of post that say to not rely on autonumbering if you really want
sequenital numbers because as soon as a new record is create the next number
is used even if the record is not saved.

 

[Sokan33]

Thanks Joe & Karl,

I've changed my mind and I will follow your advice/instinct. Thanks for
steering me out of potential trouble.