Toggle ODBC TraceSQLMode with an AutoHotKey Script

Traditional methods of enabling and disabling ODBC TraceSQLMode are a pain in the neck. This AutoHotKey script makes it as easy as pushing a button.

Toggle ODBC TraceSQLMode with an AutoHotKey Script

Toggling ODBC TraceSQLMode can be done with any custom hotkey combo.

In my JetShowPlan primer, I posted an AutoHotKey script you can use to toggle the Access query plan logger on and off.  Assigning a hotkey to this feature makes it a lot more likely that you would actually use it when the need arises.  It also makes it easier to remember to turn it off when you're done, as leaving it turned on can negatively affect performance.

Read on for the steps to assign a custom hotkey to the ODBC TraceSQLMode feature.

Identifying the Registry Key

This is usually the most difficult step of all.

The path to the registry key you use to enable or disable TraceSQLMode depends on several factors, including:

  • Access version number
  • 32-bit vs. 64-bit Office
  • ClickToRun (C2R) vs. MSI installation technology
  • 32-bit vs. 64-bit processor architecture

The most reliable way to identify the correct registry key is to use the Process Monitor utility.

Verify You Have the Correct Key

Before going any further, it's a good idea to make sure you have the right key.

Go into regedit as an administrator and set the value of TraceSQLMode to 1:

  1. Click [OK] to close the Edit dialog (no need to close Registry Editor itself)
  2. Close Access if it's already open (msaccess.exe checks the value of the TraceSQLMode registry key at startup)
  3. Open Access
  4. Double-click any linked ODBC table so it opens in datasheet view
  5. Find and open sqlout.txt

If you can't find the sqlout.txt file, then the most likely explanation is that you changed the wrong registry key.  Use procmon to troubleshoot.  

Do not move on past this step until you confirm it is working!!!  

(This alone could save you hours of frustration.)

Changing Permissions on the Registry Key

Since the TraceSQLMode key is in the machine-wide registry hive, HKLM, you would normally need to run regedit as an administrator to change it.

You may be surprised to know that individual registry keys (a.k.a., registry "folders") have their own permissions.  And you can change these permissions. Granting permissions to non-admin users to change the settings in this particular key is very low risk.  

Here's how to set permissions so that non-admin users can toggle the setting:

  1. Open regedit as an administrator
  2. Navigate to the TraceSQLMode registry value you identified above
  3. Right-click on the parent ODBC key and choose "Permissions..."
  4. Click [Add...] and enter your Windows user-profile name
  5. Click [Check Names] then [OK] to add the record
  6. Select the user record then check the box to Allow [√] Full Control
  7. Click [OK] to close the Permissions dialog box

Refer to the screenshots below for more information:

Create the AutoHotKey Script

Next, we will create the script that we will use to toggle TraceSQLMode on and off.

  1. Download and install AutoHotKey (v1.x) if you don't already have it
  2. Create a blank file in your favorite text editor
  3. Paste the code below into the text editor
  4. Update the two lines that begin with TraceSql in the "Configuration" section to match your environment
  5. Save the file with an .ahk file extension
  6. Double-click the saved file in File Explorer to launch the script
  7. Press Ctrl + Win + O to test the feature (you should see a MsgBox that says, "TraceSQLMode set to ON/OFF")
  8. Press Ctrl + Win + O to confirm the toggle is working (this time, the MsgBox should show "OFF" instead of "ON" or vice versa)
#SingleInstance Force

^#o:: ; Ctl + Win + O  (feel free to use your own key combination)
    ;--== Toggle ODBC TraceSQLMode ==--
    ;----- BEGIN CONFIGURATION (make all changes here) -------------
    ; set to 64 for 64-bit Access or 32-bit ClickToRun (C2R) versions of Access; else set to 32
    TraceSqlRegView = 64   
    ; for help identifying the correct key, visit
    TraceSqlKey = SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE\Software\Wow6432Node\Microsoft\Office\16.0\Access Connectivity Engine\Engines\ODBC  
    ;----- END CONFIGURATION ---------------------------------------
    SetRegView %TraceSqlRegView%
    RegRead TraceSqlSetting, HKEY_LOCAL_MACHINE\%TraceSqlKey%, TraceSQLMode
    If ( TraceSqlSetting = 0 ) {
            RegWrite REG_DWORD, HKEY_LOCAL_MACHINE\%TraceSqlKey%, TraceSQLMode, 1
            If ErrorLevel
                MsgBox Error enabling TraceSQLMode. Check permissions on:`n`nHKLM\%TraceSqlKey%`n`nfor user '%A_UserName%'
                MsgBox TraceSQLMode set to ON
    } Else {
            RegWrite REG_DWORD, HKEY_LOCAL_MACHINE\%TraceSqlKey%, TraceSQLMode, 0
            If ErrorLevel
                MsgBox Error disabling TraceSQLMode. Check permissions on:`n`nHKLM\%TraceSqlKey%`n`nfor user '%A_UserName%'
                MsgBox TraceSQLMode set to OFF
    SetRegView Default
Each time you press Ctrl + Win + O, TraceSQLMode will toggle on and off.

Analyzing sqlout.txt

Creating the sqlout.txt file does you no good if you don't know what to do with it once you have it.  For those details, check out my short introduction to the sqlout.txt file.

Potential Pitfalls

Registry Key Configuration in AutoHotKey

The TraceSqlRegView should be either 32 or 64.  

On my system, which is running a 32-bit ClickToRun version of Microsoft 365 (version 2202) on 64-bit Windows, I needed to use a value of 64 so that AutoHotKey would read from the 64-bit area of the registry.  That's because the ClickToRun versions of Office install into a virtualized registry key:


So, even though it's 32-bit Office running on 64-bit Windows, the WOW6432Node appears in the 64-bit section of the registry:

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

The whole situation is needlessly confusing, which is why I strongly recommend learning how to use ProcMon to troubleshoot these registry locations.

Insufficient Registry Permissions

If you forgot to assign Full Control to the registry key...or assigned it to the wrong user...or assigned it to the wrong key, then the toggle will not work.  

You should see a friendly error message from the script:

If you get this error message, refer back to the "Changing Permissions on the Registry Key" section above.

Hotkey Does Not Seem to Work

Sometimes, pressing the hotkey won't seem to do anything at all.

First, make sure the script is running.  You should see a green square icon with a white "H" in the notification area of the Windows taskbar.  If you don't see one, double-click the .ahk file to be sure it's really running.

Next, make sure that the active window is not running in admin mode.  The hotkey gets registered to the logged-on user's profile.  So, if the active window is the Registry Editor and it is running in elevated mode (i.e., as an administrator), then the admin user profile will be active instead of the current user profile.  

Bottom line, make sure the script is running and a non-admin application has the focus before trying the hotkey.

Referenced articles

JetShowPlan: A Primer
You may be familiar with JetShowPlan, but I guarantee you’ve never read an article about it quite like this one.
Using ProcMon to Troubleshoot Registry Calls
Finding the correct registry keys for JetShowPlan and ODBC TraceSqlMode can be tricky. Let ProcMon take the guesswork out of the process.
3 Ways to Find sqlout.txt
Finding ODBC TraceSQLMode’s sqlout.txt file can be deceptively difficult. Here are three approaches to make it easy. At least one is guaranteed to work.
Making Sense of SQLOUT.TXT
You’ve enabled ODBC Trace SQL Mode and created a sqlout.txt file. But do you know what to do with it now? Let’s explore.

External references

Free keyboard macro program. Supports hotkeys for keyboard, mouse, and joystick. Can expand abbreviations as you type them (AutoText).

Image by Click on 👍🏼👍🏼, consider ☕ Thank you! 🤗 from Pixabay