Use of compact and repair

[RexesOperator]

This is a very general question. so I am not sure if it has been asked before.

I know that compact and repair can sometimes fix problems with the database.

Should it be done only when problems seem to be creeping in? Or should this be done on a regular basis - say once a week or once a month?

 

[KeithG]

It can be done as much as you want. It is good to compact your database because when you delete an object (forms, quiries, reports) you don't gain the space back until you compact the db.

 

[Lightwave]

If at all possible I use the compact on close facility built into Access. Just keeps things nice and tidy and I find my databases crash less. This can slow the closure of a database down especially if it is over a network but I think that is a small price to pay for a tighter application.

 

[Rabbie]

Just remember that you can only compact and repair a DB when there is no-one using it.

 

[Simon_MT]

Another good practice is to run msaccess.exe /decompile over your application database if VB is being used.

Simon

 

[rhett7660]

Just remember that you can only compact and repair a DB when there is no-one using it.

If you have one set up over a network and you have the compact and repair on close? What effects would one see on the db? Or will it even do it?

 

[Rabbie]

If you have one set up over a network and you have the compact and repair on close? What effects would one see on the db? Or will it even do it?

It will compact/repair when the last user logs out.

 

[rhett7660]

Perfect... I was hoping you would say that!

 

[boblarson]

But, make sure your users are patient. If it takes a while and they CTL+ALT+DEL while it is compacting, then you could wind up with a mess. That's why it's best to have frontend on the user's computer where their db compacts on close and it won't hose others. If you store the fe on the server and have it compact on close then it will potentially corrupt because of the compact on close if someone terminates it early.

 

[Banana]

Would that be prevented by displaying a dialog saying "Please wait while database is being compacted" when database is being shut down? (Not sure how I'd trigger this...)

 

[boblarson]

I wouldn't put it past anyone to do anything. I know users who just don't think, or read.

 

[Banana]

In that case, an API call to make it CTRL-ALT-DEL-proof?

 

[boblarson]

Even if you could do that, what's to stop someone from holding their power button in for 2 seconds? If they can't kill it one way, they're sure to kill it another.

 

[Banana]

Write an API to disable power button?

At which point, we'll get this.

 

[boblarson]

Yep, and we'd need to bolt the computers to the desk and make sure they can't pull the power cord . And if they actually had a legitimate reason to Ctl+ALT+DEL or Shut down because their system was locked up, they couldn't. hee hee hee

 

[Rabbie]

Remember its getting harder to make things idiot proof nowadays because idiots are getting smarter.

 

[doco]

Idiot Savant? or Savant Idiot?

 

[Ryuk]

Databases are only compacted and repaired when you open and close them interactively. Backend/linked databases that are not opened interactively must be compacted and repaired in another way. Compacting and repairing requires exclusive access to the database. For multi-user databases, you must ensure that no users have the database open prior to compacting and repairing.

The ‘Compact’ feature helps you to reduce the size of the database by eliminating the unused space. On the other hand, the ‘Repair’ function helps in fixing minor corruption issues in the database files. At times, the utility fails to fix the issues with the database file due to severe corruption/damage in the .accdb file or any other reason. When such a situation arises, you need to look for an alternate solution.

 

[Gasman]

@Ryuk,
Are you aware you are responding to 10 and 13 year old threads?