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.

Twig

What access technologies (DAO, OLEDB, ODBC,...) are you using/able to use to query the db? – Overdo

DAO and OLEDB. But I need to log queries made by compiled code. – Twig

I don't believe there is any answer to your question in those circumstances. Why do you want to monitor everything? Have you considered writing a wrapper subroutine to execute all your SQL and have it write the log file? – Nolen

I have a compiled app at a location and I need to monitor what SQL is being attempted. – Twig

What about my second question? You could write a wrapper function to execute your SQL DML statements and record those in a log file. Likewise, for SELECTs executing in code. For recordsources of forms/reports, you'd need to write to the log when the forms/reports are opened, and when a form is requeried/filtered (reports can be filtered in A2007, so you'd need to use the filter event there, too). But again, I why do you need to monitor the SQL statements? Are you trying to troubleshoot a performance problem? – Nolen

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. – Twig

Which version of Access and OLE DB you use? – Awry

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_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.

Awry

I am using DAO and OLEDB to access the MDB from vb6 and .net. – Twig

I asing OLEDB to access the .mdb from .NET but SQL Server is not involved. – Twig

Corresponds to support.microsoft.com/kb/252883/en OLE DB supports JETSHOWPLAN parameter, because this implementation used JET (DAO) to access MDB. It can be changed in the next version of OLE DB provider. To be sure, that you make setting in registry in the correct place I recommend you trace OLE DB access with respect of Process Monitor (see technet.microsoft.com/en-us/sysinternals/bb896645.aspx). In the trace protocol of Process Monitor search for showplan string in the trace of registry access. If OLE DB do try to read showplan setings in registry from other place you'll see. – Awry

+1 for telling us there was an English version. Makes me wonder how many good tools are out there but written in German. – Teal

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.

Nolen

I don't know that there is any logging available with OLEDB. If you find that there is, please post back about it. – Nolen

Answer 3

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…

Baseler

The showplan.out doesn't seem to give me a log of the actual queries being made. – Twig

The showplan does not give a log of the queries (my sorry if that was misleading). Show plan does log the query plans generated for each query and that is what you need for performance tuning. As far as I know there no actual logging available for each query. Remember you can use the dao object model to reterive data without sql. So only the plans used to retrieve data are logged and not the sql. My guess no sql is logged since you not limited to using sql to grab data. As mentioned JET is not a service but only a file based in-process library of code anyway. – Baseler

Answer 4

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.

Kaunas

So what about foiks who link to the BE MDB file directly from their own database and/or open the database directly? Or take it home in the evening and replace it in the morning before anyone gets in to work? – Abruzzi

I didn't notice that the objective was to use a bullet-proof all-weather earthquake-aware database monitor. I thaught we were just talking about the best way to follow-up what's happening to a standard mdb file, being updated by standard users through a standard user interface, with a standard developer trying to collect some standard data in order to improve his work ... – Kaunas

Answer 5

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

Abruzzi

Why would upsizing be the answer to the question? – Nolen

David, why not? Surely you can log the queries in SQL Server. – Abruzzi

You seem to be mistaking the question for the problem. Logging is not the problem -- logging is only a possible mechanism for resolving a problem that remains unstated. – Nolen

This could potentially be useful in the event that the Access queries translate across well and the interface to the application remains unchanged. Then changing the compiled app's connection string to use SQL Server temporarily and running some traces could be a good approach. I understand the problem to be discerning what the compiled app is actually doing. – Solvolysis

I wonder if you could create a linked server in SQL Server to your back end data file, then switch your linked tables to SQL Server. This would get you the same logging as you'd get with upsizing, but wouldn't require that you actually bother to upsize. – Nolen