Symptoms

SPEED Ferret crashes while searching a specific Access database, but works normally when searching other databases.

Problem

The database contains one or more objects having invalid metadata.

Background

Metadata is data that describes the design of your database. Access maintains this information in system tables and other internal data structures.

In addition to the primary metadata that you directly modify when you edit an object's design, Access also maintains secondary metadata which is derived from the primary metadata and cached in the database in order to improve performance.

For example, Access stores two versions of each VBA module. The primary version contains your actual VBA code, and the secondary version is a compiled version of the code that is ready to run. A similar thing happens with queries, where Access creates and stores an optimized execution plan that is derived from the actual query design.

Over time, as you make repeated changes to your database design, Access continually and incrementally updates the secondary metadata to keep it in synch with the primary metadata. On rare occasions, the secondary metadata apparently falls out of synch, and this can lead to problems.

SPEED Ferret utilizes Microsoft's DAO object library to retrieve the properties of many objects in an Access database. When the secondary metadata is out of synch, the DAO library will sometimes cause an Access Violation. And since DAO is a DLL running within the SPEED Ferret process, this causes the entire SPEED Ferret process to crash.

Initial Test

Perform a search that includes only the queries in your database. For a project named Project1, you could use the following object filter to accomplish this: {Project1.Queries.*}

If SPEED Ferret crashes while searching only queries, then the Jet metadata is damaged. Otherwise, the problem most likely exists within the VBA metadata.

Repairing Damaged VBA Metadata

• Start msaccess.exe from the command line using the undocumented /decompile switch.
• Open your database using the File/Open command. All of the secondary VBA metadata (compiled VBA code) will be discarded at this time.
• Recompile the database. (Note: The Compile command normally performs an incremental compilation designed to update the existing VBA metadata. But in this case, the secondary VBA metadata has been discarded by the /decompile switch, so the Compile command is forced to completely recompile all of your VBA code, thereby correcting any metadata problems that may have existed.)
• Close the database.

Repairing Damaged Jet Metadata

Paste the following code into a module in the database, then run it from the Immediate window. (Press Ctrl-G to open the Immediate window. Then enter QueryCheck and press ENTER.)

The code will create a file named c:\journal.txt. Each time that it inspects a query, it will append that query's name to the end of the file. When it encounters a corrupt query, your Access session will crash. Then if you open the file c:\journal.txt, you will find the name of the offending query at the bottom of the file.

To fix the query, just open the query in design view and Save it. Then restart Access and run the code snippet again to see if any other queries are broken.

Note: If the offending query has a name like "TMPCLPnnnnn", then it is a temporary, hidden object. Exit your Access session and then reopen the database, and the temporary query will no longer be present.

Sub QueryCheck()

  Dim DB As DAO.Database
  Dim QueryDef As DAO.QueryDef
  Dim FileNo As Long
  Dim SccStatus As Long

  Set DB = Application.CurrentDb

  For Each QueryDef In DB.QueryDefs

    FileNo = FreeFile
    Open "c:\journal.txt" For Append As #FileNo
    Print #FileNo, "Query '" + QueryDef.Name + "'? ";
    Close #FileNo

    On Error Resume Next
    SccStatus = QueryDef.Properties("SccStatus")
    On Error GoTo 0

    FileNo = FreeFile
    Open "c:\journal.txt" For Append As #FileNo
    Print #FileNo, "OK"
    Close #FileNo

  Next

End Sub