AutoHotKey

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.

Mike Wolfe

Mar 14, 2022 • 6 min read

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:

Click [OK] to close the Edit dialog (no need to close Registry Editor itself)
Close Access if it's already open (msaccess.exe checks the value of the TraceSQLMode registry key at startup)
Open Access
Double-click any linked ODBC table so it opens in datasheet view
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:

Open regedit as an administrator
Navigate to the TraceSQLMode registry value you identified above
Right-click on the parent ODBC key and choose "Permissions..."
Click [Add...] and enter your Windows user-profile name
Click [Check Names] then [OK] to add the record
Select the user record then check the box to Allow [√] Full Control
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.

Download and install AutoHotKey (v1.x) if you don't already have it
Create a blank file in your favorite text editor
Paste the code below into the text editor
Update the two lines that begin with TraceSql in the "Configuration" section to match your environment
Save the file with an .ahk file extension
Double-click the saved file in File Explorer to launch the script
Press Ctrl + Win + O to test the feature (you should see a MsgBox that says, "TraceSQLMode set to ON/OFF")
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 https://nolongerset.com/procmon-troubleshoot-registry-calls/
    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%'
            Else
                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%'
            Else
                MsgBox TraceSQLMode set to OFF
    }
    SetRegView Default
    Return

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:

HKLM\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE

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.