|
|
Access Articles:
Microsoft® Access Database Corruption
|
List
All Access Articles
Microsoft Access Database corruption can
manifest itself in many forms, the majority of
which become evident when you are presented
with an error when you open your database. Some
examples of these errors include:
- "Unrecognized database format" (When your
database was operating under your current
version of Access before)
- "Enter database password" (when none was
set)
- "The Microsoft Jet Database Engine cannot
open the file"
- "Visual Basic for Applications Project in
This Database Is Corrupt"
- "Unexpected Error 35012"
- An "Invalid Page Fault..." error (not
necessarily a corrupt database issue)
- This list goes on...
Other symptoms include being unable to
open a form, report or module objects, text
data in tables containing unusual characters,
unexpected database bloat, general
unpredictable behaviour and error
messages.
If you suspect that your database is corrupt
then the very first step that you should
take is to create a backup copy of the
database and store it in a safe place. Whether
or not you will be able to restore your
database depends on the level of corruption
your database has experienced.
Having created your backup the next step is to
try to repair the damage using Access itself.
This can be accomplished by starting up
Microsoft Access and cancelling the open
database dialog. Next select Tools -
Database Utilities - Compact and Repair
Database (In Access 97 this is just called
"Repair Database"). The "Database to Compact
From" dialog will now show and you should
browse to your damaged file, select it and then
enter a new name and select a location for the
repaired database to be created. If no errors
are reported after repairing the database then
you can open your new database file and verify
that the symptoms that you were experiencing
have been resolved and that all your data is
present and intact.
If the error was not resolved by this operation
then there are still a few more solutions to
try before giving up. The next step is to
create a new database and to import all your
database objects (tables, queries, forms,
macros and modules) into the new database file.
Start up Microsoft Access and this time select
the create "Blank Database" option and give it
an appropriate name. Now select File - Get
External Data - Import from the main menu
and locate your damaged file in the Import
dialog. A new "Import Objects" window will now
open with a tab for each type of database
object.
Figure 1
You can either try to import and
verify each database object individually or use
the "Select All" button for each object type.
Usually, but not always, if a corrupt object is
found then it will be skipped. If you select
the "Options" button then you will also be able
to import any custom menus and toolbars that
you created in your database. In many cases you
will be able to recover all of your database
objects this way. If you are unlucky then you
might lose some of your objects (which you
should hopefully be able to import from your
last successful backup), however if you are
really unlucky then you might not be able to
import any objects at all.
Another option open to you is to try
Microsoft's Jet Compact Utility which,
according to some reports, can on some
occasions recover damaged Access
databases.
Sometimes corruption will manifest itself in
database tables. This is commonly seen when a
field in a record contains unusual characters
or simply displays "#Error" (or you might be
receiving unusual errors when trying to view a
particular record). If this is the case then
you will have to scroll through the table and
deleted each record affected. Once all affected
records have been deleted you should remove any
relationships from the table and rename it.
Next, re-create the table and relationships and
then copy & paste the data from the
original table into the new one. After this is
complete, delete the original table and compact
the database. Finally, verify that the database
is operational and re-enter the records that
you had to delete. This type of corruption is
very common with memo fields and therefore
these should be used sparingly and, if
possible, not at all.
If your database contains forms, reports or
modules and the symptoms that you. are
experiencing involves being unable to open one
of these objects then you can try using the
undocumented "Decompile" feature. Click on the
windows "Start" button and select "Run..." .
Type in the following: "msaccess /decompile"
and Microsoft Access will start. Select "Open
an existing file", locate your database and
open it. After decompiling, remember to compact
and repair your database, compile it again and
check to see if the symptoms you were
experiencing have been resolved.
If the preceding steps have failed then it
might be possible to at least recover some if
not all of the data stored in your database.
This involves using Microsoft Query with Excel.
To begin, start up Microsoft Excel and select
Data - Get External Data - New Database
Query (In Excel 97 this is called "Create
New Query") form the main menu as shown in
Figure 2.
Figure 2
The "Choose Data Source" window
(Figure 3) will now show and you should scroll
down the list of data sources and select "MS
Access Database".
Figure 3
Click on the OK button and the
"Select Database" dialog (Figure 4) will show
allowing you to browse to your database file.
Figure 4
If MS Query is able to connect to
the database successfully then you will be
presented with Query Wizard which should
hopefully list your tables and saved queries.
Select the first table listed and all its
fields and follow the wizard instructions.
Figure 5
When prompted choose to "Return
Data to Microsoft Excel®" and accept the
default of $A$1 when asked where you want to
put the data. Repeat the process for each table
in the database and save it to a separate
workbook with the same name as the source
table. Once complete you will now be able to
restore your data to your last successful
backup of your database by opening it,
selecting File - Get External Data -
Import from the main menu and changing the
file type to "Microsoft Excel" and following
the instructions given by the Import
Spreadsheet Wizard. If you do not have a backup
then I'm afraid that you will have to recreate
the database tables, forms etc from scratch and
import the data in the saved workbooks into
your tables.
If you have been unsuccessful up to this point
then I'm afraid that your chances of recovering
your database without professional help are
bleak. If you wish to continue searching for
more assistance then I would suggest that you
visit the MS Access Support Center
and continue searching for alternative options.
Failing that then your last option is to try a
database recovery product such as AccessFIX for corrupt
Microsoft Access databases.
Finally, if you do manage to restore your
database then always remember to verify
that your data is intact before returning to
normal use.
Jamie Czernik BSc.
http://www.jamiessoftware.tk
(This article is provided "AS IS" with no
warranties or guarantees.) Further
Help:
* MS Access Support
Center
* AccessFIX
* www.accessdatabaserepair.com
|
Copyright Jamie
Czernik 2004 -
|
|
