Peter's Software
Helping you create better Microsoft Access applications

Tip: How to Fix Mysterious Errors

Every now and then you may see an error in your Microsoft® Access® database application that just doesn't seem to make sense. Or perhaps you've been trying to track down a nagging error message for hours and you just don't know where to go next. Well, this list is for you. If you have one of those mysterious errors that just won't go away, try the steps below and I'm sure you'll find a resolution.

(What was your error, and which step resolved it? Let us know at info@peterssoftware.com)

a. Compile all your modules

Click Debug > Compile <My Project Name> from any module in design mode. Sometimes a compile error in one module can cause weird problems in another. It's a good idea to check to see that all of your modules compile successfully before you continue debugging the error.

b. Compact and repair your database

Make a backup, then click Tools > Database Utilities > Compact and Repair Database. This can sometimes fix problems, and it should be done from time to time anyway to keep your database running efficiently. Also, try compacting and repairing the database where the tables reside if you have split your database.

c. Check for "MISSING:" references

From any module in design mode, click Tools > References and look for any checked references that are "MISSING:". Uncheck these references if they are not needed to compile your application, or click the "Browse" button to find the new location of the missing library. A missing reference can cause an error in a seemingly unrelated module.

d. Restart your application and/or your computer

Close your database and re-open it. Close Access and re-open it. Restart your computer. Shut down your computer for a few seconds, then turn it on again. Each of these options may remove something in memory that might be causing a problem and allow a fresh (perhaps error free) application reload.

e. Check and uncheck some references to get Access to update its internal pointers

Open Tools > References. Make a mental note or write down the checked libraries. Check a library at random that is currently unchecked. Close the dialog, Open Tools > References again. Uncheck the library you just checked. Close. If that doesn't work try changing the order of some of your referenced libraries. If that doesn't work try UNchecking a checked library. Close the dialog. Open again. Check the library. Close again. These procedures will force Access to update its internal pointers and could resolve strange errors.

f. Decompile and recompile your database

Make a backup, then open Access with the /decompile parameter. Click File > Open and open your database, then close it, and close Access. Open Access again normally, open your database and click Debug > Compile from any module in design mode. This process causes all of the unseen compiled code saved with your database to be removed and completely re-compiled. If there happened to be any residual compiled code in your database that might have been causing a problem, this will clean it up.

g. Microsoft Office Diagnostics/Detect and repair

(Access 2003 and earlier) Click Help > Detect and Repair...
(Access 2007 and later) Click Office Button > Access Options > Resources > Diagnose

Running these diagnostics can sometimes correct a problem with the Access installation and/or an installed library.

h. Get the latest updates to your version of Access

Go to http://office.microsoft.com and download and install the latest updates for your version of Access by following the "Office Update" link. You may have to repeat this process a few times as some updates require other updates to be installed first. One of these updates may include a fix for the problem you are having.

i. Access Repair/Reinstall

Pull out your Microsoft® Access® install disk, insert it into your CD drive, and double click "Setup.exe" if it doesn't automatically start by itself. You have the option here to repair your Access installation, then will have the choice to reinstall, or detect and repair any errors in the installation. This process can fix any Access libraries that may have become corrupted or which have gone missing.

j. Create a new database, and import all objects from the old database to the new one

Create a new database, then click File > Get External Data > Import..., find the problem database file, select all objects and then import them. If the problem is with one specific form or report, then that object may not survive this process. You may have to re-create that form or report, but that's better than losing your entire database to a nasty error.

k. Check that your functions and subroutines end with "On Error Goto 0"

It's a good idea to reset error handling when you leave a function or subroutine, as an error in one routine can sometimes, in rare occasions, be trapped and reported by another routine. This can be quite confusing, to say the least. Resetting error handling with "On Error Goto 0" as the last statement in your routine will prevent this kind of confusion from occurring.

l. Maybe get some professional help

Seriously. There are people on the web who will be more than willing to help you resolve your problem for free. A list of Access help resources can be found at this link: http://www.peterssoftware.com/help.htm. You can search the Microsoft Knowledgebase for a possible resolution: http://support.microsoft.com. If your problem is serious database corruption, there are database repair/recovery services listed here: http://www.peterssoftware.com/links.htm

Do you have a suggestion for something else to try that might resolve a mysterious Access error? Let us know at info@peterssoftware.com

Peter's Software Helping you create better Microsoft Access applications

Tip: How to Fix Mysterious Errors

Peter's Software
Helping you create better Microsoft Access applications