Microsoft JET SQL Query Logging or "How to debug my client program?"

Question

Problem:

We use a program written by our largest client to receive orders, order transfers and do other things related to the order. We have no other chance, but to use the program, and the client is very incapable when it comes to problems with their program. We just need to live with the program.

Now this program is in most cases very slow when used with two or more users, so I tried to look behind the curtain and find the source of the problem.

Some points that I have learned so far:

• It is written in VB 6.0
• It uses a password-protected Access-DB (Access 2000 MDB), which is located on the same user machine.
• This folder is shared over the network and used by all other users.
• It uses the msjet40.dll 4.00.9704 file to communicate with access. I think this is ADO?

I also used Process Monitor to control access to files and found out why the program runs so slowly: it performs thousands of read operations on an mdb file, even if the program is inactive. Over the network, this is of course extremely slow:

Process Monitor Trace http://img217.imageshack.us/img217/1456/screenshothw5.png

The real question is:

Is there a way to track requests that are responsible for reading activity? Is there a trace flag that I can set? Connect JET DLL? I assume that the program performs several expensive queries that cause JET to read a large amount of data in the process.

PS: mdb , , . ( ) .

, , - -, .

Answer 1

, , Access , JETSHOWPLAN - showplan.out. TechRepublic, :

ShowPlan Jet 3.0 , . (ShowPlan .) , :

\\HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\JET\4.0\Engines\Debug

Debug JETSHOWPLAN ( ). ON . Access , , .

ShowPlan , Jet SHOWPLAN.OUT ( " My Documents " , Jet) , Jet . , , Jet .

, OFF . Jet , . , . , , .

- , - - - … ;-)

Answer 2

(, Wireshark) ?

Answer 3

ODBC, .

• ODBC Data Source Administrator.
• " ".
• "" "".
• .
• ODBC.
• .
• " ".
• , " ".

Answer 4

: MS Access 2000 ?

: , MDB " ", , MS Access ? ( , : " foo.mdb."?)

( ), .MDW , MDB. , " ", "" MDB. , :

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

Then MS Access should provide you with a username and password that (hopefully) match what the VB6 application offers you. This will at least allow you to open the MDB file and look at the table structure to see if there are obvious design flaws.

In addition, as far as I know, Eduardo is right that you should be able to run the debugger in the source code of the developer to a large extent to find out exactly what the requests do in real time ...

Answer 5

This is not possible without the help of developers. Unfortunately.