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.

3 Ways to Find sqlout.txt

Microsoft Access comes with an ODBC tracing tool known as ODBC TraceSQLMode.

To enable this tool, you first need to make some changes to your registry.  Once that's done, Microsoft Access will start logging query plans to a file named sqlout.txt.  Actually finding the sqlout.txt file, though, can be deceptively hard.

Read below for three techniques–in order from simplest to most reliable–that will take all the guesswork out of finding the sqlout.txt file.

Check Your Documents Folder

The first place to check is in your user profile's \Documents\ folder.

That's typically where the sqlout.txt file is created.  However, that's not always the case.  Furthermore, sometimes there is a sqlout.txt file there from a previous session.  The current sqlout.txt file could be located somewhere else.

TIP! Always check the Modified Date on the sqlout.txt file to make sure you are looking at the right one.

Use the CurDir Function

Open Microsoft Access then go to the Immediate Window ([Ctrl] + [G]).  Run the CurDir() function to output the current working directory:

The current directory is often–but not always!–your user profile's Documents folder.

If you open the outputted path in File Explorer, you should see the sqlout.txt file there.

Keep in mind that your Access application's startup code could potentially change this path.  So, while it's more reliable than the first method, it's still not foolproof.

Search with Everything

I love Everything.

If you can't find sqlout.txt using either of the previous methods, I guarantee this one will work for you.  It has the added bonus of introducing you to the Everything utility which has been a complete game changer for my daily workflow.

  1. Search for sqlout.txt
  2. Sort by "Date Modified" in descending order

The screenshot above is from one of my development machines.  As you can see, the sqlout.txt file appears in two different locations.  So, while the TraceSQLMode output usually goes to my Documents folder, that's not always the case.

Still Can't Find sqlout.txt?

If you tried all three of the above techniques and you still can't find the sqlout.txt file, then it means the problem is probably in the Windows Registry where you have to enable the feature in the first place.

Don't worry, though, I've got you covered there, too.  You can use a different Windows utility–Process Monitor–to figure out exactly which registry entry you need to set to enable ODBC TraceSQLMode.  I cover all of those details in my article, Using ProcMon to Troubleshoot Registry Calls.

One final note:  if you have to enable ODBC TraceSQLMode in the registry, that setting won't take effect until after you exit and restart Microsoft Access.

External references

How does Access talk to ODBC data sources? Part 1, by Ben Clothier

Referenced articles

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.
Everything You Need to Find Files
Or, should I say, You Need *Everything* to Find Files.

All original code samples by Mike Wolfe are licensed under CC BY 4.0