How to change primary key field & not lose relationships?

[may1hem]

Hi, I've created a database in Access, and now need to change the primary key in one of the main tables. How can I change this whilst keeping the link between related records?

There are 2 tables: tblDiskInfo, tblTracks. Here are the fields:

tblDiskInfo
- idBarcode
- Title
- Description

tblTracks
- idTrack
- fkDiskBarcode
- TrackNumber
- TrackName

Currently there is a relationship between idBarcode and fkDiskBarcode. What I need to do now is to insert an autonumber field into tblDiskInfo and create a new foreign key field in tblTracks to hold the related disk number.

How can i do this whilst ensuring that the actual disk record data that I've already input and the track record data stay related?

Thanks,
May

 

[BNPMike]

A common misconception of new users to Access is that the records are linked. They are never ever linked. Only when you run a query with a join does Jet set about connecting records from different tables. It does this afresh everytime and retains no record of what happenned last time. Curiously part of the reason is so people like you could do things like change keys without having your IT department re-build the entire application.

You can erase relationships (you don't need them anyway) and redeclare different ones, and immediately you are ready to go.

In your example your tracks will all be related to discs when you change your design. You won't lose anything.

 

[DanJR]

mayhem1,

you could use an update query. Relate the two tables based on your existing relationship, then update the new foreign key field based on your autonumber field. eg:
[tt]
UPDATE tblDiskInfo
INNER JOIN tblTracks ON tblDiskInfo.idBarCode =
tblTracks.fkDiskBarcode SET tblTracks.MyForeignKey =
[tblDiskInfo].[MyAutoNumber]
[/tt]
Once you have updated your new foreign key, you then update your relationships.

Cheers,
Dan