VBA ADODB 更新记录集
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20998502/
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
VBA ADODB update recordset
提问by user3122285
I am trying to read the contents of two different tabs in a worksheet and compare them by using ADODB and querying techniques VBA.
我正在尝试读取工作表中两个不同选项卡的内容，并通过使用 ADODB 和 VBA 查询技术来比较它们。
Below you can find my code:
您可以在下面找到我的代码：
stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & wbBook.FullName & ";" _
& "Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;Readonly = False"";"
' MsgBox (stCon)
'here is SQL code to gather data including our calculation from two tables within the workbook
'stSQL = "SELECT [Recon_Daily_Xml_report$].RECTYPEGLEDGER, [GL_Activity_totals$].TRXNTYPE, ([Recon_Daily_Xml_report$].Amount_Abs - [GL_Activity_totals$].BILLINGAMT) as Amount_Diff ,"
'stSQL = stSQL & " ([Recon_Daily_Xml_report$].NUMOFENTRIES - [GL_Activity_totals$].NUMOFTRXNS) as Count_Diff "
'stSQL = stSQL & " FROM [Recon_Daily_Xml_report$], [GL_Activity_totals$]"
'stSQL = stSQL & " WHERE Lower([Recon_Daily_Xml_report$].RECTYPEGLEDGER) = Lower([GL_Activity_totals$].TRXNTYPE)"
'stSQL = stSQL & " ORDER BY [Recon_Daily_Xml_report$].RECTYPEGLEDGER ASC"
stSQL = "SELECT LCASE([GL_Activity_totals$].TRXNTYPE),Sum(ABS([GL_Activity_totals$].BILLINGAMT)),Sum([GL_Activity_totals$].NUMOFTRXNS) "
stSQL = stSQL & " FROM [GL_Activity_totals$] "
stSQL = stSQL & " Group By [GL_Activity_totals$].TRXNTYPE "
stSQL = stSQL & " ORDER BY [GL_Activity_totals$].TRXNTYPE ASC"
'MsgBox (stSQL)
Set cnt = New ADODB.Connection
Set rst = New ADODB.Recordset
cnt.Open stCon
'rst.Open stSQL, cnt, 1, 3
rst.Open stSQL, cnt, adOpenStatic, adLockOptimistic
'rst.Open strSQL, cnt, adOpenStatic, adLockOptimistic
With rst
Do While Not .EOF
If rst.Fields.Item(0).Value <> "" Then
strString = Replace(rst.Fields.Item(0).Value, " ", " ")
rst.Update
rst.Fields.Item(0) = strString
End If
.MoveNext
Loop
End With
This specific code gives me back an error suggesting that I cannot update the field in the recordset I want to update when reading it. The error I am currently getting is:
这个特定的代码给了我一个错误，提示我在读取时无法更新我想要更新的记录集中的字段。我目前得到的错误是：
Run-time error '-2147217911 Cannot update. Database or object is read-only.
Tried to change the way i open the recordset by using 1,3 option but again i was getting the same error.
试图通过使用 1,3 选项更改我打开记录集的方式，但我再次遇到相同的错误。
Can anyone help with this?
有人能帮忙吗？
回答by serakfalcon
The issue is with
问题是
LCASE([GL_Activity_totals$].TRXNTYPE)
and with the GROUP BY. In this case, rst.Fields.Item(0) is an expression, not a table value. You can't update expressions. Also, since you're using GROUP BY, the recordset is not linked to any particular record for access to edit. You could accomplish the same task purely in SQL
和 GROUP BY。在这种情况下， rst.Fields.Item(0) 是一个表达式，而不是一个表值。您无法更新表达式。此外，由于您使用的是 GROUP BY，因此记录集未链接到任何特定记录以进行编辑访问。您可以完全在 SQL 中完成相同的任务
cnt.Execute("UPDATE [GL_Activity_totals$] " & _
" SET [GL_Activity_totals$].TRXNTYPE = Substitute([GL_Activity_totals$].TRXNTYPE,' ', ' ') " & _
" WHERE NOT [GL_Activity_totals$].TRXNTYPE IS NULL " & _
" AND [GL_Activity_totals$].TRXNTYPE <> '';")
