What do compact and repair do in Access 97?

[Guest_imported]

Can anyone give me some info or a link to a FAQ about compact and repair?

I have the following questions:
1) What does "repair" do?
2) Why do "compact" reduces the size of the db but "repair" increases it?
3) Do I need to tell the users to compact every now and then or do I just need to compact once when I finish the design of the database (before delivering the product to the user)
4) Is there any danger of loosing data from compacting or repairing?

 

[BNPMike]

Typically databases will try and reuse space on the page the find themselves on currently. If in fact there is not any reusable space they (again typically - I don't know specifically about Jet) go right to the end of used space. Thus deleted space builds up in your database and indexes. Compact pulls all the data out and loads it back in no space is wasted.

How this affects you depends on the volatility of your data. If you are an airline booking system your data is very active. If it's HR then may be it is relatively static.

As a guide for a transactional system, compact once per week. High transaction rate applications may need once per night.

Access gets a bad name for not being able to support many users or very much data. It's probably neglecting aspects like this that are responsible rather than Jet. mike.stephens@bnpparibas.com

 

[BNPMike]

Repair is used when the database has become corrupt. The usual problem is some failure mid-transaction. For example if you have added new data - created a new page say - but before the page lists have been updated the session has been lost, you will have your 'indexes' saying the data isn't there yet there is a page of data sitting in the database. Repair tries to read through its maps of the database (lists and indexes) comparing them with what pages actually exist. It may then be able to reconstruct things - to some extent.

Whether Repair changes the database size or loses data is irrelevant. You must use it if you have a problem. You must not ever use it otherwise.

Compact should not lose data. mike.stephens@bnpparibas.com