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
