ACC: TraceSQLMode Setting Helps Debug SQL Queries to ODBC

ID: Q113918

The information in this article applies to:

Microsoft Access versions 1.1, 2.0, 7.0, 97

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)
   ===================================================================

REFERENCES

For more information about setting options for ODBC, search the Help Index for "registry," or ask the Microsoft Access 97 Office Assistant.

For more information about setting options for ODBC, search for "ODBC Settings" then "Customizing MSACC20.INI Settings" using the Microsoft Access version 2.0 Help menu.

Additional query words: queries tracing sqltrace


Keywords          : kbusage OdbcOthr 
Version           : 1.10 2.0 7.0 97
Platform          : WINDOWS 
Issue type        : kbinfo

Last Reviewed: April 3, 1999