Insert New Records in Ref Integrity Tables

[RichiHardie]

Hi All,

I hope somebody can help me on this.

I still use Access 97.

I have 4 tables that contain a vehicle registration number field as their primary key and have one-to-one referential integrity applied.

I want to add a new registration to all 4 tables from one query. How?

I've tried many permutations with no success, such as putting the registration on a new table and trying to apply this to the 4 RI tables. I keep getting ref intergrity violations. Obviously I could disable the ref integrity rules, update the tables and then reapply the rules, but this is not possible if I am going to make the database available to a user group. A new registration needs to be added seamlessly. By not having ref integrity could leave the DB in an inconsistent state.

Help! If what I want is not directly possible, then any work-round would be appreciated.

Thanks in anticipation

Richard

 

[neileg]

Please don't double post.

You should not have the same PK in more than one table. If you hold the value in another table it is a foreign key.

Registration number is a poor choice as a PK anyway. Since registrations can be changed, the same vehicle can have more than one registration in its life, and more than one vehicle can have the same registration at some point.

If you use a form/subform setup, you can handle your tables effectively.

 

[Geoff Convery]

If the tables have one-to-one relationships do you really need to have them as separate tables? There's only one record for each vehicle in each table so why not have just one large table with all the columns in it?