Some information on what 'Compact and Repair' actually does

[gabrielcrowe]

Just for reference, from Microsoft themselves:

Running the Compact 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/surges, and so on are resolved.

If a primary key exists in the table, compacting re-stores 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.

[jont]

Gabe, spoiler.

I was told little byte Pixies danced over the database arranging them into a beautiful place to live safe from the ravages of the Corruptors - evil monsters that like nothing more than to mess with the Pixies work.

[Darren B]

OOOOOOKKKKKKKKKK

I think its time for a holiday

[Mike Hughes]

Hmm. That looks like just the 'compact' bit, what about the 'and repair'?

Mike

[leehack]

I'm yet to find a primary key on my travels.

[pinbrook]

Hmm. That looks like just the 'compact' bit, what about the 'and repair'?

Mike

compact is for developers, repair is for users

[Mike Hughes]

Ah. Still in the dark I'm afraid.

To summarise gabe's post 'compact' makes the database smaller, neater and faster.

I'm just wondering what the 'repair' bit does to fix broken stuff.

Mike

[gabrielcrowe]

The 'repair' part is the re-indexing the auto incrementing IDs.

A database would not know how to repair errors in its data, only problems with computer generated content, that has perhaps lost sync.

'compact' *should* take all parts of the database spread across the disc (if its fragmented) and collate it to a single contiguous file.

This in effect, reduces the amount of disc space that it takes up, a little.

Sometimes, access keeps daft caches of data. For example: take a db of 100mb and delete all but one table. occasionally, after you quit, its still 100mb. After a jolly good compact, the db engine will discard the deleted data. this will then reflect in the file size. This was true in only some cases, but it can happen.

[gabrielcrowe]

oh, and jont?

You're a nut. Get some help feller.

[Mike Hughes]

I prefer the description here: http://www.datarecovery.com.sg/data_...s_database.htm

Which seems to be saying that there are certain error conditions / corruption of the database that can be identified and corrected. An example being an uncleared write operation flag.

Mike

[pnagames]

Compact as Gabe says shrinks the Database to a more reasonable size

however that is not always the case. e.g. i have one database with only links to SQL tables on the server and it still remains at 370MB size... (tried compacting several times)

Repair ... i havent seen work to date. normally if my databases has crashed they are beyond repair and i have to restore them from the previous day's backup (pain if you have done loads of work on them)

[chris ashdown]

Jonty's description is far and away the most acurate answer to the question, the others are just guesswork

Hey these Norfolk mushrooms are great

[malbro]

'compact' *should* take all parts of the database spread across the disc (if its fragmented) and collate it to a single contiguous file.
.

According to Microsoft the size of the file that is reported is the actual size not the size on the disc so in theory defragmenting should not affect the file size that is reported. The amount of disc space used by a file depends on the cluster size in use on the specific disc, the larger the disc size the larger the cluster which is the smallest disc space that can be used. So on a large Gigbyte hard drive a 1byte file can take up 32kbytes of disc space. A file has to take at least one cluster which it fills as it grows until it needs more space than it is using in which case another cluster is added to the file space. The number of clusters is a fixed value which depends on the file system and the operating system so as the disc sizes get bigger the cluster (the minimum allocation) gets bigger.

Access databases do not delete space used by a record when it is deleted nor do they reuse the space so if you add and delete information in Actinic the database will grow, compacting it removes all the deleted records so the file size will decrease if there are deleted records in the database prior to compacting the database.