PRA: Error In SQL Pass-Through Action Query Not Detected
  
PSS ID Number: Q115897
Article last modified on 03-13-1995
 
2.00
 
WINDOWS
 

---------------------------------------------------------------------
The information in this article applies to:
 
 - Microsoft Access version 2.0
---------------------------------------------------------------------
 
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.
 
1. Start Microsoft Access and open any database.
 
2. Create a new module.
 
3. 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("", False, False, _
         "ODBC;DSN=dabusql;UID=sa;PWD=;DATABASE=mult;")
 
         Set qd = db.CreateQueryDef("")
         qd.SQL = "DROP TABLE tsint2222"  ' Nonexistent table
         qd.Execute                       ' Error expected here
         MsgBox "DONE", 0
         Exit Function
 
      Handler:
         MsgBox Error$
         Exit Function
      End Function
 
4. From the View menu, choose Immediate Window.
 
5. 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.
