How do I fix a corrupt database

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

I'm sorry if I am appearing noobish, but I'm a C programmer that
inhereted access because I'm the only "computer guy" in the office. Now
I've found some problems that I fear maybe a corrupt database.... and I
haven't the faintest of clue where to start when it comes to fixing
this problem.

Here is what happen:

About a week ago the registrar came to me to ask if I could help her
add a record to her training database. She told me that her and her
fellow registrar had MS Access lock-up on them while they where both
editing records. We will call the records the were editing StudentA and
StudentB. And after the lockup Access would not let her add StudentB.

The query:

SELECT * FROM Student WHERE (((Student.SSN)="123-45-6789"));

Returns the record for StudentA. Since 123-45-6789 is StudentA's SSN
that is fine, but

SELECT * FROM Student WHERE (((Student.SSN)="987-65-4321"));

where 987-65-4321 is StudentB's SSN still returns the record for
StudentA.


I deleted both records by using the delete commands:

DELETE * FROM Student WHERE (((Student.SSN)="123-45-6789"));
DELETE * FROM Student WHERE (((Student.SSN)="987-65-4321"));

and then I re-added StudentA. But I still have the same problem. Only
now when I select for StudentB's SSN (who's record has not been
re-added).

SELECT * FROM Student WHERE (((Student.SSN)="987-65-4321"));

I get a record that returns with all feilds reading "#Deleted" (even
the SSN feild)


So this sounds corrupted to me, but I am not a database admin and I
haven't a clue where to begin.... Any Ideas?
 
Jeff wrote:
...
So this sounds corrupted to me, but I am not a database admin and I
haven't a clue where to begin.... Any Ideas?
Click to expand...

I have a couple of ideas. First make a copy of the database to a
different directory or with a different name.

Open the copy and under tools - utilities run the repair function. You
might want to follow up with the compress function. (note: Don't compress
the original file)

If that works, great. If not, and I don't really expect it to work,
come back and post a list of tables and their relationships. I suspect the
problem is in relationship integrity.

Access does not really delete data until it is compressed, so don't do
that to the original file yet. It is not easy to get those previously
deleted records back, but it can save one's behind occasionally.

Good Luck
 
Begin by making a copy of the database for safekeeping in case you do more
harm than good. Nothing like a good backup!

Open the problem database. Go to Tools, Database Utilities, Compact and
Repair. See if that fixes it.

Actually it might not be a corruption issue at all. A LOT depends on the
design of the database. It may have some convoluted way of creating primary
keys and now both records share the same. It may have some badly implemented
relationships between tables.

If you wish, I could look at the database. However I don't feel all that
comfortable with seeing SSANs so if you could change them first that would be
best. Zip it up and email to OCLV5200 at hotmail.com . I teach tonight so
probably won't be able to look at it until Friday night.
 
Jeff said:
I get a record that returns with all feilds reading "#Deleted" (even
the SSN feild)
So this sounds corrupted to me, but I am not a database admin and I
haven't a clue where to begin.... Any Ideas?
Click to expand...

Correct. That record is corrupted.

See the Damaged records within tables section in the To retrieve your
data from a Corrupt Microsoft Access MDB page at my website
http://www.granite.ab.ca/access/corruption/corruptrecords.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
Ok, thanks for all the advice guys, I'll try this stuff when I get to
work tomorrow and let you know how it turns out.
 
Back
Top