ADO command executing multiple SQL statements: cannot receive error message: USE the Connection.Errors assembly

Question

I have triggers on different tables that use raiserror to tell the user that he was trying to do something bad. I would like to launch several attachments and updates atomically, rolling back if one of the triggers says this. I was evil in my former life, so I use Access VBA.

If the first statement is faulty, I get a runtime error in my VBA. However, if the second or subsequent statement raisesror, then I do not see an error message.

SOLUTION: check the connection. Collection of scraps.

This is the Access 2007 ADP front end and the SQL 2005 database.

The following VBA is minimal to show effect. I get the same effect if I complete all of my atomic statements in a stored process.

I thought I was smart by putting my check as close as possible to the database. Obviously not!

If you want to try the code below, replace the order of s1 and s2 in CommandText and enjoy the different results.

Public Sub TestSqlErrors2() 'CREATE TABLE [dbo].[tblStuff]([id] [int] IDENTITY primary key NOT NULL,[stuff] [varchar](255) NULL) Dim cmd As ADODB.Command Dim cnn As ADODB.Connection Dim r As Long Dim s1 As String Dim s2 As String s1 = " insert into tblStuff(stuff) values ('aaa') " s2 = " if 1=1 begin raiserror ('me, i''m just a lawnmower',16,1) rollback transaction end " Set cnn = Application.CurrentProject.Connection Set cmd = New ADODB.Command Set cmd.ActiveConnection = cnn 'cnn.BeginTrans 'makes no difference cmd.CommandText = "begin transaction " & s2 & s1 & " commit transaction " cmd.Execute r 'cnn.CommitTrans 'cnn.Errors.count is either 2 or 0 End Sub 

I tried various combinations. In the stored proc below, I explicitly (re) raise an error. However, this error does not return to VBA.

 --CREATE TABLE [dbo].[tblStuff]([id] [int] IDENTITY primary key NOT NULL,[stuff] [varchar](255) NULL) alter proc AddMoreStuff2 as begin try begin transaction --swap the two statements below around to see the difference --if the first statement is raiserror then a run-time error appears in VBA --if not, no error appears in VBA, although no records are added insert into tblStuff(stuff) values ('aaa') if 1=1 begin raiserror ('me, i''m just a lawnmower',16,1) rollback transaction end commit transaction end try begin catch declare @errormessage varchar(1024) set @errormessage = error_message() raiserror ( @errormessage, 16, 1 ) end catch 

VBA code to call this new proc ...

 Public Sub TestSqlErrors2() Dim cmd As ADODB.Command Dim cnn As ADODB.Connection Dim r As Long Set cnn = Application.CurrentProject.Connection Set cmd = New ADODB.Command Set cmd.ActiveConnection = cnn cmd.CommandText = "exec AddMoreStuff2" cmd.Execute r End Sub 

Answer 1

See http://support.microsoft.com/kb/254304

PRB: ADO error collection does not contain user error Messages

Executing a SQL Server (SP) stored procedure using ActiveX data Objects (ADOs) do not populate the ADO error collection Connection object with user errors that occur in the SP. This behavior only occurs when you use the OLE DB Provider for SQL Server (SQLOLEDB) to establish an ADO connection to a SQL Server database.

You need to explicitly specify nocount at the beginning of your sql script.

Answer 2

The DAO does not cause a VBA error, but it fills the connection. Collection of scraps. Therefore, I can check this after every call to Execute to get a description of the error. eg:.

 Dim errorMessage As String Dim e As Object For Each e In cnn.Errors errorMessage = errorMessage & e.Description Next If Len(errorMessage) > 0 Then Err.Raise 64000 - 1, Left(cmd.CommandText, 20), errorMessage End If