How to Monitor and Log Actual Queries Made Against an Access Mdb

Is it possible to monitor and log actual queries made against an Access MDB?

The answer depend on the technology used from the client which use MDB. There are different tracing settings which you can configure in HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\ODBC http://office.microsoft.com/en-us/access/HP010321641033.aspx. If you use OLEDB to access MDB from SQL Server you can use DBCC TRACEON (see http://msdn.microsoft.com/en-us/library/ms187329.aspx). I can continue, but before all you should exactly define which interface you use to access MDB.

MDB is a file without any active components, so the tracing can makes not MDB itself, but the DB interface only.

UPDATED: Because use use DAO (Jet Engine) and OLE DB from VB I recommend you create JETSHOWPLAN regisry key with the "ON" value under HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\JET\4.0\Engines\Debug (Debug subkey you have to create). This key described for example in https://web.archive.org/web/1/http://articles.techrepublic%2ecom%2ecom/5100-10878_11-5064388.html, http://msdn.microsoft.com/en-us/library/aa188211%28office.10%29.aspx and corresponds to http://support.microsoft.com/kb/252883/en allow trace OLE DB queries. If this output will be not enough for you you can additionally use TraceSQLMode and TraceODBCAPI from HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\ODBC. In my practice JETSHOWPLAN gives perfect information for me. See also SHOWPLAN commend.

UPDATED 2: For more recent version of Access (like Access 2007) use key like HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines. The tool ShowplanCapturer (see http://www.mosstools.de/index.php?option=com_content&view=article&id=54&Item%20%20id=57, to download http://www.mosstools.de/download/showplan_v9.zip also in english) can be also helpful for you.

Monitor queries in an access database

Have you tried turning on the ODBC trace function? Use the ODBC Data Sources control panel applet. Switch to the 'Trace' tab and review the options available.

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

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 TechRepublic article alternate, summarized here:

The ShowPlan option was added to Jet 3.0, and produces a text file
that contains the query's plan. (ShowPlan doesn't support subqueries.)
You must enable it by adding a Debug key to the registry like so:
\\HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\JET\4.0\Engines\Debug
Under the new Debug key, add a string data type named JETSHOWPLAN
(you must use all uppercase letters). Then, add the key value ON to
enable the feature. If Access has been running in the background, you
must close it and relaunch it for the function to work.

When ShowPlan is enabled, Jet creates a text file named SHOWPLAN.OUT
(which might end up in your My Documents folder or the current
default folder, depending on the version of Jet you're using) every
time Jet compiles a query. You can then view this text file for clues
to how Jet is running your queries.

We recommend that you disable this feature by changing the key's value
to OFF unless you're specifically using it. Jet appends the plan to
an existing file and eventually, the process actually slows things
down. Turn on the feature only when you need to review a specific
query plan. Open the database, run the query, and then disable the
feature.

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… ;-)

how to get sql query in MS Access 2007

I was not able to find any such tool (i did lots of google few days back, when i was having similar requirements) and finally endup doing import of ms-access db in sql server and change connection, did my work and then changed the connection back to ms-access.. :)

Storing queries on backend access database

You could either save the queries in a separate file on the network share for the users to import from as needed.

A more polished method is to maintain a single frontend, then copy this to a network share when a new version is ready, and let the users download this whenever they launch the application from a shortcut. This way the distribution is fully automatic, and it even works in a Citrix environment.

The full description and a script (which you probably can reduce a little) can be found here:

Deploy and update a Microsoft Access application in a Citrix environment

The crucial line where the local copy is opened is here:

' Run PPT.
If objFSO.FileExists(strAppLocalPath) Then
  Call RunApp(strAppLocalPath, False)
Else
  Call ErrorHandler("The local application file:" & vbCrLf & strAppLocalPath & vbCrLF & "could not be found.")    
End If

So to be sure get strAppLocalPath right.