Relationships Broken ?

[Databaseguy]

OK, so about a week ago, I compacted and repaired a DB. This week, one of the users complains that when they change a field on the master (PK) part of a form, all of the records in the sub form "dissapear". Now, I knew what was happening immediately. The FK table wasn't updating with the PK, so there were no related records.

I go into the relationships window, and sure enough the fields that are supposed to be related are not. I attempt to set them, and Access tells me they already exsist.

I go into mSysRelationships and sure enough, there's entries for those tables and fields.

I have a backup, but the data is old. As a last resort, I'll copy the schema (including relationships) and re-populate the tables with current data.

Any idea on what happened ? Or a fix ? I really don't want to empty the tables and copy new data into them.

Thanks In Advance

 

[markgrizzle]

Sounds like a corrupted database. Periodically repair and compact to avoid this problem. For an immediate solution, try creating a blank database, then reimport all the objects from your existing db. Do them in groups. Tables first, then queries, etc.

 

[willir]

Databaseguy

A sad tale indeed. Being a network administrator, I have heard this too many times.

I guess the obvious question is if the database is stored on a server, does your system admin perform daily backups? If so, you are smiling again.

If not, you need to see how extensive the damage is with the data. Run queries against your tables -- specificaly look for records containing null values for the foreign key. I suspect you will find the primary keys are okay unless the database design is different than most. (By default in a properly designed table, the primary key has to be unique, and can not be null.)

Now you have to decide to "tweak or turf". If it is bad, and there is no easy solution for recovery, then restore. Nothing worse than fighting data integrity issues every day for months and months.

If you think you just need to tweak, either delete the bad records, or setup a "fix-it" routine. Basically, you want to enter the correct foreign key back into the table. Working in table view sometimes is quick and dirty if the data is fairly easy to fix, or setup a special join form. I have fixed this type of problem before where I used this approach. A combo box is tied to one table. If useful, a subform below this links the current record, and displays pertain info that helps you decide what it is. Have a second combo box on the other side linked to the other table. Again, a subform linked to this combo box may simplify identifying the record. Then decide how you want to assign the primary key of the one table as a foreign key to the second table. If you have a lot of records, make it as simple as possible. Using a command button works. But so will allowing the combo box to do the work behind the scenes, and it requires less mousing around.

NEXT, and this is key. Develope a backup plan. Something as simple as a shortcut on the deksotp to copy the file works. If the data is important, then look at a mor robust solution. For the Novell client, or on windows NT, 2000 and XP, you can schedule the task. I personally perfer storing the database on the network where daily backups are performed.


ALSO, why and how did the the problem happend in the first place? Do you think the repair and compact procedure messed things up? I have never seen this happen, but I have seen poor database designs mess things up as you described. I suppose it is possible that the "repair" process caused problems if the design had a problem -- this is just speculation.

For me, the designer used a key to link to one of two different tables in a one-to-many relationship when in fact they should have used a many-to-many relationship. The original design "seemed" to work but underneath, they were creating a mess.

So review the table design and check the relationships. Such things as ensuring "not null" for the foreign key can prevent this type of thing. Cascading updates and delete properties for the relationships should be reviewed as well.

And review the form design. A bit of coding can go along way to enforcing referencial integrity.

Lastly, is it possible some one edited the data on the table instead of using designed forms? If certain design structure is not used, this type of approach can be very dangerous.

Good luck. I have been there, and I feel for you.
Richard

 

[Databaseguy]

Thanks all fro your replies. I think what happened was someone was poking around and broke the clustered index (relationship). It's not my "baby". That is, I didn't write it.

To make a short story long, I was trying to set relationships for the three clustered fields one at a time. All I had to do was highlight all three, drag and drop.

[homer mode] DOPE!!! [/homer mode]

Jeeez, and I've been working with access since '95. :0