Empty Result Set on One Table in the MDB

[Noah Nordrum]

First of all, thanks for your great work on this amazing driver!

I'm processing a slew of previously generated mdb files and some of them seem to have an issue with the one table that I need from the file (of course!).

When I query the table, even a select 1 from <table_name>, I get an empty ResultSet and no thrown errors.</table_name>

If I do a select * from <table_name>, I can see all the columns in the wrapped.resultMetaData, but the call to next() keeps returning false (even if I call wrapped.next() in the debugger).</table_name>

The query works as expected on probably 80-90% of the files through the driver, and 100% of the time if I do it directly in Access. File size doesn't seem to correlate with function--I have large and small files that all work/don't work, but it is consistent. Some files never work, while some work all the time.

I'm using java 1.7, and UCanAccess-2.0.8.

I believe it has something to do with the Hsql<-->Access layer, but don't know enough about how it works to be sure. Can't send the file, but do you have any suggestions for how to debug/diagnose this?

Interesting development: if I do a compact and repair in Access for the file, the driver is able to read the file with no issues.

[Marco Amadei]

This is a known problem, it's the third time that it has been reported.
I don't need the mdb file, but only it would be helpful if you mentioned the access file version. Is it an access 97 file?
The problem occurs likely at loading time, and the thing I might do to improve the ucanaccess behavior is to to show a clear warning/error message. This because it's a format issue.
Now I'm far from my office so I'm going to analyze the problem next monday.
After that, I'll let you know my findings in this forum thread.

[Noah Nordrum]

So this requires Access be installed, and is pretty hacky, but it works.

try {
final File access = new File("C:/Program Files/Microsoft Office/Office14/msaccess.exe");
if (access.canExecute()) {
final String command = "\"" + access.getAbsolutePath() + "\" \"" + file.getAbsolutePath() + "\" /compact";
final Process exec = Runtime.getRuntime().exec(command);
exec.waitFor();
}
} catch (Exception e) {
e.printStackTrace()
}

Obviously adjust for your particular office install version/location/etc.

I'm running this in a batch context, so this is a perfectly acceptable resolution.

I'm actually not sure what version it is, or how to figure that out. I was trying to figure that out when I saw the "Compact and Repair" button, and that's when the light bulb went off in my head. I agree though if if it's a format error you can detect at loading time, a warning/error message would be GREATLY appreciated :)

Thanks!

[Marco Amadei]

Hi Noah,
unfortunatly, with the only "corrupted" database I got, I can't detect the mdb format damage.
Please, try to execute the following program replacing <file> with the mdb path to a corrupted (and not yet repaired!) mdb and </file>

with the name of the table that wrongly appears to be empty:

[Noah Nordrum]

format: V2000 [VERSION_4]
rows nr:0

after I compact and repair, it looks like this:

format: V2000 [VERSION_4]
rows nr:55

I debugged all the way to the place where it loads the _rowCount from the java.nio.HeapByteBuffer in TableImpl:237. Seems likely that it's something in TableImpl.loadCompleteTableDefinitionBuffer, but we've reached the end of my knowledge of java.nio unfortunately...

I have a feeling this is a toss over to jackcess for a 2.0.5 fix issue.

[Marco Amadei]

Yes, unfortunatly I've the same feeling...
my java.nio knowledge isn't competitive...
and the issue can just be fixed at the jackcess layer.
Could you provide a "damaged" mdb to the jackcess team?(I mean, after having deleted private data from the one you're testing)
I got one, but it was provided by a stackoverflow user and I'm not sure I can embed it in the toss over to jackcess.

[Marco Amadei]

This issue, which is due to a misalignment between table metadata and data in damaged mdb/accdb, has been solved in the 2.0.9.