ACC2000: TraceSQLMode Setting Helps Debug SQL Queries to ODBC (209082)


The information in this article applies to:

  • Microsoft Access 2000
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:

Log entryDescription
SQLExecDirect: SQL-stringThe calling application executed a non-parameter user query
SQLPrepare: SQL-stringThe calling application prepared a parameter user query.
SQLExecute: (PARAMETERIZED QUERY)The calling application executed a prepared, parameter user query.
SQLExecute: (GOTO BOOKMARK)The calling application retrieved a single record based on a bookmark.
SQLExecute: (MULTI-RECORD FETCH)The calling application retrieved multiple records based on multiple bookmarks.
SQLExecute: (MEMO FETCH)The calling application retrieved a Memo field for a single record based on a bookmark.
SQLExecute: (GRAPHIC FETCH)The calling application retrieved a Long Binary (OLE Object) field for a single record based on a bookmark.
SQLExecute: (RECORD-FIXUP SEEK)The calling application retrieved a single record based on an index key (not necessarily the bookmark index).
SQLExecute: (UPDATE)The calling application updated a single record based on a bookmark.
SQLExecute: (DELETE)The calling application deleted a single record based on a bookmark.
SQLExecute: (INSERT)The calling application inserted a single record in dynaset mode.
SQLExecute: (SELECT INTO insert)The calling application inserted a single record in export mode.

REFERENCES

For more information about setting options for ODBC, click Microsoft Access Help on the Help menu, type Customize ODBC driver settings in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MajorLast Reviewed:6/29/2004
Keywords:kbinfo kbusage KB209082
©2004 Microsoft Corporation. All rights reserved.