ACC: TraceSQLMode Setting Helps Debug SQL Queries to ODBC

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

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

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

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;

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