Microsoft KB Archive/209082

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

Article ID: 209082

Article Last Modified on 6/29/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q209082



IMPORTANT: This article contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base:

256986 Description of the Microsoft Windows Registry

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

This article applies only to a Microsoft Access database (.mdb).



SUMMARY
Microsoft Access can record all SQL statements sent to an Open Database Connectivity (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, follow these steps:

WARNING: If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.

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

HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\ODBC

Before you can set the TraceSQLMode, you may need 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 Immediate window.

The setting is:   [ODBC] TraceSQLMode=1 To turn off the TraceSQLMode option, 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 SQL view 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;

A sample Sqlout.txt file may appear as follows:

SQLExecDirect: SELECT Config, nValue FROM MSysConf

SQLExecDirect: SELECT "dbo"."authors"."au_id" FROM "dbo"."authors"

The following table outlines log entries that may appear in the Sqlout.txt file:

