I am trying to run SQL scripts via VB & ADO.
Whilst I can run the scripts successfully uising the following pretty
standard VB Function, I want to be able to return the SQL result message.
I can return Errors by intercepting the Err.Description when they occur, but
not the success.
(e.g. (11 row(s) affected)).

So essentially my question is, how do you return the result text from a VB
ADODB.Connection.Execute command?

Public Function SqlRunScript(Provider As String, InitialCatalog As String, _
                                             DataSource As String, _
                                            LoginName As String, _
                                             LoginPassword As String, _
                                            strScriptFile As String) As
Boolean

    On Error GoTo ErrorHandler

    Dim strCmd As String
    Dim strBatch As String
    Dim cnnSQL As ADODB.Connection

    Set cnnSQL = New ADODB.Connection
    cnnSQL.Open "PROVIDER= " & Provider  & "Data Source=" & DataSource & _
                            ";Initial Catalog='" & InitialCatalog & _
                            "';User Id=" & LoginName & ";_
                            Password=" & LoginPassword & ";"

    Open strScriptFile For Input As #1

        Do While Not EOF(1)
            Line Input #1, strCmd

            If (Left(strCmd, 2) <> "GO") Then
                strBatch = strBatch + " " + strCmd
            Else
                If strBatch <> "" Then
                    cnnSQL.Execute strBatch
                    TxtResults = TxtResults & strBatch & vbcrlf
                    TxtResults.Refresh
                    strBatch = ""
                End If
            End If
        Loop
    Close #1

    cnnSQL.Close
    Set cnnSQL = Nothing
    SqlRunScript = True

    Exit Function
ErrorHandler:
    MsgBox "Error in SqlRunScript !" & vbCrLf & Err.Description
    TxtResults = TxtResults & Err.Description & vbcrlf
    SqlRunScript = False
    Resume Next
End Function

Thanks in advance
Brian

Execute method of ADO Connection has parameter, which calls RecordAffected.
It returns number of affected records by last executed query. Just pass
variable as aparameter and read that variable after execution has been
completed. But in case of batch execution, that variable should contain ONLY
value of affected records by LAST statement in a batch

--
Val Mazur
Microsoft MVP