The Access Wizard
$Account.OrganizationName
The Access Wizard Newsletter Tips Tricks and Traps for Access Users and Developers
January 2008

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.

In this Issue
  • Tip of the Month -- Access "Under the Hood"
  • Symptoms of Corruption
  • Causes of Corruption
  • The Easiest Way to Recover -- Compact and Repair
  • A More Powerful Tool -- Decompile
  • Conclusion and a Look Ahead
  • Trap of the Month - Dates in Queries Need Special Formatting

  • 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 at all.

    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 reasonable size.


    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 command.

    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 follows:

    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 word "decompile".

    For me the command I use in the Open box is:

    "C:\Program Files\Office03\OFFICE11 \MSACCESS.EXE" /decompile

    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 message.

    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 things right."

    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 would


    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
    Acess Traps

    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 expect.


    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:

    ? syscmd(acSysCmdAccessDir)

    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!)

    Quick Links...

    Custom Software Home

    Access Wizard Archives

    Our Services



    Join our mailing list!

    Forward email

    This email was sent to jim@custom-software.biz, by jim@custom-software.biz
     
     

    Custom Software | Copyright Custom Software All Rights Reserved | Westford | MA | 01886