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

Question

Is it possible to monitor what is happening to an Access MDB (ie. what SQL queries are being executed against it), in the same way as you would use SQL Profiler for the SQL Server?

I need logs of actual queries being called.

Answer 1

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_MACHINESoftwareMicrosoftJet4.0EnginesODBC 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_MACHINESOFTWAREMICROSOFTJET4.0EnginesDebug (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_MACHINESoftwareMicrosoftJet4.0EnginesODBC. 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_MACHINESOFTWAREMicrosoftOffice12.0Access Connectivity EngineEngines. 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.