When Bad Things Happen To Good Databases -- Recovering From Corruption
It's the beginning of a new year, and for many of us
that means resolutions. We've spent the holidays
eating, drinking and partying, and now we're realizing
that our merrymaking has not been merry for our
bodies. With a mind toward reforming our ways, we
make New Year's resolutions. In an attempt to get our
bodies back to a healthy state, we resolve to go to the
gym, walk more, and eat better.
In a similar way, Access databases can also become
corrupt, and when they do, it's not a pretty picture. This
month, we'll take a look at the symptoms of database
corruption and look at some helpful tools for recovery.
|Symptoms of Corruption
Sometimes when you're working with an Access
database you'll find that things start going wrong for
no discernible reason. The symptoms could range
from a report refusing to open, a query providing
inaccurate results, a control on a form no longer
working the way it should or maybe not even working
In the worst case scenario, the database might refuse
to open entirely.
|Causes of Corruption
A primary source of corruption to Access databases
are network interface cards (NIC). These little gizmos
help the computer talk to the network. Unfortunately
Access databases are like the canary in the coal
mine; if there's a problem with a network or with an
interface card, Access will be among the first
programs to show problems.
Another really common way to corrupt a database is to
improperly close Access by shutting down your
machine while the program is writing to a record. This
almost always causes difficulties when you try to open
the database again.
|The Easiest Way to Recover -- Compact and Repair
So what's the first step when you suspect you're
dealing with a corrupt database? While in the Access
application, you'll see a menu item called Tools on
the menu at the top of the screen. Under this Tools
menu select Database Utilities. From there, select
Compact and Repair.
Using this utility is the best place to start. Although it
low risk, it's a good idea to make a backup of the
database before you take this step. In versions of
Access from Access 97 and earlier, sometimes
running this routine would make matters worse rather
than better. From Access 2000 on I've heard no
reports of compact and repair causing a problem, but
better to be safe than sorry.
This step will fix many minor problems with the
database as well as crunching it down so that it takes
up less room on your hard drive. It does a number of
other things as well, but we'll review those on another
day. Some folks run this routine periodically to get a
more compact file; if your database grows quickly
taking this step can bring it back down to a more
|A More Powerful Tool -- Decompile
The decompile processes is significantly more
powerful than the Compact and Repair utility. It will fix
most sources of corruption; although it's a bit more
complicated to execute, it can be a real lifesaver. If you
try compact and repair and that doesn't solve the
problem then Decompile is your next logical step.
Typically when you start Access, you do so by opening
an existing file. Because Windows knows about
extensions, it will start Microsoft Access for you
automatically when you click on a database file.
Alternatively, you could open Access and then choose
to open a file from any folder using the File> Open
To utilize the Decompile tool however, you'll have to
use command language to give Access an explicit
order to start with the Decompile command
(remember typing strings of DOS commands after
prompts back in the olden days?) There are several
ways to do this, and I think the easiest method is as
Click the start menu button on bottom left-hand corner
of your desktop and select the Run option. Depending
upon which version of Windows you're running and
how it's configured, you may have to hunt a bit to find
the Run command.
Once you've selected Run you'll see a dialogue box
with an input field preceded by "Open:" You must input
the full path to Microsoft Access in this box along with
a request to run Decompile (see below for the
syntax). Although there are many ways you can figure
out the path to Access, I typically go to Windows
Explorer (My Computer) and navigate my way to the
hard drive > Program Files > Microsoft Office > the
folder for the version of Office you're running. Stop
when you see the Access icon in the window and then
copy the path that appears in the address bar. You
then have to add the Access command to the path
(msacess.exe , see the example below) Finally, you
have to follow the command with the parameter
preceded by a forward slash, followed by the
For me the command I use in the Open box is:
Notice that there are quotes around the path to
Access. These are required since there are spaces in
the path. If you omit the quotes you'll get an error
Once you have that properly typed this in, hit return
and Access will open just as it normally does. At this
point, open the database that you want to decompile.
The first database (file) you open is the one that will
be decompiled. The decompile process will reset the
database so that all the internal links within the
database are checked and reestablished. In reality,
what goes on behind the scenes is much more
complex, but we'll stop at the explanation that it "sets
Once you've run Decompile, you'll likely find that
otherwise crippling problems have been resolved and
your database should be up and running. At this point
you can just work with your database as you normally
|Conclusion and a Look Ahead
These two methods, Compact and Repair and
Decompile, are easy ways to recover corrupt
databases. I suggest running Compact and Repair
first and then Decompile if the database still doesn't
seem right. In most cases, these procedures will fix
typical problems. However, in certain circumstances
a database may be so corrupted that even these
approaches will not resolve the issues. When faced
with this situation, it's time to bring out the big guns.
We'll take a look at those next month.
|Trap of the Month - Dates in Queries Need Special Formatting
If you enter a date in the criteria of a query and don't do
anything special, you'll usually find that you get what
you expect because Access will add the # symbol as
part of the expression.
However, if you are building the date as part of a query
from code or you're using a parameter query and omit
the # symbol, you'll have a problem.
In order to compensate for this, make sure you add #
before and after the date when you feed a query from
code or through a parameter. If you do this, the date
should always be recognized and you'll get what you
Tip of the Month -- Access "Under the Hood"
This month, I gave your fairly involved process to work
with Access at a deeper level. If you're comfortable
working with code, there's an easy way to get to the
path to Access.
Once in Access, hit Control+G. This will take you to
what's called the immediate window where you can
feed Visual Basic commands directly to Access. In
that window, type the following:
When you hit return, you'll get the current path to
Access. You can use this as an input for your
decompile command as we discussed above.
What this essentially does is to make an inquiry to the
system to see where Access is installed.
It's a fairly esoteric command, and one that you might
want to play with if you're interested in that type of stuff
(or if you're a geek like me!)