Changing primary key to autonumber

[Jan H. DeGiorgio]

I have a db I've been using for ten years that uses a compound (date and
text) primary key which I'd like to change to autonumber. Although, by
appending my records to a new table with the correct structure, I've been
able to set up the first table ok I'm not having any luck setting up the
child table and establishing a relationship.

I thought I'd read that I could do this by setting up a long integer field
in the second table and then establishing the relationship between the two
tables but this doesn't seem to be working. I've tried a couple of other
approaches with no luck.

Any help in figuring this out will be appreciated.

 

[Ken Snell]

Was the child table set up with a relationship to the original primary key?
If yes, then you can run an update query to populate the new "long integer"
field in the child table with the correct autonumber value from the first
table. An SQL statement similar to this should work:

UPDATE ChildTableName INNER JOIN ParentTableName
ON ChildTableName.OldKeyFieldName = ParentTableName.OldPrimaryKeyFieldName
SET ChildTableName.NewKeyFieldName = ParentTableName.PrimaryKeyFieldName;

 

[Jan H. DeGiorgio]

Ken,

Forgive the greenhorn questions but.

1. Do I need to maintain the old relationship when I'm populating the child
table.

2. Since the old relationship uses a composite key is the SQL syntax
.....ParentTableName.OldPrimaryKeyFieldName1 and OldPrimaryKeyFieldName2?

Thanks for your help

 

[Ken Snell]

Answers inline....


--
Ken Snell
<MS ACCESS MVP>

Ken,

Forgive the greenhorn questions but.

1. Do I need to maintain the old relationship when I'm populating the child

No.



2. Since the old relationship uses a composite key is the SQL syntax
....ParentTableName.OldPrimaryKeyFieldName1 and OldPrimaryKeyFieldName2?

The SQL would look something like this:

UPDATE ChildTableName INNER JOIN ParentTableName
ON ChildTableName.OldKeyFieldName1 = ParentTableName.OldPrimaryKeyFieldName1
AND ChildTableName.OldKeyFieldName2 =
ParentTableName.OldPrimaryKeyFieldName2
SET ChildTableName.NewKeyFieldName = ParentTableName.PrimaryKeyFieldName;