Search code examples
vbaadodbmultiple-records

VBA ADODB - Get multiple rows affected from single query


I am running an update query to SQL Server through VBA using VBA's ADODB connection class. In SQL Server the query returns rows affected which can be accessed in VBA through the ADODB command class.

However I have no idea how to return an array of multiple records affected statements.

I am using the sample code:

Dim ADODBconn As ADODB.Connection
Dim rsRead As New ADODB.Recordset
Dim Rslt_Count As Long

'Set Up Connection and begin SQL Transaction
Set ADODBconn = CurrentProject.AccessConnection
ADODBconn.BeginTrans

'Build Update SQL
SQLLine = "******update query that runs on multiple tables here********"

'Execute SQL
ADODBconn.Execute SQLLine, Rslt_Count, dbFailOnError

MsgBox Rslt_Count
ADODBconn.CommitTrans
ADODBconn.Close

Any help appreciated. Thanks Richard


Solution

  • Let us say you have a stored procedure:

    CREATE PROCEDURE UpdateTables
    AS
    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;
        DECLARE @RowCount2 INTEGER
        DECLARE @RowCount3 INTEGER
        DECLARE @RowCount4 INTEGER
    
        UPDATE Table2 Set atext = 'a' WHERE atext='b' 
        SELECT @RowCount2 = @@ROWCOUNT
        UPDATE Table3 Set atext = 'a' WHERE atext='b' 
        SELECT @RowCount3 = @@ROWCOUNT
        UPDATE Table4 Set atext = 'a' WHERE atext='b' 
        SELECT @RowCount4 = @@ROWCOUNT
    
        SELECT @RowCount2 AS Table2, @RowCount3 AS Table3, @RowCount4 AS Table4
    END
    

    As per Return number of rows affected by UPDATE statements

    See also : access-SQL pass-through query (creating SP) error

    You can then:

    Dim dbconn As ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim rs As New ADODB.Recordset
    Dim param As New ADODB.Parameter
    
    Set dbconn = New ADODB.Connection
    dbconn.ConnectionString = ServerConLocal
    
    dbconn.Open 
    
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = dbconn
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "updatetables"
    
    rs.CursorType = adOpenStatic
    rs.CursorLocation = adUseClient
    rs.LockType = adLockOptimistic
    rs.Open cmd
    
    ''Records affected
    For Each fld In rs.Fields
        Debug.Print fld.Name & " : " & fld
    Next
    End Sub
    

    You can also simply create a pass-through query with the single line:

    UpdateTables
    

    Which might return:

        Table2  Table3  Table4
        0       2       0