title: ADO Command running multiple SQL statements: can't get error message back: USE THE Connection.Errors collection
body: <p>I have some triggers on various tables that use raiserror to tell the user that he tried to do something bad. I would like to run several inserts and updates atomically, rolling back if one of the triggers says so. I was evil in a former life so I'm using Access VBA.</p>
  
  <p>If the first statement is at fault, I get a run time error in my VBA. However if the second or subsequent statement calls raiserror then I can't see any error message.</p>
  
  <p>SOLUTION: examine the connection.Errors collection.</p>
  
  <p>This is an Access 2007 ADP front-end and a SQL 2005 database.</p>
  
  <p>The VBA below is the bare minimum to show the effect. I get the same effect if I wrap all my atomic statements in a stored proc.</p>
  
  <p>I thought i was being clever, putting my validation as close to the database as possible. Obviously not!</p>
  
  <p>If you care to try out the code below, swap the order of s1 and s2 in CommandText and admire the different results.</p>
  
  <pre class="lang-vba prettyprint-override"><code>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 " &amp; s2 &amp; s1 &amp; " commit transaction "
  cmd.Execute r
  'cnn.CommitTrans
  'cnn.Errors.count is either 2 or 0
  End Sub
  </code></pre>
  
  <p>I've tried various combinations. In the stored proc below I'm explicitly (re-) raising an error. However, that error doesn't get passed back to VBA.</p>
  
  <pre class="lang-sql prettyprint-override"><code>--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
  </code></pre>
  
  <p>The VBA code to call this new proc is...</p>
  
  <pre class="lang-vba prettyprint-override"><code>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
  </code></pre>