Does Compact and Repair defragment the database file. Does it do anything more?
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Repair reads through the entire database file - including system and hidden tables - to check the structure and corrects errors if possible by writing to the file itself.
Compact reads the same info but writes it to a new database file.
If errors are met, it asks for a repair of the file and exits.
If success, the orignal file is deleted and the new file is renamed to that of the original, effectively defragmenting the original file.
/gustav
In the performance department, besides defragmenting all the records and pages it:
1. May also re-write the records in PK sequence (depends on a registry setting).
2. Flags all queries as un-compiled (read un-costed), so they are costed the next time they are run.
3. Updates all database statistics.
I'm not sure if that was covered in the link that cap posted or not (I dodn't want to repeat something someone has already said/posted); everytime I click on it, IE 7.0 crashes (I'm sure it's a problem here) so I could not check.
JimD.
from the KB
http://support.microsoft.c
Compacting
Running the Compact and Repair Database utility within Microsoft Access can also improve the performance of the database. This utility makes a copy of the database file and, if it is fragmented, rearranges how the database file is stored on disk. When completed, the compacted database has reclaimed wasted space, and is usually smaller than the original. By compacting the database frequently, optimal performance of the database application is ensured, and page corruptions due to hardware problems, power failures or surges, and so on are resolved.
If a primary key exists in the table, compacting restores table records into their primary key order. This provides the equivalent of Non-maintained Clustered Indexes, and makes the read-ahead capabilities of the Microsoft Jet database engine much more efficient.
Compacting also updates the table statistics within the database that are used as Jet optimizes queries. These statistics can become outdated as data is added, manipulated, and deleted from the various tables. Query speed will be enhanced significantly, because they are now working with data that has been rewritten to the tables in contiguous pages. Scanning sequential pages is much faster than scanning fragmented pages. Queries are forced to recompile/optimize after each database compaction.
During compaction, you can use the original name for the compacted database file, or you can use a different name to create a separate file. If you use the same name and the database is compacted successfully, Microsoft Access automatically replaces the original file with the compacted version.
In Microsoft Access, you can set an option to automate this procedure. To do so, on the Tools menu, click Options, click the General tab, and the click to select the Compact on Close check box. This will automatically compact and repair the database as it is closed.
Limitations of Compacting
* For the Compact operation to succeed, you must have enough storage space on your hard disk for both the original and the compacted database.
* You cannot compact an open database. In a multiuser environment, the compact operation is not successful if another user has the database open.
Note In Microsoft Access, you can compact a database while it is open, as long as the database has been opened exclusively.
more on this link.
Description of the Compact and Repair Utility
http://support.microsoft.c
from the KB posted above
Compacting a database
When you compact a database, the compact process will reclaim unused space in a database that is created by object and record deletions. It does so by creating a new destination database and by copying each object in the old database to the new database. If you choose to compact the database into the original database name instead of to a new database, the compact process creates a temporary database, exports all the objects from the original database into the temporary database, removes the original database, and then renames the temporary database to the name of the original database.
The following is a list of actions that the compact process performs:
* It reorganizes the pages of a table so that they reside in adjacent database pages. This improves performance because the table is no longer fragmented across the database.
* It reclaims unused space that is created by object and record deletions. When objects or records are deleted from the database, the space they occupied is marked as available for new additions to the database. However, the size of the database never shrinks unless you compact the database. For databases in which objects and records are frequently added, deleted, and updated, you should compact frequently.
* It resets incrementing AutoNumber fields so that the next value allocated will be one more than the highest value in the remaining records. For example, if all the records in the database have been deleted, after you compact the database, the value in the AutoNumber field will be 1 when the next record is added. If the highest remaining AutoNumber value in the database is 50, after you compact the database, the value in the AutoNumber field will be 51 when the next record is added. Note that this is true even if records containing values higher than 50 were added previously but were deleted prior to compacting.
* It regenerates the table statistics that are used in the query optimization process. These statistics can become out-of-date over time. This typically happens if transactions are rolled back or if the database is not properly closed because of an unexpected power loss or because the computer is turned off before a program that is using Microsoft Jet has had a change to quit completely.
* It flags all queries so that they will be recompiled the next time that the query is run. This is important because database statistics can change and a previously compiled query may have an inaccurate query plan.
Repairing a database
The repair process tries to repair only the tables, the queries, and the indexes in the database. It does not try to repair damaged forms, reports, macros, or modules.
Business Accounts
Answer for Membership
by: capricorn1Posted on 2009-11-03 at 05:15:09ID: 25728568
from KB
Running the Compact and Repair Database utility within Microsoft Access can also improve the performance of the database. This utility makes a copy of the database file and, if it is fragmented, rearranges how the database file is stored on disk. ..
read the rest here
http://support.microsoft.c