Microsoft KB Archive/113918

= ACC: TraceSQLMode Setting Helps Debug SQL Queries to ODBC =

Article ID: 113918

Article Last Modified on 1/18/2007

-

APPLIES TO


 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q113918



IMPORTANT: This article contains information about editing the *registry. Before you edit the registry, you should first make a backup copy of *the registry files (System.dat and User.dat). Both are hidden files in the Windows folder.



SUMMARY
Advanced: Requires expert coding, interoperability, and multiuser skills.

Microsoft Access can record all SQL statements sent to an ODBC data source in a file called Sqlout.txt. You can create this log file by setting the TraceSQLMode setting to 1.



MORE INFORMATION
To create a log file of all the SQL queries sent to ODBC for processing, do the following.

In Microsoft Access 7.0 and 97
WARNING: Using Registry Editor incorrectly can cause serious problems that may require you to reinstall Windows 95. Microsoft cannot guarantee that problems resulting from the incorrect use of Registry Editor can be solved. Use Registry Editor at your own risk.

For information about how to edit the registry, view the Changing Keys And Values online Help topic in Registry Editor (Regedit.exe). Note that you should make a backup copy of the registry files (System.dat and User.dat) before you edit the registry.

Set the TraceSQLMode parameter to 1 in the Registry File. Make this entry to the following registry path:

In Microsoft Access 97:

  \HKEY_LOCAL_MACHINE\Software\Microsoft\Office\8.0\Access\Jet\3.5 \Engines\ODBC

In Microsoft Access 7.0:

  \HKEY_LOCAL_MACHINE\Software\Microsoft\Access\7.0\Jet\3.0 \Engines\ODBC

Before you can set the TraceSQLMode, you may have to create the ODBC Key and the TraceSQLMode DWORD value. The Sqlout.txt file generated is placed in your current directory. You can identify your current directory by evaluating ?CurDir in the Debug window.

For more information about creating this Registry file entry, search for "TraceSQLMode" using the Find option in the Microsoft Access Help Topics.

In Microsoft Access 1.x and 2.0
Set the TraceSQLMode setting in the [ODBC] section of your Access .ini file as shown below. The Access .ini file for version 2.0 is Msacc20.ini and for version 1.x is Msaccess.ini. The Sqlout.txt file is placed in your Microsoft Access folder.

The setting is:

  [ODBC] TraceSQLMode=1

To turn the TraceSQLMode option off, change the setting from 1 to 0.

NOTE: After setting this option, you must restart Microsoft Access for the change to take effect. SQL statements sent to an ODBC data source continue to be recorded in the Sqlout.txt file as long as the TraceSQLMode setting is set to 1.

A sample of the SQL statement from the View SQL window in a Microsoft Access query follows:

  SELECT DISTINCTROW dbo_authors.au_id, dbo_authors.au_lname, dbo_authors.au_fname, dbo_authors.phone, dbo_authors.address, dbo_authors.city, dbo_authors.state, dbo_authors.zip, dbo_authors.contract FROM dbo_authors;

Here's a sample Sqlout.txt file:

  ================= Open the attached dbo.authors ==================

SQLExecDirect: SELECT dbo.authors.au_id FROM dbo.authors SQLExecDirect: SELECT au_id,au_lname,au_fname,phone, address,city,state,zip,contract FROM dbo.authors SQLPrepare: SELECT au_id,au_lname,au_fname,phone,address,city, state,zip,contract FROM dbo.authors  WHERE au_id = ? OR  au_id = ? OR au_id = ? OR au_id = ? OR au_id = ? OR  au_id = ? OR au_id = ? OR au_id = ? OR au_id = ? OR au_id = ? SQLExecute: (MULTI-ROW FETCH) ===================================================================

