Compact and Repair

[dhstein]

I often run a compact and repair on the database. When I did it today, later
on in the day there was an error - that the table may be corrupt. I restored
from a backup, but if I compact the backup I get the same problem. So the
database seems to be OK right now - but I can't compact and repair. Any
suggestions as to how to proceed ? Thanks for any help on this.

 

[Al Campagna]

dhstein,
No guarantees but... you could create a new blank database, and then
import all your objects from the old db to the new.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[John W. Vinson]

I often run a compact and repair on the database. When I did it today, later
on in the day there was an error - that the table may be corrupt. I restored
from a backup, but if I compact the backup I get the same problem. So the
database seems to be OK right now - but I can't compact and repair. Any
suggestions as to how to proceed ? Thanks for any help on this.

See Tony Toews' corruption FAQ:

http://www.granite.ab.ca/access/corruptmdbs.htm

PRESERVE YOUR BACKUPS!!! You may need them; some corruption cannot be cured.

You may want to create a new, empty database and import everything from your
current working copy (to get a clean set of system tables). Import all the
tables first, and compact (to see if the problem still occurs). If not, import
the other objects.

If this is a shared database, do split it (see Tony's discussion on the
subject).

 

[dhstein]

Al, John,

Thanks for the advice. How do you import the tables ? Do you have to
export first ? Import one at a time ? I have a lot of tables and a lot of
forms. Can the forms be imported ? What about the VBA code ? Thanks.

David

 

[Al Campagna]

dhstein,
From the new empty db, select File/Get External Data/Import to open
up a browse dialog box.
Browse to, and select, your old db and then select all the tables
(following John's suggestion)
After importing all the tables, try a compact and repair. If OK, go
back and get all the forms.
Compact & Repair... if OK etc... etc... till all the objects from the
old db are imported.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[John W. Vinson]

dhstein,
From the new empty db, select File/Get External Data/Import to open
up a browse dialog box.
Browse to, and select, your old db and then select all the tables
(following John's suggestion)
After importing all the tables, try a compact and repair. If OK, go
back and get all the forms.
Compact & Repair... if OK etc... etc... till all the objects from the
old db are imported.

It doesn't appear to be the case here, but in my experience there's often one
form or report (more often a form) with a corrupt VBA module. In such cases it
may be necessary to import forms one or a few at a time, compiling the VBA
project after each import, compacting after each compile; if there's one form
that breaks it, delete that database and use the backup you made before the
last import (you DID make backups before each import... right!?), and recreate
that form from scratch.

 

[dhstein]

Al and John,

Thanks again for your help with this. Let me run this past you for your
advice. This is a split database for which the users modify the data every
day. I do have backups of both the backend and the front end for every day.
Yesterday (on a different computer) I tested an older front end and a copy of
the back end and successfully compacted the database. Since I make all the
changes to forms and VBA code, is it correct to assume that the problem is
with the front end? That would make things much easier since I don't have to
worry about the tables and I can just recreate the recent changes on the
front end.

David

 

[BruceM]

If you are using a shared front end, you should not be. Distribute the FE
to each user so that everybody has their own copy of the FE.

This is in addition to other suggestions you have received. I don't see
that this has been mentioned yet, so I am adding it now. If you use an oder
copy of the FE with no problems you can use that FE copy as your baseline
copy. Import the forms, etc. one or a few at a time. If you can identify
the form (or report or module) causing the problem, you have the baseline
copy of the FE from which you can import the troublesome object.

 

[Al Campagna]

Hi John,

It doesn't appear to be the case here,

Not sure what part of my reply you're referring to.

Since I now know that the database is split, my example of doing table
importing first doesn't make sense... given that the separate data BE is
probably
not the source of corruption.

However, in the problem determination, I would still suggest importing
all
forms in one shot. If there's a C&R problem after that... then create a new
blank db, and start importing forms... a few at a time... to "drill down" to
the culprit.
To me, that seems to be a more efficient process.

Different strokes for different folks... :-D

 

[Al Campagna]

dhstein,
If you can C&R the current BE without a problem, then there's no
need to do anything with it. C&R your BE to be sure, but I suspect
the FE.

If you have a good backup FE, a few days old, and you aren't
going to lose hours of coding work you've done on the db since that date...
then just swap the FE out.

If you have a good backup FE, but will lose many hours of work,
then you'll have to try to save your current FE... using the suggested
processes.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[John W. Vinson]

Al and John,

Thanks again for your help with this. Let me run this past you for your
advice. This is a split database for which the users modify the data every
day. I do have backups of both the backend and the front end for every day.
Yesterday (on a different computer) I tested an older front end and a copy of
the back end and successfully compacted the database. Since I make all the
changes to forms and VBA code, is it correct to assume that the problem is
with the front end? That would make things much easier since I don't have to
worry about the tables and I can just recreate the recent changes on the
front end.

I agree: if you can C&R the backend with no problems (and, just to be sure,
open it directly and open each table to see if it has any problems) then the
problem is in the frontend.

If you have a working copy of the frontend, just trash the corrupt one and use
the working copy.

As noted, each user must have their OWN INDIVIDUAL COPY of the frontend. A
shared frontend is the worst of both worlds: you get the contention,
corruption risk and bad performance of a shared database AND the network
delays of a split database.

 

[David W. Fenton]

It doesn't appear to be the case here, but in my experience
there's often one form or report (more often a form) with a
corrupt VBA module.

A corrupt Access project (i.e., the VBA) will never prevent
compacting, because the corruption is internal to the data in the
BLOB field where the project is stored. Only Jet structural problems
could prevent a compact from completing, and only then because the
corruption is so bad that Jet can't recover a sufficient portion of
the structure and data.

 

[dhstein]

Thanks to all for the advice and suggestions. Just to clarify exactly the
problem: The users all have their own front ends. They are using the Access
runtime version which has been installed on each machine. The Database does
successfully C & R with both the older front end and the newer front end.
The problem occurs after the C & R has been done on the back end - then there
is a specific program that generates an error (I'm sorry I don't have the
specific error). But this only happens if the user is using the newer front
end - when I revert back to the older front end it works fine. So I was / am
looking for some corroboration that the problem must be in the front end. I
think that's the consensus - so thanks again for all the help.

David