The following sections describe various MSACC20.INI settings in the [ODBC] section that may help with understanding unexpected behavior when using remote data sources. For other settings that may be useful, search for ODBC Settings in the Microsoft Access 2.0 Help.
Asynchronous processing might cause unpredictable results with some network libraries and some servers. These network libraries are often more robust when operating synchronously, owing chiefly to the added complexities of handling and testing multiple asynchronous connections. Client applications are often written to operate fully synchronously, even if interactive; this is simpler to implement and test. You can force Jet to operate synchronously by setting DisableAsync to 1 in the MSACC20.INI file. Also notify your network/server vendor; an upgrade or patch might be available for these problems.
Disabling asynchronous processing is also very useful if you're trying to read the trace of statements sent to the server with TraceSQLMode or TraceODBCAPI options.
You can examine the SQL statements for queries executed by the Jet engine against an ODBC data source by adding a TraceSQLMode=1 entry to the [ODBC] section of your MSACC20.INI or your run-time application's APPNAME.INI file. The SQL statements sent by Jet to the ODBC driver appear in a file called SQLOUT.TXT in your ACCESS directory or your application's current directory. Following are the first few entries in SQLOUT.TXT typically generated:
SQLExecDirect: SELECT Config, nValue FROM MSysConf SQLExecDirect: SELECT dbo.tblUsers.Contact_ID FROM dbo.tblUsers SQLExecDirect: SELECT Contact_ID,Initials,StartDate,EndDate,LoginName,Password,TimeSlotType,TimeSlotStart,FirstDay,UseAlarms,AlarmInterval FROM dbo.tblUsers SQLExecDirect: SELECT dbo.tblUsers.Contact_ID FROM dbo.tblUsers WHERE (LoginName = 'admin' ) SQLPrepare: SELECT Contact_ID,LoginName,Password FROM dbo.tblUsers WHERE Contact_ID = ?
The first query executed by Jet when you create a connection to the database is the query to determine if the MSysConf exists and, if it exists, return the Config and nValue data for each of the rows in the table. This is used to determine if the password should be stored for the attached tables and what the background population parameters are. Each time your application executes a query, records are appended to SQLOUT.TXT, so you'll want to periodically erase the SQLOUT.TXT file or disable writing to it.
In extreme cases, you may wish to see what the actual ODBC calls are that Jet is making. You can examine the ODBC calls executed by the Jet by adding a TraceODBCAPI=1 entry to the [ODBC] section of your MSACC20.INI or your run-time application's APPNAME.INI file. The SQL statements sent by the ODBC driver to the ODBC server appear in a file called ODBCAPI.TXT in your ACCESS directory or your application's current directory. If you're trying to trace calls at this level, setting DisableAsync will make the trace much easier to follow.
This log can become exceedingly large. If you use either of the Trace... options, make sure to delete the .TXT files periodically. These files are recreated on the next entry after you delete them.
By default, Jet will try to log on to an ODBC database with the same user name and password that the user provided for Jet. If you are using a slow communication line to a server and have different server user names and passwords, you can avoid the overhead of this extra call by setting TryJetAuth to 0 (no).
This is an indicator of whether to match table names exactly when attaching. Values are 0 (attach the first table matching the specified name, regardless of case) and 1 (attach a table only if the name matches exactly). The default is 0. Although most ODBC servers are case insensitive, this is useful if you're using one which isn't.
This is a list of server object types to which attaching will be allowed. The default is 'TABLE','VIEW','SYSTEM TABLE','ALIAS', 'SYNONYM'. When connecting to Oracle, you may wish to remove SYNONYM from the above list to avoid an "Invalid Column Name" error message that happens with some Oracle synonym queries (see the Knowledge Base article Q108439 for more details).