Is Repair and Compact sequence important?

[Vesh]

Hi all,

We have about 75 different clients accessing respective Access-97 *.mdb databases sporadically on a 24x7 basis. We're erring on the side of caution by doing a Repair, Compact, Repair sequence. We presume it won't hurt anything to do this, and will hopefully keep the databases from getting corrupted and keep the performance from degrading. Right now we do this daily, manually only on high-volume systems. The plan is to eventually do regular preventative Repair, Compact, Repair on all systems using an automated tool.

The automated tool we are considering only does Repair-and-Compact, period. The question is... does the R-C-R sequence buy us anything extra, or can we just do a R-C sequence and leave it at that? We’d prefer to use the commercial tool, unless we lose something critical by giving up the extra Repair in our present practice, in which case we’d rather bear the cost of doing it manually(or writing a home-grown utility to automate it).

(I know Microsoft Knowledge Base recommends NO “preventative” Repairs, but that only applies to Jet versions 3.0 and prior.)

We first decided to regularly perform the Repair, Compact, Repair sequence based on word that the R-C-R sequence was specifically a Microsoft tech’s recommendation. Can someone kindly help us confirm whether or not this -- in fact -- is true? We are planning to stay with Access-97 into next year, at least. We use Jet 3.5+ from Visual Basic applications over a file-sharing network.

I hope the post wasn’t too long. Thank you in advance for any help.

 

[gwinn7]

Yes, I can confirm Microsoft's assertion.

Repair and Compact has been regularly used in our office. However, Repair and Compact does not cure everything. This is a key point.

For example, especially if you are on a Windows 2000 Server environment, you may get the error message indicating that the record is locked by another user, when its not. In my experience, this means that there is a corrupted field that is of type MEMO. In the past couple of weeks we had to repair this field at least 4 times across several different records. Obviously, you can see the problem of cost of labor just to fix these problems, unless you have a utility that will do it for you automatically. Its something on my list to do's. Anyhow...

Because you have 75 users, I am guessing that you have a high volume of records. You may want to consider a DBMS system, especially if you are going to expand your operations.

Gary
gwinn7

 

[andrewvanbeck]

Also, just a quick note - Autonumber fields act strangely after repair and compact. You may find some (or even all) of your autonumber fields get reset.

Andrew.

 

[pantech]

Hi Vesh,
can you please tell me how do you manage the multisuer environement with access database at back end??What are the points to be noted to make a database in access and front end vb6 to make multiuser

 

[Jeremy1952]

What is the program you are using to automate the process? I only have about a dozen users but my database inflates ridiculously every night. Appreciate input.