How do I fix a corrupt database

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?

re: How do I fix a corrupt database

Jeff

Certainly sounds like corruption. I assume you have a split database
(data in one file, queries, forms, modules etc in another). If this is
not the case set about splitting the database ASAP.

Work in the data file. Work on a COPY!!

I assume you've tried the repair and compact option on the tools menu.
If not try it (it probably won't help but it only takes a minute).

If what you have is data corruption you can probably get going again by
creating a new table which has the same fields as the problem table and
copying the data from the old table to the new ... BUT ... you'll have
to copy all the rows above the faulty row(s) and then all the rows
below the faulty row(s) while avoiding the faulty row. Copy and paste
will do it, but an append query with appropriate criteria would be
better. (If you don't understand what I mean here you'll have to spend
a bit of time on the learning curve, I'm afraid.)

You'll need to check whether the problem table has any relationships
set up with other tables. If it has you must note these and then
remove them. Delete the problem table and rename your copy table with
the original's name. Re-create any relationships.

Providing you only have one or two corrupt rows this will probably fix
things for you.

You really must start some kind of backup regime so that you can
recover from corruption. I've been working in Access for some 12 years
or so and have only had 2 corruption episodes on stuff I've written
(both due to power cuts). Having said that, I try to insist on good
quality networks and UPS etc, which is probably why the corruption
demons haven't bothered me much!

HTH

Jeff Bailey

re: How do I fix a corrupt database

It sounds like you have a front end database and a backend database.
The frontend is where the data manipulation takes place and the backend
is where the data is stored - the actual data tables. If more than one
person can manipulate data then each person probably has a front end
database application. And the data that is being manipulated by the
multiple people all resides in the same repository database - the
backend.

What you need to do is to locate where the backend database resides and
then open that database and compact/repair it for starters. You could do
the same with the frontend databases also. Just go to the Tools menu to
Database Utilities to Compact and Repair Database and click that.

To locate the backend database - if you don't know where it is, go to a
code module and add a New code module by clicking on the New tab. Once
inside the code module go to the view menu and click on "Immediate
Window". This is your Debug window. You just want to make sure it is
visible because this is where the location of your backend database will
be displayed with the following code. Here is the code you run:

Sub LocateBackend()
Dim DB As DAO.Database, tdf As DAO.TableDef
Set DB = CurrentDb
For Each tdf In DB.TableDefs
Debug.Print tdf.Connect
Next
End Sub

Copy this code into the code module and place the mouse cursor anywhere
inside the subroutine and then press the F5 key (at the top of the
keyboard - that is the Run key for subroutines). Then look at the
Immediate Window below. Whatever linked tables you have in the Frontend
database - their backend locations will be displayed in the Immediate
window with the subroutine above.

HTH

Rich

Rich

*** Sent via Developersdex http://www.developersdex.com ***

re: How do I fix a corrupt database

"Jeff" <jeffrey.anderson@gmail.com> wrote in
news:1138893089.692025.272610@g47g2000cwa.googlegr oups.com:
[color=blue]
> So this sounds corrupted to me, but I am not a database admin and
> I haven't a clue where to begin.... Any Ideas?[/color]

Sounds like a corrupted index.

Have you compacted the MDB with the data tables in it? If it's a
corrupted index, it will tell you that and rebuild the index, but
it's likely to lose records that were viewable before the compact in
table view (though not sortable). So, you'll want to keep a backup
copy of the data before you compact, so you can compare the number
of records in the student table before and after.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

re: How do I fix a corrupt database

Rich P <rpng123@aol.com> wrote in
news:cwqEf.32$cJ2.7284@news.uswest.net:
[color=blue]
> To locate the backend database[/color]

All you need to do is show hidden and system tables and look in
MSysObjects for the entries for the linked tables. The connect
string will indicate waht back end database is in use, without
needing to write any code.

You could also just pick a linked table and type this in the debug
window:

?CurrentDB.TableDefs("tblWhatever").Connect

and that should return the connect string for it. However, you'd
still want to check to see if there's more than one back end
involved (though that's relatively unusual).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

re: How do I fix a corrupt database

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.