Microsoft KB Archive/115897

PRA: Error In SQL Pass-Through Action Query Not Detected

PSS ID Number: Q115897 Article last modified on 03-13-1995

2.00

WINDOWS

= SYMPTOMS =

When an error occurs while you are performing a SQL pass-through action query on a remote SQL server, the error is not reported to Microsoft Access.

= CAUSE =

Microsoft Access does not detect errors that occur while performing data- definition queries.

= RESOLUTION =

Avoid using data-definition queries with remote SQL servers. Instead, perform these functions (DROP TABLE, CREATE TABLE, and so on) from the Admin utility included with SQL Server.

If you use DB_FAILONERROR as an option for action queries, errors will be detected.

= STATUS =

This behavior is by design.

= MORE INFORMATION =

This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information on Access Basic, please refer to the “Building Applications” manual.

Steps to Reproduce Problem
This example attempts to delete a nonexistent table from a SQL Server database. Use your own database information in the “Set db = DBEngine(0)” line in this example.

  Start Microsoft Access and open any database.   Create a new module.   Enter the following function in the module. NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic. Function test On Error GoTo Handler Dim db As Database, qd As QueryDef Set db = DBEngine(0).OpenDatabase(&quot;&quot;, False, False, _ &quot;ODBC;DSN=dabusql;UID=sa;PWD=;DATABASE=mult;&quot;)

Set qd = db.CreateQueryDef(&quot;&quot;) qd.SQL = &quot;DROP TABLE tsint2222&quot; ' Nonexistent table qd.Execute                      ' Error expected here MsgBox &quot;DONE&quot;, 0 Exit Function Handler: MsgBox Error$ Exit Function End Function   From the View menu, choose Immediate Window.   Type the following in the Immediate window, and then press ENTER: ? Test 

Note that you do not receive any error message.

Additional reference words: 2.00 dao programming queries KBCategory: kbinterop KBSubcategory: ObcSqlms ============================================================================= Copyright Microsoft Corporation 1995.