Clean up a table or mdb beyond compact and repair?

[Laurel]

I have a sick back-end database. If I add a record to a particular table,
it causes the problem described below. If I compact/repair the back end
after adding the record, the problem goes away. I want to do something
beyond compact/repair to get rid of the problem in a more thorough way. Any
advice? I thought I might start by creating another table that looks just
like the culprit table, and then moving all the rows from the culprit to the
new one. Then deleting the old and renaming the new. Reasonable? Any
other techniques?


*********************
For those who are curious.

Here's the problem. In this bit of code,

ls_temp = "Select * from qryClassSummaryDetail " & As_where
Set rstClassDetail = CurrentDb.OpenRecordset(ls_temp)

if ls_temp equals this,

Select * from qryClassSummaryDetail WHERE [class_code] = 'ML2' AND
Student_ID <> -1

I get only one row returned - the first row in the result set if the same
SQL is executed in the query builder. (all of these statements were cut
from "Print ls_temp" in the Immediate window - no typos here.) Also, the
path in the code is irrelevant. I can take the path to build any of these
SQL statements and then set ls_temp to whatever just before the Set. The
problem is related to the value of ls_temp - not what happened on the way to
constructing it.

ls_temp = "Select * from qryClassSummaryDetail " & As_where
Set rstClassDetail = CurrentDb.OpenRecordset(ls_temp)


All these other values for ls_temp return the expected multiple rows. In
fact, the first one returns the same rows that the "bad" SQL returns.

Select * from qryClassSummaryDetail WHERE [class_code] = 'ML2' AND
[Score_Date] Between #9/10/2006# and #9/20/2006# AND Student_ID <> -1

Select * from qryClassSummaryDetail WHERE [class_code] = 'ML2' AND
[Score_Date] = #9/19/2006# AND Student_ID <> -1

Select * from qryClassSummaryDetail WHERE [class_code] = 'ML2' AND [Week_of]
= #9/18/2006# AND Student_ID <> -1

 

[John Vinson]

Any other techniques?

Check the suggestions at

http://www.granite.ab.ca/access/corruptmdbs.htm

John W. Vinson[MVP]

 

[Laurel]

Thanks. Importing the table worked.

Check the suggestions at

http://www.granite.ab.ca/access/corruptmdbs.htm

John W. Vinson[MVP]