ACC: TraceSQLMode Setting Helps Debug SQL Queries to ODBC (113918)
The information in this article applies to:
- Microsoft Access 1.1
- Microsoft Access 2.0
- Microsoft Access for Windows 95 7.0
- Microsoft Access 97
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)
===================================================================
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.
Modification Type: | Major | Last Reviewed: | 5/6/2003 |
---|
Keywords: | kbinfo kbusage KB113918 |
---|
|