0

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?
0
  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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.