Compact and repair your way to a trim database
Compact and repair your way to a trim database
—by Leesa A. Israel
Application:
Microsoft Access 97/2000/2002
Have you ever had a particular database that seems to run much slower than others? Does it seem to take forever to run a query or to open a table? You might immediately attribute the holdup to your hardware. Well, before you spend a lot of money to buy a new computer, you might want to check something else. The problem could be that your database needs to be compacted and repaired.
The Compact and Repair features in Access can help you do just that. Since Access 2000, the compact and repair processes have been com-bined into a single procedure that makes it even easier to keep your databases healthy. (In Access 97, compact and repair are two separate but still easy-to-use features.)
Preventative maintenance
In this article, we’ll explain what the Compact and Repair features do and why it’s important to compact and repair your Access databases on a regular basis. We’ll show you how to perform the maintenance yourself on both open and closed databases. Then we’ll show you how you can set an option so that Access will automatically compact and repair a database. (In Access 97, this option isn’t available.) Finally, we’ll discuss some troubleshooting questions to ask yourself if the compact and repair procedures aren’t work-ing. Let’s get started!
Why compact and repair?
When you delete information in an Access data-base, that disk space isn’t actually emptied. This causes disk space to be used inefficiently, which is called fragmentation. When you compact a da-tabase, a new copy of the file is created and Access rearranges how the file is stored on your hard disk. This reduces its file size and therefore the amount of storage space the file takes up on your hard drive or network. As a result, the da-tabase runs more efficiently.
For example, suppose you’re working in a ta-ble and you need to delete several of the records of employees who are in a division that was sold. You might have to delete the table itself. When you do, your database will no longer be using disk space in the most efficient manner because of the empty space left by your deletions. When you delete something in Access (whether it’s just a record or an object such as a form or report), Access doesn’t reclaim the space that was used by that data or object. Compacting the database fixes that problem and, as a result, your database will open objects and retrieve records faster.
Repairing a database fixes items that may have become corrupted when Access shut down unexpectedly. If you’re working on a database and suddenly your computer freezes and you have to close Access, there may be objects in the database that have been corrupted. Running a repair can fix those corrupted objects as well. Keep in mind that this process can also help cor-rect errors that result in a shared database that’s been exposed to network problems. If an Access database is behaving unpredictably, try com-pacting a
—by Leesa A. Israel
Application:
Microsoft Access 97/2000/2002
Have you ever had a particular database that seems to run much slower than others? Does it seem to take forever to run a query or to open a table? You might immediately attribute the holdup to your hardware. Well, before you spend a lot of money to buy a new computer, you might want to check something else. The problem could be that your database needs to be compacted and repaired.
The Compact and Repair features in Access can help you do just that. Since Access 2000, the compact and repair processes have been com-bined into a single procedure that makes it even easier to keep your databases healthy. (In Access 97, compact and repair are two separate but still easy-to-use features.)
Preventative maintenance
In this article, we’ll explain what the Compact and Repair features do and why it’s important to compact and repair your Access databases on a regular basis. We’ll show you how to perform the maintenance yourself on both open and closed databases. Then we’ll show you how you can set an option so that Access will automatically compact and repair a database. (In Access 97, this option isn’t available.) Finally, we’ll discuss some troubleshooting questions to ask yourself if the compact and repair procedures aren’t work-ing. Let’s get started!
Why compact and repair?
When you delete information in an Access data-base, that disk space isn’t actually emptied. This causes disk space to be used inefficiently, which is called fragmentation. When you compact a da-tabase, a new copy of the file is created and Access rearranges how the file is stored on your hard disk. This reduces its file size and therefore the amount of storage space the file takes up on your hard drive or network. As a result, the da-tabase runs more efficiently.
For example, suppose you’re working in a ta-ble and you need to delete several of the records of employees who are in a division that was sold. You might have to delete the table itself. When you do, your database will no longer be using disk space in the most efficient manner because of the empty space left by your deletions. When you delete something in Access (whether it’s just a record or an object such as a form or report), Access doesn’t reclaim the space that was used by that data or object. Compacting the database fixes that problem and, as a result, your database will open objects and retrieve records faster.
Repairing a database fixes items that may have become corrupted when Access shut down unexpectedly. If you’re working on a database and suddenly your computer freezes and you have to close Access, there may be objects in the database that have been corrupted. Running a repair can fix those corrupted objects as well. Keep in mind that this process can also help cor-rect errors that result in a shared database that’s been exposed to network problems. If an Access database is behaving unpredictably, try com-pacting a
