Relationship/Referential Integrity

[Syma]

I have a relationship/referential integrity problem. I
have a primary table and a secondary table, both with a
common field. In the primay table, it is the primary
key. I have data in both tables now, 736 records worth.
But, I need to change the data in the primary key of the
primary table, and have the secondary table updated to
match that field. Unfortunatly, I cannot do so. It loses
the data in the secondary table. So, I try to change the
relationship so it inforces referential integrity. It
will not let me do so because it "violates referential
integrity rules." It says there are records in the
secondary table that are not related to records in the
primary table. How can I get around this? It appears too
late to inforce referential integrity. What else can I do
to make this happen??

 

[Lisa]

It's never too late to enforce referential integrity -
you'll have to look for the problem records in your
secondary table and fix them so the value in the common
field is a value that exists in the primary table. You
never want to leave data like that.

Try a Find Unmatched Query Wizard.

Once you have the data fixed, you can then turn on
referential integrity. You will then get the option to
check the box "Cascade Update Related Records". This will
allow you to change the value in the common field of your
primary table and Access will automatically change the
value in the secondary table for you.

Lisa

 

[Guest]

syma did you get this?

 

[Greg Kraushaar]

When you set enforced referential integrity MSAccess check the
existing tables to ensure that the rules are not already broken.
In your case, they are. You will need to identify the records in
violation and fix them before you enforce the rules. e.g. There can be
no nulls. Field types on both side of the join must be compatible

It is usually easier to place the rules on empty tables
That's why it is a good idea to plan your database structure in detail
before you even think about turning on the computer.
Don't sweat it though, we all get burnt at least once.

Try this.
Copy the structure (NOT data) of the tables to new tables
Set the rules on these new tables
Copy the data in your primary table (The ONE side of the relationship)
ot the new table

Copy the data in the secondary table (TheMany side). You will get
errors. Ignore these.

Do a comparison on the Old and new secondary tables. Look for records
in the old that didn't make it to the new table. You will need to fix
these records manually, or maybe just throw them away. They will
almost certainly not be valid.

Delete the old tables
Rename the New tables to the Old names.

HTH
Regards Greg Kraushaar
Wentworth Falls Australia
(Do not email - the reply address is a Spam spoofer)
(If you really must, remove all UCase and numbers)