如何在 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
How to get the affected rows in VBA ADO Execute?
提问by Danny Beckett
The following code errors on the MsgBox cn.RecordsAffected
line 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.Connection
does not have a RecordsAffected
property. However, the Execute
method returns the affected records as a ByRef
argument [MSDN]:
ADODB.Connection
没有RecordsAffected
财产。但是,该Execute
方法将受影响的记录作为ByRef
参数返回[ MSDN]:
Dim recordsAffected As Long
cn.Execute "SELECT * INTO someschema.sometable FROM someschema.anothertable", _
recordsAffected
MsgBox recordsAffected