Problem with linked tables

[Lisa Z]

About two weeks ago, I discovered a problem in our database. There had been
a corruption that our IT dept. tried to correct by rebuilding one of the data
tables. In the process, the tech removed the linkage between that table
(Table A), which had a primary key defined on ID number, and the main data
table (Table B), which stores information on the various degree files we have
for each student.

I restored the linkage, or so I thought, last week. However, since that
time, when our assistant goes in to change addresses as students submit
address updates (Table A), we are discovering that the directory listing for
those students links incorrectly to another, totally unrelated student degree
record in the other table. (Table B) What would cause this error?

Other pertinent information... database is split... 3 users each have the
front end on their computers with back end on the Data server. Users all
utilize forms when reading or updating student information.

Any ideas on where I should look for the problem, or what I might have done
wrong in re-establishing the relationship?

 

[Larry Daugherty]

Hi Lisa,

Ask your oh, so helpful IT department to give you the last backup of your
data from before the problem surfaced. Re-enter all of the data you've
entered since then. There might be less tedious ways of recovering the data
but it doesn't sound like your IT department understands what the
"Relational" in a Relational Database Management System is. They've broken
the linkage between the Primary Key of the parent table to the Foreign key
in the child(ren) table(s). If you have more than a couple hundred child
records it simply is better to gather the paper input sources and do it over
than it would be to go back to the earlier point and take note of the keys
and then to go identify the mis-keyed child records. You don't really know
what IT changed and they may not fully recall now either. If Referential
integrity had been set is might have flagged the errors more quickly.
Rather than have someone get in and fiddle with the tables you're always
better off to first try the Compact and Repair operation. Most of the time,
that will work. If it doesn't then I'd go to the last backup. That should
be a clue to save all input material until the data has been in the database
a day or two. Don't buy anyone's bs about Access. If they are accomplished
developers they will understand the problems of fiddling with the data.
Lots of folks in IT think they know it all 'cause they know Oracle or DB2 or
you pick it. If they don't have direct experience of Access, they don't
know Access!

I hope you have daily backups of your data so that you can get within a day
of your last good entries. One of the banks I worked for came up with a
shocker. One of the users got into one of our small databases and screwed
it up. "No problem, IT backs up nightly"> We asked IT for the last backup
before the failure date. They gave us a backup later than that date. We
said, "No, before last Friday". They gave us another copy of what we
already had. We then asked them for the next backup before that one. They
gave us another copy.... You get the picture. In my own time at home I
wrote an application to backup, compress and restore the compressed copy for
use. I then took care of backing up all of my own applications.

HTH

 

[Lisa Z]

Ah, there is the rub...they do back up daily, but do not save old back ups.
There just happened to be a back up that was made by them when they had tried
to restore the tables, but it apparently still had a corruption in it,
because as soon as we opened the database and tried to check on the first
record, we were told, "Record is deleted." Now, I am looking at having to
rebuild the entire database from the ground up. Is there perchance any
miracle way to import the data from the current database without also
importing the corruption??? Or are we going to have to go through all 4000+
of our records and input that, as well? I am prepared for the worst case
scenario at this point!

 

[Larry Daugherty]

Hi Lisa,

There may be various ways to recover but it is highly dependent on how
quickly you got your guard up and started protecting yourself from IT. If
you have a copy of the database that they didn't fix there may be hope.
However it they did their wonderful fixes and then backed up their fixes and
you have no backups your then they've quite actively dumped you into deep
do-do.

I'm assuming the worst: that they did their "fix" and that you have been
entering data on top of that mess. That's how it sounded in your original
post. If that is the case, there is no magic fix from anyone that can save
your data. Even worse, you probably have no backup from a pre-corruption
level. That's something you might check. Any IT dept worth receiving a
salary will back up everything and push a copy off the servers into archival
storage. If they have done that, That will probably be your only method of
full recovery. I'd guess a lot of your source documentation has gone by the
board. If you can get back to that archived copy (first, be sure it isn't
just another copy of the garbage IT has already been giving you) and if you
can re-enter the data since then, you have a hope.

This whole episode is worthy of a complete written report to upper
management. Detail how much of your data is now unreliable and what it will
cost to restore it to the extent possible. Upper management responds to
data corruption and the loss of money. Just a verbal report gets swept
aside and IT denies all. IT has boldly moved into an area of which they
were ignorant and made a small problem into a huge one. The problem is
compounded by the fact that your IT dept does as so many IT depts. do:
Loudly advertise that they take a nightly backup. They deliberately imply
that you have a backup for each day. The reality is that they take that
nightly backup but it over-writes the next newest backup such that you only
ever have one (yesterday's) backup.

Please post back to let us know how you're doing with it. Please indicate
your version of Access and the Operating System(s) involved and how your
application is deployed. Given that this is unlikely to involve Access so
much as hard work, feel free to email me at the fairly simply encoded email
address.

HTH