Return database messages on successful SQL execution when using ADO

Question

I'm working on a legacy VB6 app here at work, and it has been a long time since I've looked at VB6 or ADO. One thing the app does is to executes SQL Tasks and then to output the success/failure into an XML file. If there is an error it inserts the text the task node.

What I have been asked to do is try and do the same with the other mundane messages that result from succesfully executed tasks, like (323 row(s) affected).

There is no command object being used, it's just an ADODB.Connection object. Here is the gist of the code:

Dim sqlStatement As String
Set sqlStatement = /* sql for task */

Dim sqlConn As ADODB.Connection
Set sqlConn = /* connection magic */

sqlConn.Execute sqlStatement, , adExecuteNoRecords

What is the best way for me to capture the non-error messages so I can output them? Or is it even possible?

Answer 1

The number of rows affected is returned through an optional second argument of the Connnection object's Execute method

Dim num As Long    
sqlConn.Execute sqlStatement, num, adExecuteNoRecords     
MsgBox num & " records were affected"