Help! My Database is Corrupted, and I Can't Repair!

As a follow up to my recent series on compacting databases, I thought I should say a word about the repairing part of Compact and Repair.

As a file-based database system, Access is more susceptible to corruption than a server-based database like SQL Server.  However, Access databases are not quite as fragile as many believe. The most common causes are external to Access: PC hardware problems, network problems, users shutting down Access, or killing the Access process when it is accessing the disk.  A few are internal: use of the Name Autocorrect feature, multiple users using the same file, or memo fields.  These last do not cause corruption, but corruption is more likely when used.

In my experience, most of the time a corrupt database can be fixed by Compact and Repair.  I personally have had only one database that was not.  That one happened when I lost network connection while I was compacting the database.

But although it doesn't happen often, Access databases can be corrupted beyond repair, or at least beyond the ability of Compact and Repair to fix.  In some cases, you've no alternative but to resort to your backup.  (You DO back up your database regularly, don't you?)  But there are things you can try before you have to resort to that.

The following links are some of the standard references for recovering a corrupt database.

Jerry Whittle's Fix Corrupt Access Database v4.5
Allen Browne's Corruption Tips
Tony Toew's Corrupt Microsoft Access MDBs FAQ

.

New Sample: Form_Resize

Form_Resize

by AD Tejpal

This sample db demonstrates resizing of access forms so as to suit current screen resolution. In addition, user has the option to carry out custom resizing - if desired.


Five styles of demo forms are included as follows:

(a) Simple controls - all free to float and resize.

(b) Simple controls - with certain controls having tag property settings for locking their position and / or size.

(c) Combo box and list box.

(d) Nested subforms - Continuous.

(e) Nested subforms - Datasheet.

(f) Tab control having (i) Nested subforms and (ii) Option group.

Tag property settings for steering the behavior of individual controls are as follows (more than one setting (separated by ;) can be included in the tag string):

(a) LockLeft: The control retains a fixed distance from left edge of the form.

(b) LockRight: The control retains a fixed distance from right edge of the form.

(c) LockTop: The control retains a fixed distance from top edge of the form.

(d) LockBottom: The control retains a fixed distance from bottom edge of the form.

(e) LockLeftRight: The control expands in such a manner that its left edge retains the original distance from left edge of the form, while its right edge retains the original distance from right edge of the form.

(f) LockTopBottom: The control expands in such a manner that its top edge retains the original distance from top edge of the form, while its bottom edge retains the original distance from bottom edge of the form.

(g) LockSize: The control retains its original size.

(h) LockWidth: The control retains its original width.

(i) LockHeight: The control retains its original height.

Demo forms have all been designed for a screen resolution of 800 x 600. On opening any of these forms, it resizes automatically to suit the screen resolution currently in force (if it happens to be different from the designed one).
 
You can find the sample here: Form_Resize


.

How Can I Compact my Access Database Less Often?

So far in this series, I have talked about what compacting is, how it works, and several ways to compact, both through the Access Interface and in VBA code.  However, one subject that's rarely discussed is how to reduce the need to compact the database in the first place.  The number one reason for compacting is due to "database bloat", where records are added, deleted, and the space is not recovered until compacted.  Reducing bloat will reduce the frequency of having to compact your database.

Here are some strategies:

Split the Database (FE/BE)
A split database is one in which the application (queries, forms, reports, macros, and code) are in a separate physical database file (known as the Front-End or FE) from the tables (which file is known as the Back-End or BE).  Since bloating affects the tables most, having them in their own file means that only the BE needs to be compacted.  As I showed last time, this can be initiated from the FE fairly easily.

There are other good reasons besides this to split your database, including improved multi-user access and less corruption.  Splitting the database is such a good idea, in fact, that I recommend it for almost every application.

Importing Data
Just as bloat is the number one reason for compacting, importing data is the number one reason for bloat. A common scenario is importing at text file to a temporary table, massaging or formatting the data in some way, appending it to the permanent table, then deleting the temp table.  As we've seen, while Access with grow the database dynamically, it will not automatically shrink it when the data is deleted.  If you import a lot of data this way, you can find yourself needing to compact often.  There are a couple of ways around this:

Linking Files
Instead of importing files to a temporary table, consider linking them.  You can link many kinds of files including external Access tables, Excel files, text files, or other external sources.  These linked tables do not contribute to bloat nearly as much as importing them, although as we'll see later, processing them may cause some database growth.  Of course any time you add records to your database, the file size will grow.  Nothing can be done about that.  But minimizing the amount of temporary data you import to your database will keep growth within normal bounds.

Importing to temp database
If you must import data to a table for some reason, consider creating a temporary database to store the data and link those tables into your database.  After you're done with the import process, the temporary database can be deleted. You an even create, populate, link, process, and delete temporary databases programmatically from your FE application.  On my website is a small sample database called ImportToTempDatabase.mdb which illustrates how.

Don't obsess - some "bloat" is okay
Not all bloat is bad.  Some of it is necessary.  Aside from inserting and deleting records, databases can grow due to internal processing.  When the database engine runs a query or a recordset based on a query, it has to process and store it somewhere, and that "somewhere" can cause your database to grow.  This growth is not a cause for concern.

If you find that immediately after compacting your database grows dynamically at first, but then slows to minimal growth, that first, dramatic growth is just Access creating the working space it needs.

.

How Can I Compact the Current Access Database in Code?

In my previous two posts: (What Does It Mean to Compact My Access Database? and How Do I Compact an Access Database?),  I discussed what compacting a database does and how to do it through the Access User Interface.  But it is also possible to use VBA code to do it as well.

Why would you want to use code?  Well, if you're making an application for others to use, you might want to give them a button to compact the database so they don't have to know the menu/ribbon selections.  You may also want to control when or how the compacting is done.

accDoDefaultAction Method
In Access 2003, it was possible compact the current database with the following code:

• CommandBars("Menu Bar"). _
  Controls("Tools"). _
  Controls("Database utilities"). _
  Controls("Compact and repair database..."). _
  accDoDefaultAction

In Access 2007 and 2010, however, this method no longer works.  The command will still execute (modified slightly--notice the new first line and the ellipsis missing from the end?)

• CommandBars("Menu Bar").Enabled = True
  CommandBars("Menu Bar"). _
  Controls("Tools"). _
  Controls("Database utilities"). _
  Controls("Compact and repair database"). _
  accDoDefaultAction

However, it will return with an error message: 
You cannot compact the open database by running a macro or Visual Basic code.

Well, that's not exactly true.

Send Keys Method
The Send Keys method is a way of sending keystrokes in code to the Access Interface.  Since the menu/ribbon is different depending on your context (and which version of Access you're using) the send keys argument can vary quite a bit.

First of all, you need to put the code in an Event Procedure behind a button on a form.  This will give us the form menu/ribbon context.

Access 2003: Sendkeys "%(TDC)", False
Access 2007: Sendkeys "%(FMC)", False
Access 2010: Sendkeys "%(YC)", False

I'm not really partial to that solution, though, because, as I said, I don't like to rely on a specific key sequence that can vary from context to context and version to version.

DAO CompactDatabase Method

A better solution, I think, is to use the DAO CompactDatabase method. The downside is that you can't use that on the current database. The workaround for that is to:

1. Programmatically create a new, temporary database
2. Copy several pre-created forms, macros and modules to this temp database
3. Close the main database and launch temporary database with the /x switch which initiates the compact process.
4. After compacting is done, close temp database and relaunch main database

On my website is a small sample database called "CompactDatabase.mdb" which illustrates this process.

The CompactDatabase method can also be used on an external database (in fact, it's easier to do so.) This is useful if you have split your database into a Front-End and a Back-End. You can use the CompactDatabase method directly from the Front-End against the Back-End. The BE, which stores the tables, is generally the one that needs compacting most. I've also got a sample called CompactBackEndDB.mdb which illustrates this.

The real usefulness of the DAO method is the safety and flexibility you have.  Instead of deleting the old database, you can rename it (say, by adding a date to the file name) so that you keep a backup.  In fact, the CompactDatabase method can also be used simply to create a backup of the database.

Compacting With ADO
For completeness, I should mention it's also possible to compact a database with ADO.  However, since I have no direct experience with it (I prefer DAO), I'll just give a Microsoft knowledge base article on the subject: http://support.microsoft.com/kb/230501/en-us.

Compacting Less Often
No discussion of compacting would be complete without addressing methods to reduce the need to compact in the first place.  I'll conclude this series next time with that.

.

How Do I Compact an Access Database?

In my previous post (What Does It Mean to Compact My Access Database?) I talked about what compacting an Access database is.  In this post, I'm going to discuss how to do it.  As with most things in Access, there are a number of ways, and the specifics change from version to version.

Compact On Close
I'm starting with Compact On Close to get it out of the way.  Few experienced Access developers recommend this option. 

Steps by version:
Access 2003: Tools > Options > General > Compact On Close (checkbox)
Access 2007: Office Button > Access Options > Current Database > Compact On Close (checkbox)
Access 2010: File > Options > Current Database > Compact On Close (checkbox)

While regular compacting can be good for performance, it's rare that a database needs to be compacted *every* time it closes.  If it does, there are steps you can take to reduce that necessity (I'll address that in a later post.) But the real problem with Compact on Close is the risk of database corruption.  Anytime you compact your database, there is a small risk of corrupting it.  When you do it every time you close the database, the changes increase simply due to its frequency. This is why most of us discourage the use of Compact On Close.

Compacting the Current Database in the UI
In Access, you can compact the current database from the Menu/Ribbon as long as you are the only one that has it open.

Steps by version:
Access 2003: Tools > Database Utilities > Compact and Repair Database...
Access 2007: Office Button> Manage > Compact and Repair Database
Access 2010: Database Tools tab > Compact and Repair Database

As I said in my last post, the compact process writes the records to a new database, deletes the old one, and renames the new one to the old name.  This is where the risk of corruption comes into it.  If something happens during the write process -- say, by a network interruption -- the new database can be corrupted and when the old one is deleted, you've lost your only good copy.

I have had this happen to me.  A database that's corrupted during compacting is, in my experience, corrupted beyond repair.  But I have to say that I believe later Access versions are better at ensuring corruption doesn't happen.  Still, there's always a risk.  That's why it's a good idea to make a copy of your database before compacting it.
 
Because network glitches can cause corruption, it is sometimes advisable (and faster) to copy a network database to a local drive, compact it, then copy it back up to the network.  This has the added benefit of preserving a the original while you compact it.
 

Compact Another Database in the Access UI

You can also compact a database that is not open (that is, the current database) in Access.  To do this, you must have Access open, but have no database open.

Steps by version:
*With No database open in Access*
Access 2003: Tools > Database Utilities > Compact and Repair Database...
Access 2007: Office Button> Manage > Compact and Repair Database
Access 2010: Database Tools tab > Compact and Repair Database

The steps are nearly the same as compacting the current database, but it will give you two dialog boxes, one (Database to Compact From) asking for the name of the file to be compacted, and a second (Compact Database Into) asking for the name you want to give to the new database.  Using this method, you *cannot* give it the same name as the original, so it safeguards your original database.  It also means, however, that you'll have to do the renaming manually.


Using Code

There are several ways to compact a database in code, both the current database and an external database.  There's enough here to fill a post of its own, so that's will I'll address next in How Can I Compact my Access Database Less Often?

.

What Does It Mean to Compact My Access Database?

Access databases are designed to grow dynamically as data is added.  Access will also create working space to manipulate data.  However, while it will grow dynamically, it does not shrink automatically when records are deleted or when it's data maniulation is done. 

What's more, as data is added and deleted, the database can become fragmented (much like a hard disk is fragmented), and records can be stored out of primary key order.  All of this can degrade the performance of your database, especially as it gets larger.

Compacting the database corrects these problems.

But before I get to that, it's important to understand exactly how compacting works because and existing file is not actually compacted.  Here's what actually happens:

1. Access creates a new, blank database called db1.mdb (Access 2003-) or database1.accdb (Access 2007+).
2. It writes the data in the original database to the new database, doing several things in the process:
3. When the process completes successfully, it deletes the original database.
4. Lastly, it renames the new database to the original database name.

This process does a number of things that improves the performance of your database.

First of all, the database is "defragmented", that is, it rearranges how the file is stored on the disk.  This is different than a disk defrag, but the effect is much the same: putting data into contiguous blocks.  In this process, empty space is reclaimed, usually leaving the database file size smaller.  This is the most common reason given for compacting, but perhaps not the most important.

Secondly, it rearranges the order of the records by writing them in primary key order.  This is the equivalent of a Clustered Index in other database implementation and makes the read-ahead capabilities of the database engine more efficient.

Thirdly, it rebuilds the table statistics which are used by the query optimizer to optimize queries.  It also forces a re-compile of your saved and embedded queries based on these new stastics.

Lastly, since technically it's called Compact and Repair, it will fix any page corruptions caused by hardware or network problems.

There are some limitations, however.  Since Compacting writes the database to a new file, there must be enough hard drive space to hold both databases.  You also can't compact a database is anyone else is in it.  You must have exclusive access (although it doesn't have to opened in "exclusive" mode.)  You can compact the current database (that is, the one you have open) through the Access User Interface, but not in code.  (I'll discuss that in a later post.)

So, how exactly do you compact a database?  There are several ways, both manual and programmatic, and I'll discuss that in my next post: How Do I Compact an Access Database?.

.