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

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/12676747/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 17:55:34  来源:igfitidea点击:

How to get the affected rows in VBA ADO Execute?

vbams-accessaccess-vbams-access-2007ado

提问by Danny Beckett

The following code errors on the MsgBox cn.RecordsAffectedline with:

以下代码错误就MsgBox cn.RecordsAffected行了:

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.

如何成功获得受影响的行数?这是针对 Access 2003 项目的。我更愿意将它保留为 2003 格式,所以如果有另一种方法可以做到这一点,那就太好了。我不想为了这 1 个功能而升级整个项目。

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

回答by Heinzi

ADODB.Connectiondoes not have a RecordsAffectedproperty. However, the Executemethod returns the affected records as a ByRefargument [MSDN]:

ADODB.Connection没有RecordsAffected财产。但是,该Execute方法将受影响的记录作为ByRef参数返回[ MSDN]:

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