Trace ODBC Commands – Registry Path for TraceSQLMode and Location of SQLOUT.TXT

execution-planms accessms-access-2016odbctrace

In the Optimizing Microsoft Office Access documentation, it mentions that we can:

…edit a Microsoft Windows registry setting that allows you to see
the commands that the Office Access database engine is submitting to
ODBC.

It goes on to give detailed instructions as to how to do this:

To enable tracing of all ODBC commands from the Jet database engine:

  1. From the Windows Start menu, select Run.
  2. Type Regedit to open the Registry Editor.
  3. If you are using a version of Office Access prior to Office Access 2007, navigate to the following registry key, which
    appears as a folder in the Registry Editor.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\ODBC

Office Access 2007 uses a customized version of the Jet database
engine, named the Office Access Connectivity Engine (ACE), which is
not shared with other Windows applications. If you are using Office
Access 2007, navigate to the following registry key.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity
Engine\ODBC

  1. Double-click the TraceSQLMode setting, change the value from 0 to 1,
    and click OK.
  2. If Office Access is open when you make this change, you
    must close and reopen Office Access for the change to take effect.

After making this change in the registry, queries submitted to any ODBC data source are logged in a text file named Sqlout.txt.

However, as you can probably tell, this documentation is quite old (it was written about Access 2007 and was published in 2006). Because it is so old, the registry information seems to be out-of-date for newer products such as Office 2016.

Also, it would seem that Access 2016 doesn't use Jet Engine, but instead uses the Access Connectivity Engine.

As such, I'm having trouble finding a couple of things:

  1. Where is the Access Connectivity Engine >>> TraceSQLMode registry key for Access 2016?
  2. Additionally, where does the sqlout.txt file get saved on my computer?

Best Answer

  1. I managed to dig up the path from a comment on this page. On my machine, with Access 2016, the TraceSQLMode registry key is found here:

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE\Software\Microsoft\Office\16.0\Access Connectivity Engine\Engines\ODBC\TraceSQLMode

  2. I found something on this page that mentioned that the sqlout.txt file is stored in the Default database folder as indicated in MS Access Tools-> Options-> General-> Default database folder. In my case, it is located here:

    C:\Users\[my user name]\Documents\sqlout.txt

Related Question