Hi Michael,
My name is Amy Vargo. Thank you for using the Microsoft Access Newsgroups.
Although it can be difficult to pinpoint the exact cause of corruption,
there are a few definite known causes. I included more information about
corruption below and ways to try to repair damaged databases and prevent
corruption in the future.
Corruption - Causes, Recovery, and Prevention
========================================
CAUSES:
Database corruption does not follow a "typical" scenario. There are,
however, some
virtually guaranteed ways to corrupt an MDB. If you power down your PC or
"crash"
your PC, or, in the case of server-based databases (including peer-to-peer
systems)
crash the network server or drop the network connection, it's almost a
guarantee.
When the Netware servers are out of space Netware may purge caches prior to
writing
the data, which can corrupt open Access files. If you leave a server based
Access
application open on the workstation and the server administrator "kicks you
off the
net" and reboots the server, most likely your MDB is corrupt.
If you are using Terminal Server and the users disconnect, rather then log
off the
server, corruption could occur when the server is shut down. This is
similar to
pulling the power plug on you computer while you still have applications
running as
an orderly shutdown of the applications does not occur until the log off
process is
completed.
Sometimes we try to open a database file from an external (removable) media
such as
a diskette, CD, or ZIP type media. Due to the need for additional disk
space for
the LDB file and file expansion, opening database files from this type of
removable
media is prone to disk space errors, unavailability, and is, in general,
not as
reliable a storage media as a hard disk. Whenever possible, copy the file
to the
hard drive and process the file from there. When you are finished, copy the
file
back to the removable media.
Because Microsoft Jet is a file sharing database system, when using
Microsoft Jet
in a multi-user situation, multiple client processes are using file read,
write,
and locking operations on a shared file. Because multiple client processes
read and
write to the same file and Jet does not use a transaction log like more
advanced
database systems such as SQL Server, it is not possible to reliably prevent
database corruption. If you want a reliable 24x7 database system, Microsoft
recommends using a true client server database system that supports durable
transactions like Microsoft SQL Server.
RECOVERY:
1. The first thing I would do is make a backup copies of the corrupt
database and
only try the recovery attempts on one of the backup copies as any attempt
to
recover the database could render it useless for future attempts.
2. To recover a damaged database file, first try using Compact Database
from the
Tools menu, Database Utilities. In the Tools Menu, Options, General tab,
you can
select Compact on Close for the currently open database.
3. When we suspect that the database file may have become damaged, we can
import
the contents of that file into a new database. If the problem was caused by
damage
to the database structure, this method will frequently allow us to recover
the data
and database controls from the original file into a new database file that
functions correctly.
To import an existing database file we first need to create a new blank
database.
From the File menu, select New and choose database. At this point you may
want to
name the new database file differently from the original so that the two
files have
unique names.
From the File menu, choose Get External Data, Import, and select the
original
database file. When the file is opened, click the Select All button on each
of the
tabs in the Import Objects dialog box. This will import all of the Tables,
Queries,
Forms, etc. from the original database into the new database.
If your database contains custom menus, toolbars, or import/export
specifications,
use the Options button to select these items.
If Microsoft Access stops the import at any point, it has probably hit a
damaged
database object. You will then need to import the objects in that group
one-by-one, instead of selecting the whole group. Microsoft Access will
not allow
damaged objects to be imported into a new database, so you will need to
re-create
any objects you are unable to import.
Once the Import is completed you will have a copy of the objects from the
original
database in a new database structure and you are ready to use the new
database.
This technique is also worth trying when a database file has been converted
from a
previous version of Access and does not work as expected.
4. Sometimes a file that does not open on one machine will open on another.
5. Corrupted Form Objects - In Access 97, we can sometimes remove
corruption from a
Form by using the undocumented SaveAsText and LoadFromText Functions. This
technique assumes Access 97 is available on the computer.
1. Open the database in Access 97.
2. Press CTRL+G to open the Debug window.
3. Type the following line and then press ENTER
SaveAsText acForm, "<FormName>", "<FileName>"
where <FormName> is the name of the form that is corrupt and <FileName> is
the path
and name of the text file you want to save to.
4. Rename the corrupted form.
5. Type the following line and then press ENTER
LoadFromText acForm, "<FormName>", "<FileName>"
where <FormName> is the name you want the form to have and <FileName> is
the path
and name of the text file you want to import from.
5. Corrupted VBA Modules - When a Report or Form appears to be corrupt it
may be
due to a damaged Class Module associated with the form.
To fix this problem, in original Database open the damaged report in design
view.
Open the properties of the report and set the Has Module Property to No.
This will
remove this corrupt Module from the project. The Has Module Property is
located at
the bottom of the "Other" tab on the properties dialog. Close and save the
report.
If the corruption involves being unable to open the class module of a form
or a
report, or the design view of a module, you may want to try the /decompile
command
line switch. It is strongly recommended that we make a back-up copy of the
database
before attempting this method. The following is a sample command with the
/decompile switch, wherein MSAccess.exe is in its default location and the
database
DB1.mdb is in the C:\ folder:
"C:\Program Files\Microsoft Office\Office\MSAccess.exe /decompile
C:\DB1.mdb"
(without the quotation marks)
After running this command from the Run box, you are prompted to compile
the
database after using /decompile.
6. Access 2000 VBA Modules - In some cases, a VBA Module becomes corrupt in
such a
way that the module cannot be located in the database window or deleted
from the
database.
If this should occur, open the VBA editor from the database window
(alt-F11) and
select the module in the Project Explorer. We can now Export the module by
clicking
on File, Export Module. Make a note of where we export the module.
We can now Close this database and create a new one. Import all the objects
from
the original database expect for the offending Module.
Note: we may receive errors for the linked tables if they cannot be found
during
the importing.
In the new database, open the VBA Editor from the database window (Alt -
F11),
Import the module we previously exported, and save it by selecting Save
Project
from the file menu.
These steps should remove any corruption from the VBA project in the new
database.
In addition, it will remove the module that could not be deleted, and
should allow
us to see the new module in the database window.
7. There is sample code in the JET35SP3.exe ReadMe File, which can be
modified to
apply to both Access 97 and Access 2000. This code provides the possibility
to
SOMETIMES recover some, if not all data in the database tables. To obtain
this
code, please see the following articles:
Q182867 ACC: Jet Database Engine 3.x Error Messages Due to Corruption
http://support.microsoft.com/support/kb/articles/q182/8/67.asp
8. Table Corruption - Sometimes we can extract table data when we cannot
open the
database file by importing the Access table data into in intermediary
application
like Excel, and then import the data from Excel into Access. In the
following steps
we are using MS Query in Excel to recover data corrupted database file:
1. Select Data\Get External Data.
2. Choose New Database Query.
3. At the Choose Data Source window, select Microsoft Access Database and
click
OK.
4. At the Select Database window, browse to the .MDB file.
5. At the Query Wizard window, select the table and columns.
6. A filter or a sort is not necessary, just click on Next and then on
Finish.
7. If the data can be read, it will display in the Microsoft Query window.
9. Troubleshooting Tools - There are methods we can use to determine which
users
and/or workstations are causing JET to mark a database as suspect:
When JET begins a write operation, it sets a flag, and resets the flag when
the
operation is complete. If a write operation is interrupted (such as with a
power or
network failure, the flag remains set. When you reopen the database, JET
determines
that the flag is set and reports the database as corrupt. In these cases,
compacting and/or repairing the database can typically restore the database.
In JET 4.0 you can use the JET UserRoster utility to determine which
users/workstations may be causing the problem. The UserRoster utility which
is
available as a VBA code sample in Knowledge Base Article:
Q198755 ACC2000: Checking Who Logged into Database with Jet UserRoster
<
http://support.microsoft.com/support/kb/articles/q198/7/55.asp>
10. Repairing Databases - You might want to review the Knowledge Base
articles on
repairing a database file:
Q109953: How to Repair a Damaged Database File
http://Support.Microsoft.Com/Support/Kb/Articles/Q109/9/53.ASP
Q279334 - ACC97: How to Repair a Damaged Jet 3.5 Database
http://support.microsoft.com/support/kb/articles/q279/3/34.asp
Q209137 - ACC2000: How to Troubleshoot/Repair a Damaged Jet 4.0 Database
http://support.microsoft.com/support/kb/articles/q209/1/37.asp
11. Other Recovery Options - PK Solutions is a Third-Party organization
that
specializes in providing Access applications and recovery of damaged Access
database files. They are available on the Internet at:
http://www.pksolutions.com
This response contains a reference to a third party World Wide Web site.
You
should know that third party sites are not under the control of Microsoft.
Accordingly, Microsoft can make no representation concerning the content of
these
sites. Microsoft is providing this information only as a convenience to
you: this
is to inform you that Microsoft has not tested any software or information
found on
these sites and therefore cannot make any representations regarding the
quality,
safety, or suitability of any software or information found there. There
are
inherent dangers in the use of any software found on the Internet, and
Microsoft
cautions you to make sure that you completely understand the risk before
retrieving
any software on the Internet.
12. If all else fails our only option may be to use a backup copy of the
database
or recreate the database from the original source documents.
PREVENTION:
Here are several precautions that we recommended to reduce the risk of
corruption:
1. Avoid power loss during database writes, which can cause the database to
be left
in a suspect state.
2. Avoid dropping network connections.
3. Avoid abnormal termination of Microsoft Jet. Examples include: Power
Loss,
Rebooting, or using Task Manager to shutdown the application, etc.
4. When programming close all DAO objects that you open. Examples include
Recordset, QueryDef, TableDef, and Database objects.
5. Do not run IPX on NT 3.51 Server where Jet databases are located across
the
network and the client is Win95 with IPX/SPX. Instead run TCP-IP on the NT
Server
and a dual protocol stack of IPX and TCP-IP on the Win95 client. (NT to NT
with
IPX/SPX will not cause the problem, nor will Novell to any client.)
6. Avoid large number of Open/Close operations in a loop (over
approximately 40,000
successive operations).
7. Compacting the Database File - Here is a list of the benefits of running
CompactDatabase often:
Note: It's highly recommended that databases are compacted often, daily if
possible.
- Reclaims disk space created by object and record deletions. When objects
or
records are deleted from the database, the space they occupied is marked as
available for new additions to the database. However, the size of the
database
never shrinks unless the database is compacted.
- Reorganizes a table's pages so they reside in adjacent database pages.
This
improves performance because the table data is sequential, allowing for
better use
of the read ahead cache.
- Resets incrementing AutoNumber fields so the next value allocated will be
one
more than the highest value in the remaining records.
- Regenerates the table statistics for the indices, which the Query
optimizer
depends heavily on these for optimal query plans. These statistics can
become
out-of-date over time, typically if transactions were rolled back, or if
the
database was abnormally terminated.
- Flags all queries so that they will be recompiled the next time the query
is
executed. This is important because database statistics can change and a
previously
compiled query may have an inaccurate query plan. For example a database
may
greatly increase or decrease in size.
In addition to compacting the database on a regular basis, the most current
update
of JET 4.0 contains modifications to help reduce database size and is
available for
download by referencing article Q239114 ACC2000: Updated Version of
Microsoft Jet
4.0 Available at:
http://support.microsoft.com/support/kb/articles/q239/1/14.asp
8. Compacting the Workgroup File:
To Compact or Repair the System.mdw workgroup file, I would first make a
copy of
the file to a different folder on the hard drive. Open Access and from the
File
Menu, Open, select Files of Type as Workgroup Files and navigate to the
copy of the
System.mdw you just made.
The default location for the System.mdw file is:
Access 2000 - C:\Program Files\Microsoft Office\Office
Access 97 - C:\Windows\System <or> C:\WINNT\System32
Access 95 - C:\Msoffice\Office
When you open the file, you may get messages indicating that some of the
system
file will be read only.
From the Tools Menu, Database Utilities, select Compact Database or Repair
Database. Unless you get errors, I would then use the Workgroup
Administrator
(Wrkgadm.exe) to temporarily connect to the new file and test thoroughly.
If this
resolves your problem, then rename the original System.mdw to System.old as
a
backup and copy the new file to the location where the original file was
located
and use the Workgroup Administrator to connect to the new file back in the
original
location.
8. Obtain the latest application product updates - JET, MDAC, Office and
MOD.
Access 97 - The Microsoft Jet Database Engine version 3.51 provides an
improved
Compact utility that combines Compact and Repair into a single process.
The Jet
database engine now generates an error if it encounters a problem during
the
Compact process and then creates the MSysCompactError table in the
destination
database. The Readme file includes sample code that allows you to easily
view rows
of data in which the Jet database engine found problems during the Compact
process.
For information on how to obtain the latest version of JET, please see
article
Q172733: Updated Version of Microsoft Jet 3.5 Available for Download,
available on
the internet at:
http://support.microsoft.com/support/kb/articles/q172/7/33.asp
For the newest revision of the JET Engine for Access 97, the Access 97 SR-2
update
is needed. Please see Article Q194377: Overview of the Updated Microsoft
Office 97
SR-2 Patch, available on the internet at:
http://support.microsoft.com/support/kb/articles/q194/3/77.asp
This article addresses how to upgrade Office to the SR-2 release. The
article
includes a brief history of Microsoft Office 97 SR-2 and discuses the
Microsoft
Office 97 Version Checker utility that analyzes your computer and
recommends a
course of action to complete the upgrade to SR-2.
Information on how to obtain the SR-2 update is available in Article
Q151261: How
to Obtain and Install MS Office 97 SR-2, available from the internet at:
<
http://support.microsoft.com/support/kb/articles/q151/2/61.asp>
Access 2000 - The most current update of JET 4.0 contains modifications to
help
reduce database bloat and is available for download by referencing article
Q239114
ACC2000: Updated Version of Microsoft Jet 4.0 Available at:
http://support.microsoft.com/support/kb/articles/q239/1/14.asp
The most current version (2.6) of Microsoft Data Access Components (MDAC)
is
available for download by referencing the Internet at:
http://www.microsoft.com/data/download.htm
The most current version of Office 2000 is the SR-1 Update with SP-2. For
additional information on other changes made by this update and how to
obtain the
update, please see the following articles:
Q245021 OFF2000: List of Fixed Problems in Microsoft Office 2000 SR-1
http://support.microsoft.com/support/kb/articles/q245/0/21.asp
Q245025 OFF2000: How to Obtain and Install Microsoft Office 2000 SR-1
http://support.microsoft.com/support/kb/articles/q245/0/25.asp
Q278942 OFF2000: What Is Office 2000 Service Pack 2 (SP-2)
http://support.microsoft.com/support/kb/articles/q278/9/42.asp
Q278269 OFF2000: How to Obtain and Install Office 2000 Service Pack 2
http://support.microsoft.com/support/kb/articles/q278/2/69.asp
To verify that we have a valid installation of the MDAC files we can use
the MDAC
Component Checker by referencing article:
Q231383 INFO: Component Checker: Diagnose and Reconfigure MDAC Installs
http://support.microsoft.com/support/kb/articles/q231/3/83.asp
For the SR-1 version of MOD Runtime Access please see:
Q253566 MOD2000: How to Obtain and Install MOD 2000 SR-1
<
http://support.microsoft.com/support/kb/articles/q253/5/66.asp>
10. File Server Networking - When sharing a Microsoft Jet database file on
a
network file server it is recommended that we disable opportunistic locking
(oplocks) on the network file server.
Microsoft has recently discovered an issue where opportunistic locking can
increase
the chances of database corruption when a Microsoft Jet database is shared
by 2 or
more clients on a network file server. This issue applies to Windows NT
4.0,
Windows 2000, and Novell file servers that support opportunistic locking.
This
issue is still under investigation.
To disable opportunistic locking on a Windows NT 4.0 or Windows 2000 file
server,
locate the following registry key on the network file server:
HKLM/System/CurrentControlSet/Services/LanmanServer/Parameters
(Note HKLM here stands for HKEY_LOCAL_MACHINE)
Under this key create a new DWORD value named EnableOplocks and set it's
value to 0
to disable opportunistic locking. Re-boot the network file server to insure
that
the setting is honored.
Ensure you have a robust file server that is dedicated to sharing the
Microsoft Jet
database file (and not also acting as a Windows Domain Controller, Exchange
Server,
SQL Server, etc...).
The reason for this recommendation is a network administrator may re-boot
the
machine to fix a problem with another deemed more important service (such
as the
mail server), or re-boot after applying a hotfix or service pack, and
forget that
your Microsoft Jet database is currently shared on the server. This will
cause
unexpected interruption of the client connections to the database, possibly
causing
database corruption.
Likewise, a robust file server should be placed in a secured location where
it
cannot be accidentally switched off. The server should have a UPS to
protect it
from intermittent power outages or power fluctuations. Likewise the network
file
server should have high performance hard drives, a good network card, and
plenty of
RAM to insure that the server is not overly taxed by the load placed on it.
Ensure you have a stable and fast network, and good solid network
connectivity to
the network file server.
Avoid using Microsoft Jet over a WAN, modem connection, ftp (or any other
less than
reliable network transport). Since Microsoft Jet is a file sharing database
system,
any less than reliable network transport increases the chances of a dropped
client,
which in turn increases the chance of database corruption.
Ensure that the network file server has the latest operating system service
pack
installed.
Installing the latest operating system service pack on the network file
server
ensures that you have the latest bug fixes for the network redirector and
file
system.
11. Backup your Microsoft Jet database file on a regular basis. Note that
you will
need to shut down all Microsoft Jet clients before backing up the Microsoft
Access
database file, failure to do so may result in the backup file having
inconsistent
data. Choose a backup schedule that corresponds to the most amount of data
you can
afford to lose. For example, if you can't afford to lose more than a days
worth of
data, backup on a daily basis. If you can afford losing a weeks worth of
data,
backup weekly, etc... A full database file backup is the only guaranteed
means of
insuring that you can recover your Microsoft Jet database file when
corruption
occurs.
12. Application Design - can play into this in a significant way depending
on the
nature of your application.
I hope this helps! If you have additional questions on this topic, please
respond back to this posting.
Regards,
Amy Vargo
Microsoft Access Engineer
This posting is provided 'AS IS' with no warranties, and confers no rights.
You assume all risk for your use. © 2001 Microsoft Corporation. All rights
reserved.
