How to find the result of an ADO Query in MS Access

Question

MS Access 2019 SQL Server 2017

Is there any straight, simple way in the VBA code, to find out if this query resulted in Commit or Rollback.

This is not insoluble, but I'm looking for a not too elaborate way of doing it.

In MS Access Module:

  Dim SQLDB As Object
  Dim ADOcom As Object
  Dim sql As String

  sql = _
    "BEGIN TRY " & _
    "  BEGIN TRAN " & _
    "    UPDATE MyTable SET MyColumn=100 WHERE AnotherColumn=5 " & _
    "  COMMIT TRAN " & _
    "END TRY " & _
    "" & _
    "BEGIN CATCH " & _
    "  ROLLBACK TRAN " & _
    "END CATCH"
  
  Set SQLDB = CreateObject("ADODB.Connection")
  Set ADOcom = CreateObject("ADODB.Command")
  SQLDB.Open "Driver={SQL Server Native Client 11.0};Server=SQL;Database=MyDatabase;Trusted_Connection=yes;"
  SQLDB.CursorLocation = adUseClient
  Set ADOcom.activeconnection = SQLDB
  With ADOcom
    .CommandText = sql
    .Execute sql

' Code to find out if the .execute resulted in Commit

  End With
  Set ADOcom = Nothing
  SQLDB.Close
End Sub

Completion: In this example, two querys are sent to an arbitrary SQL Server from an arbitrary MS Access Module. The first query returns the expected value, but the second query does not. Both querys works fine in SSMS. What is wrong in the second query?

Sub Example()
  Dim SQLDB As Object
  Dim query_1 As String, query_2 As String
  Dim rs As Object
  
  Set SQLDB = CreateObject("ADODB.Connection")
  SQLDB.Open "Driver={SQL Server Native Client 11.0};Server=MySQLserver;Database=MyDatabase;Trusted_Connection=yes;"
  Set rs = CreateObject("ADODB.Recordset")
  Set rs.activeconnection = SQLDB
  
  query_1 = _
    "SELECT 1"
  
  rs.Open query_1
  Debug.Print rs(0) ' Expected 1, got 1.
  rs.Close
  
  query_2 = _
    "DECLARE @Success INT " & _
    "BEGIN TRY " & _
    "  BEGIN TRAN " & _
    "    SELECT 1/0 " & _
    "    SET @Success=1 " & _
    "  COMMIT TRAN " & _
    "END TRY" & _
    " " & _
    "BEGIN CATCH " & _
    "  ROLLBACK TRAN " & _
    "  SET @Success=0 " & _
    "END CATCH " & _
    "SELECT @Success AS [Success]"

  rs.Open query_2
  Debug.Print rs(0) ' Expected 0 but rs is Nothing and got Error 3265
  rs.Close
  
  SQLDB.Close
End Sub

Answer 1

You can return a status value or @variable. For example,

  sql = _
    "BEGIN TRY " & _
    "  BEGIN TRAN " & _
    "    UPDATE MyTable SET MyColumn=100 WHERE AnotherColumn=5 " & _
    "    SELECT 0 AS [ErrorStatus] " & _
    "  COMMIT TRAN " & _
    "END TRY " & _
    "" & _
    "BEGIN CATCH " & _
    "  ROLLBACK TRAN " & _
    "  SELECT 1 AS [ErrorStatus] " & _
    "END CATCH"

You can consume this result in the following code. In the CATCH you can use THROW to get the original error and still having the transaction rollbacked:

BEGIN CATCH
      ROLLBACK TRAN;
      THROW;
END CATCH

which mean you need to have a try/catch in the vb.code itself. Another approach if you need the error details is to use the following functions (execute this in the SSMS):

BEGIN TRY  
    -- Generate a divide-by-zero error.  
    SELECT 1/0;  
END TRY  
BEGIN CATCH  
    SELECT  
        ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_SEVERITY() AS ErrorSeverity  
        ,ERROR_STATE() AS ErrorState  
        ,ERROR_PROCEDURE() AS ErrorProcedure  
        ,ERROR_LINE() AS ErrorLine  
        ,ERROR_MESSAGE() AS ErrorMessage;  
END CATCH;  
GO

They are returning details of the error. You can return them or assigned them to a variable and process it later.