Problem with a MS Access query after a "Compact and repair" operation.

Question

I have an Access application that use the classical front-end/back-end approach. Yesterday, the backend got corrupted for a reason I don't know. So I opened the backend with Access 2003 and access asked me if I wanted to repair the file, I said yes and it seemed to work.

I can open the database see the tables contents and run most of the queries.

However there is an access query that doesn't work with a specific where clause.

Example :

// This works in the original DB, but not in the compacted one :
SELECT a, b, c 
FROM tbl1 INNER JOIN tbl2 ON tbl1.d = tbl2.d 
WHERE e = 3 AND tbl2.f = 1;

// This works in both the original and the compacted one :
SELECT a, b, c 
FROM tbl1 INNER JOIN tbl2 ON tbl1.d = tbl2.d 
WHERE e = 3;

When I try to run the queries, nothing happens. The access process start to use most of the CPU and the GUI stop responding. If I run the query from the query editor, I can use Ctrl+Break to stop the execution. I tried to give the query lot of time and it didn't help.

I've checked the execution plan in showplan.out and it seems correct (at least it should not takes forever to execute)

I tried to compact the DB again. I tried to import the tables in a new DB. I even tried to import the tables and their data in a mdb file that was in a now good state (from a backup).

Anyone have an idea?

Answer 1

I don't know what type of meta data Access brings along when it imports a table from one database into another one. If the meta data is corrupted, importing the table to another database wouldn't necessarily resolve the problem. If practical, you might try creating the tables from scratch in a brand new database and then just exporting and importing (or copying and paste appending) the data into the new database.

I've never seen a table get corrupted like this in such a small database, although with Access anything is possible. Could there be something wrong with the data?

Answer 2

I'd try recreating the query fresh (new name, etc.), and see what happens.

You could even try copying it (even within the same DB or to a brand new one). If that works, the worst case scenario is you have to copy all the objects across to a new DB.

Answer 3

Do you have access to a SQL Server installation? You could use the Upsizing Wizard under the Tools -> Database Utilities menu to copy the data to SQL Server, and see if you get the same problem there.

Answer 4

Sounds like an index was corrupted and when that happens, it's dropped during the compact. Check for a system table called MSysCompactErrors -- you'll have to show hidden objects and/or system objects in Tools | Options | VIEW.

Never compact a Jet MDB without making a backup beforehand. Because of that rule, the COMPACT ON CLOSE function is completely useless, as it's not cancellable, so you always make sure it's turned off in all MDBs.

Answer 5

Is there an index on the field tbl2.f?

Also try going into that table in datasheet view, sort tbl2.f in ascending sequence and see if there is anything really strange in the first or last records.