I have a database where the designer linked tables by the Social Security
Number. I want to change that to an autonumber field in the names table and
a same name field in the related tables (type long)
Outside of an update query setting the number ID in the related tables, is
their another/better way to do this?
Not that I can think of. You'll need multiple queries, I'd guess, especially
if there is not yet an autonumber field or if there are additional linked
tables.
I'd do it in steps, working on a COPY of your database (not the production
instance.... shudder!!!)
1. Add the Autonumber filed to the names table. This might require creating a
new table and running an Append query to populate it.
2. Add a Long Integer foreign key field to each child table in design view.
3. Run update queries like
UPDATE [names] INNER JOIN [childtable1]
ON [names].SSN = [childtable1].SSN
SET childtable1.IDfield = names.IDfield;
for each child table.
4. Drop the relationships on SSN, establish new relationships between the new
ID fields
5. Delete the SSN fields from the child tables
6. Test everything: forms, reports, exports, etc.
7. Correct the inevitable errors (e.g. subforms depending on the SSN field
being available)
8. Put the new database into production
John W. Vinson [MVP]
