Programmatically check for Access database corruption?

Question

Is there a way to programmatically check for database object corruption in Access 2003?

My development project has gotten complex enough that it's hard to manually check all the objects after a day of programming to see if some small control, form, report, query, or code object has been corrupted somehow. I already have the data split off into a separate SQL Database stored on another machine, and this project is merely a front-end application to work with the data.

Mostly an academic musing, as I just don't want to get so far - then have corruption put me back several weeks because some seldom used object got corrupted way back when.

Any ideas out there? Thanks in advance for any pointers!

EDITED 12/03/2009 @ 11:51

Sadly, I can only accept one answer - though I got a few very good ones, thank you for all the pointers!

Answer 1

You might like to look at: http://stackoverflow.com/questions/244896/is-it-possible-to-programmatically-detect-corrupt-access-2007-database-tables

I am inclined to keep a copy of important databases at each compact & repair and to compare the new database against the previous one. You can also check for non-standard characters.

Answer 2

Proper compilation practices will prevent corruption of the VBA project (which is what you're talking about here).

That entails:

1. use OPTION EXPLICIT in all modules.

2. turn off COMPILE ON DEMAND in the VBE options.

3. compile your code regularly, while working.

4. periodically (e.g., once a day after a full day of coding) decompile and recompile the code.

If you do this, you'll never encounter corruption in the first place so you won't need to test for it (which is impossible in the first place).

Answer 3

Neither Compact/Repair nor Decompile/Recompile catches all corruption problems, although you should be doing this anyway.

I use a function to export all Container Docs (and QueryDefs) using SaveAsText into a date/time stamped folder, and use it regularly throughout the day. If I suspect any corruption, I create a new mdb, and use LoadFromText to recreate the objects.