如何在VBA ADO Execute中获取受影响的行?

问题描述

MsgBox cn.RecordsAffected行上的以下代码错误:

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

如何成功获取受影响的行数?这是用于Access 2003项目.我希望将其保留为2003格式，因此，如果还有其他方法可以做到，那就太好了.为了实现此1功能，我不想升级整个项目.

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

推荐答案

ADODB.Connection没有RecordsAffected属性.但是，Execute方法返回受影响的记录作为ByRef参数[ MSDN ]:

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

这篇关于如何在VBA ADO Execute中获取受影响的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！