Solved: Edit primary key?

[amarsha4]

Hi,

I have a VB6 program centred around an Access database.

I have a Staff table with StaffNo as the primary key and a Meeting table with a composite primary key consisting of StaffNo, MeetingDate and MeetingTime. The relationship between the two tables is one to many (one member of staff can attend many meetings).

Now, I need it to be possible to change the StaffNo, but don't really know how to do it. Access will not allow one table's StaffNo to be changed without the other matching up.

I only use Access to store the information, all viewing/adding/editing is done through a stand-alone VB6 program

Hope someone can help.

I've actually been pondering the idea of removing all relationships in the database, since I control, through the VB program, what actions can and cannot be performed. Bad idea?

[CreganTur]

Now, I need it to be possible to change the StaffNo
Do you need to change these numbers as a one time thing, or are you needing a way to change the value of this field at any time?

I ask because Primary Keys, by definition, are not supposed to change. They are a constant value that you can use to uniquely identify records in a table. I can understand needing to make a change as a one time thing (in which case you turn off all restrictions and relationships, make your changes, and then re-establish them), but as far as being able to dynamically change your primary key value- this is bad design and will cause you great heacaches in the future.


I've actually been pondering the idea of removing all relationships in the database, since I control, through the VB program, what actions can and cannot be performed. Bad idea?
This is a very bad idea. Primary keys are in place to ensure that you have a unique value that you can use to identify records. Relationships are setup so you can quickly and accurately find data in another table that is related to your unique records. This is the very foundation of relational databases.

Without these things the data in your tables will quickly get out of control, to the point that it could eventually become impossible to find the data that you actually want.

[amarsha4]

Thanks again!

I'll just have to make sure the StaffNo is right first time then.

Thanks for your help.