How to get affected lines in VBA ADO Execute?

Question

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

Arguments are of the wrong type, are out of range, or are in conflict with each other.

How can I successfully get the affected row count? This is for the Access 2003 project. I would prefer to save it in the 2003 format, so if there is another way to do this, it will be great. I would not want to update the whole 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 

Answer 1

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

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