I've described the symptoms of corruption and suggested potential recovery approaches in the January 2008 and February 2008 issues of the Wizard.

In January, I explained how to do the most straightforward and simple recovery of a corrupt a database by using the native Compact and Repair tool in Access. I also showed the more powerful tool of decompiling an application.

In February, I provided a method to recover from almost all issues by essentially rebuilding your file. That approach, although almost always effective, is bringing out the really big guns. There is one intermediate method that you might want to consider prior to going down the rebuilding path.

This intermediate method uses a utility that Microsoft provides which is stronger than the native Compact and Repair tool that comes in Access, yet less drastic than rebuilding your file.

The JetComp utility is not typically included when you install Access. The easiest way to get it is to go to Microsoft's site and download it.

The following link can be used to get the program:
http://su pport.microsoft.com/kb/295334

You'll want to read the details on the download page to make sure that you're getting the most recent version of the JetComp utility as well as the one that is appropriate for your particular Access version.

After you've downloaded the file and have begun the installation process you'll see the dialog box below:

The program will invite you to put the extracted file in some convenient spot. Where you choose to put the file is not critical since JetComp is one of those programs that is easy to download again when you need it.

Once you have extracted the files go to that folder and double-click on the JETCOMP.exe file. This will start the JetComp program and you'll be met with the screen below:

The first item wants your existing database so browse to the Access file giving you the problems. The Database to compact Into space is the place to put your new file; so navigate to the directory where you want to save your recovered file, then type the name of the new database. It is important that you don't try to compact into an existing database even if it's empty, as that will likely result in an error.

The additional compact options that you may be interested in are the 4.x versus 3.x version: 3.x is for Access 97 and 4.x will work through Access 2003.

After you run the JetComp utility, you'll want to test the results to make sure that the corruption has been eliminated. If not, it may be time for the more drastic measure of rebuilding the file as I described in the February article.

Prior to beginning the JetComp utility, make sure that the file that you are trying to fix is closed. If you're working in a multi-user environment you'll have to check that all users are out of the file. There are some problems which will be beyond the ability of JetComp to fix. Before you abandon all hope, it's worth reading the JetComp word document that comes along with the JetComp utility. It will give you hints and issues to look for if you do have a problem. Although the JetComp is not terribly easy or intuitive to use, it's certainly worth a shot if the straightforward Compact and Repair tool, or the more complex decompile tool fails to fix your database.

This trap is only for those of you who are generating SQL statements in VBA code.

When writing SQL statements (see June 2005) , you may have to deal with an apostrophe. These frequently occur in values such as last names (e.g. O'Leary). If you want to preserve the apostrophe and not corrupt your code, you have to give this apostrophe special treatment.

Although there are several different approaches to take to preserve an apostrophe, the easiest way to deal with this is by doubling the apostrophe. So instead of the statement "SELECT * FROM tblClient WHERE Lname ='O'Leary'"
Replace the O'Leary with O''Leary.

This looks like it should not work, and if you were to take a look at it in the query design view it would look very strange. What's happening though is when Access encounters a single quote followed immediately by another single quote, it reinterprets the two single quotes as an apostrophe rather than the beginning or ending of a string.