INF: Addressing Access Conformance Errors in the SQL ODBC Drvr (137633)

MORE INFORMATION

Microsoft Access makes heavy use of the ODBC catalog API functions to determine the capabilities of the ODBC drivers to which it connects and the structure of attached external tables. If the values returned on these calls to catalog functions are not exactly those expected by Access, Access raises a conformance error:

All Access errors in the 7700s are conformance errors. The Microsoft whitepaper "Jet Database Engine ODBC Connectivity" (available on the MSDN and TechNet CDs) contains a list of the conformance errors and the specific check which leads to the error.

Conformance errors encountered with the Microsoft SQL Server ODBC driver rarely indicate a problem with the driver itself. Because Access strictly checks the values returned from the ODBC catalog functions, any problems in the environment can raise conformance errors. For example, if there is a problem in the underlying network that raises a network error on a catalog API call from Access, Access will report this as a conformance error because it did not get the answer it expected.

If you get conformance errors from the Microsoft ODBC SQL Server driver, you should check the following:

1. The most common cause of conformance errors is that the driver is being run against a server which does not have the proper version of the SQL Server system catalog stored procedures. To correct this, the DBA should run the INSTCAT.SQL script associated with the latest version of the driver being used against the server. For example, if both clients using the 2.00.1912 driver (which shipped with SQL Server version 4.21a) and the 2.50.0121 driver (which shipped with SQL Server version 6.0) are being run against a server, then the INSTCAT.SQL that shipped with SQL Server 6.0 and the 2.50.0121 driver should be run against that server. The INSTCAT.SQLs are backwards compatible, so you can run the version 6.0 INSTCAT.SQL into a version 4.21a server and still access that server with 2.00.1912 drivers.
2. If the conformance errors are related to a specific table or group of tables, there may be a problem with the table. Run the following dbcc commands in SQL Server, and address any errors as directed in the SQL Server 6.0 "Administrator's Companion" or the SQL Server 4.21a "Troubleshooting Guide":
   
   DBCC CHECKTABLE(problem_table)
   DBCC CHECKCATALOG
3. Older versions of the Jet Database Engine in Access 2.0 are more prone to generating this conformance error message with SQL Server drivers. Check the version of the MSAJT200.DLL module by selecting the module in File Manager, then pressing ALT+ENTER. If the version number is earlier than 2.50.1606, see Knowledge Base Article 122927 for instructions on getting the Access 2.0 Service Pack.
4. Check the general connectivity from the client to the SQL Server. Make sure that the SQL Server client utilities such as ISQL/w can successfully connect. Follow the connectivity troubleshooting steps outlined in the section 'Isolating Connection Problems' of either the SQL Server 6.0 "Administrator's Companion" or the SQL Server 4.21a "Troubleshooting Guide."
5. If the ODBC SDK is installed on the client, rerun the steps that generate the Access conformance error while spying on the ODBC data source with the ODBCSpy utility. You should be able to see the specific ODBC error causing the conformance error captured in a call to the SQLError ODBC function. Also, the particular ODBC API function which raises the specific conformance error reported by Access can be determined from the list of specification errors in the "Jet Database Engine ODBC Connectivity" whitepaper. A user can then search the spy trace for that particular function and see if there was an error associated with it.