Before I introduce you to this key utility that
can prevent an emergency technical support or computer consultant
call, you need to do a nightly verified and regularly tested backup
of any Microsoft Access databases at your company. If you
have a very low tolerance for data loss, you even may want to
back up more than once a day.
In Part III, we’ll look at all kinds of data
protection measures you can take to prevent expensive, devastating
database corruption. But, for the time being, backing up a Microsoft
Access database regularly should be a top priority.
Maintenance 101: Compacting and Repairing a Microsoft Access
Database
Relational databases run best when all the
tables are indexed properly, the way you can look up information in
a book quickly by referring to a properly designed index in the
back.
Microsoft Access does a decent job of keeping
these indexes up to date on its own. This alone is a huge
improvement over previous generations of relational database
programs. However, as various items in a database (tables, queries,
reports, forms, fields and records) are added and removed, space
builds up between data that makes using the database less efficient.
This dilemma is very similar in concept to the
way a hard drive becomes fragmented. To eliminate disk
fragmentation, you’d run a defragmentation utility. In Microsoft
Access, the process is called compacting.
Note:
Before running this utility program on a Microsoft
Access database, make sure all users are out of the
database, and that you have a current verified and
tested backup copy.
To run the Compact and Repair operation on a Microsoft Access 2000
database, go to the Tools menu, choose the Database Utilities
command, followed by the Compact and Repair Database command on the
submenu (Figure 3-9). In most cases, Microsoft Access takes care of
the rest, without complications.
The Compact and Repair Database command
in Microsoft Access can be used to optimize and repair a
database.
Tip:
If you want to see what kind of impact compacting has on the
size of your Microsoft Access database, open Windows
Explorer and look at the size of the .mdb file before and
after you run the utility program.
If your Microsoft Access database application is split between
two .mdb files, where one contains the data tables and the other
contains the front-end, you’ll need to run the Compact and Repair
process on each .mdb file separately.
Repair Access Databases Action Items
Do you use Microsoft Access databases?
Do you perform a verified, nightly backup
of your Microsoft Access databases? How often do you test restoring
the database to make sure your backup system is really working as
expected?
Would it be devastating to lose up to one
full day of work in a Microsoft Access database? If so, would you
consider running a verified backup of the Microsoft Access database
more than once daily?
Do you know how to compact your Microsoft
Access databases to make them run more efficiently?
Do you know how to repair a corrupted
Microsoft Access database?
Are any of your Microsoft Access databases split up between a
front-end and back-end .mdb file?
|
