How to get the affected rows in VBA ADO Execute?

Question

The following code errors on the MsgBox cn.RecordsAffected line with:

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

How can I successfully get the affected number of rows? This is for an Access 2003 project. I'd prefer to keep it in 2003 format, so if there's another way to do this, that would be great. I'd like to not have to upgrade the entire project for the sake of this 1 function.

Private Sub Command21_Click()
On Error GoTo Err1:
    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    With cn
        .Provider = "SQL Native Client"
        .ConnectionString = "Server=myserver\myinstance;Database=mydb;Uid=myuser;Pwd=mypass;]"
        .Open
    End With

On Error GoTo Err2:
    cn.Execute "SELECT * INTO someschema.sometable FROM someschema.anothertable"
    MsgBox cn.RecordsAffected
    Exit Sub

Err1:
    MsgBox "Failed to connect to database!"
    Exit Sub

Err2:
    MsgBox Err.DESCRIPTION
    cn.Close

End Sub

Secunderabad

Answer 1

ADODB.Connection does not have a RecordsAffected property. However, the Execute method returns the affected records as a ByRef argument [MSDN]:

Dim recordsAffected As Long
cn.Execute "SELECT * INTO someschema.sometable FROM someschema.anothertable", _
           recordsAffected
MsgBox recordsAffected    

Tafia