Changing primary key to autonumber

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jan H. DeGiorgio - 26 Dec 2003 22:32 GMT

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.

Reply to this Message

Ken Snell - 26 Dec 2003 23:45 GMT

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;

Signature

Ken Snell
<MS ACCESS MVP>

> 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
[quoted text clipped - 8 lines]
>
> Any help in figuring this out will be appreciated.

Reply to this Message

Jan H. DeGiorgio - 31 Dec 2003 15:06 GMT

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

> 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"
[quoted text clipped - 17 lines]
> >
> > Any help in figuring this out will be appreciated.

Reply to this Message

Ken Snell - 31 Dec 2003 18:21 GMT

Answers inline....

Signature

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
> table.

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;

> Thanks for your help
> > Was the child table set up with a relationship to the original primary
[quoted text clipped - 24 lines]
> > >
> > > Any help in figuring this out will be appreciated.

Reply to this Message