Access MDB: 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

Keep in mind that the file sitting on your hard drive is simply a windows file. So, there is a big difference between a server based system and that of a simple text file, or Power Point file, or in this case a mdb file just sitting on the drive.

However you can get the jet engine to display its query optimizeing via showplan.

How to do this is explained here:

http://www.databasejournal.com/features/msaccess/article.php/3658041/Queries-On-Steroids--Part-IV.htm

The above article also shows how to access the jet disk read statistics, which I also find extremely useful for optimizing things.

Just remember to turn off that data engine logging system when you’re not using it as it creates huge log files…

Answer 2

If you're accessing it via ODBC, you can turn on ODBC logging. It will slow things down a lot, though. And it won't work for any other data interface.

Another thought is using Jet/ACE as a linked server in SQL Server, and then using SQL Profiler. But that's going to tell you the SQL that SQL Server processed, not what Jet/ACE processed. It may be sufficient for your purposes, but I don't think it would be a good diagnostic for Jet/ACE.

EDIT:

In a comment, the original poster has provided this rather crucial information:

The application I am trying to monitor is compiled and at a customer's premises. I am trying to monitor what queries it is attempting against an MDB. I cannot modify the application. I am trying to do what SQL Profiler would do for a SQL Server.

In that case, I think that you could do this:

1. rename the original MDB to something else.

2. use a SQL Server linked server to connect to the renamed MDB file.

3. create a new MDB with the name of the original MDB and link to the SQL Server with ODBC.

The result will be an MDB file that has the same tables in it as the original, but they are not local, but links to the SQL Server. In that case, all access will be going through the SQL Server and can be viewed with SQL Profiler.

I don't have a clue what this would do to performance, or if it would break any of the data retrieval in the original app. If that app uses table-type recordsets or SEEK, then, yes, it will break. But this is the only way I can see to get logging.

It shouldn't be surprising that there is no logging for Jet/ACE, given that there is no single server process managing access to the data store.

Answer 3

you could write your own profiler, based on a "transaction" object that will centralize all instructions sent to the database, You'll end up somewhere with a "transaction.execute" method, and a transaction table in your access db. This table can then be used to collect transaction's instructions, start time, end time, user sending the instruction, etc.

Answer 4

I'd suggest upsizing the tables to SQL Server. There is a tool from the SQL Server group that is better than the Upsizing Wizard that is included with Access. SQL Server Migration Assistant for Access (SSMA Access)

Also see my Random Thoughts on SQL Server Upsizing from Microsoft Access Tips page

Answer 5

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 http://articles.techrepublic.com.com/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.