Microsoft JET SQL Query Logging or "How do I debug my customer's program?"

Answer 1

It is not possible without the help of the developers. Sorry.

Answer 2

First question: Do you have a copy of MS Access 2000 or better?

If so: When you say the MDB is "password protected", do you mean that when you try to open it using MS Access you get a prompt for a password only, or does it prompt you for a user name and password? (Or give you an error message that says, "You do not have the necessary permissions to use the foo.mdb object."?)

If it's the latter, (user-level security), look for a corresponding .MDW file that goes along with the MDB. If you find it, this is the "workgroup information file" that is used as a "key" for opening the MDB. Try making a desktop shortcut with a target like:

"Path to MSACCESS.EXE" "Path To foo.mdb" /wrkgrp "Path to foo.mdw"

MS Access should then prompt you for your user name and password which is (hopefully) the same as what the VB6 app asks you for. This would at least allow you to open the MDB file and look at the table structure to see if there are any obvious design flaws.

Beyond that, as far as I know, Eduardo is correct that you pretty much need to be able to run a debugger on the developer's source code to find out exactly what the real-time queries are doing...

Answer 3

Eduardo is right that it's not possible without the help of the developers - specifically, you need the source code of the application.

Answer 4

Could you not throw a packet sniffer (like Wireshark) on the network and watch the traffic between one user and the host machine?

Answer 5

If it uses an ODBC connection you can enable logging for that.

1. Start ODBC Data Source Administrator.
2. Select the Tracing tab
3. Select the Start Tracing Now button.
4. Select Apply or OK.
5. Run the app for awhile.
6. Return to ODBC Administrator.
7. Select the Tracing tab.
8. Select the Stop Tracing Now button.
9. The trace can be viewed in the location that you initially specified in the Log file Path box.

Answer 6

To get your grubby hands on exactly what Access is doing query-wise behind the scenes there's an undocumented feature called JETSHOWPLAN - when switched on in the registry it creates a showplan.out text file. The details are in this article: link text (http://articles.techrepublic.com.com/5100-22-5064388.html)

For tracking down nightmare problems it's unbeatable - it's the sort of thing you get on your big expensive industrial databases - this feature is cool - it's lovely and fluffy - it's my friend… ;-)

Cheers, Dave

Answer 7

We're having the same issue here and we are trying the JETSHOWPLAN tips, but we cannot find the SHOWPLAN.OUT in the HD. Any tip?

All the best, Pedro.