VBA ADODB更新记录集 [英] VBA ADODB update recordset

问题描述

我正在尝试阅读工作表中两个不同选项卡的内容，并使用ADODB和查询技术VBA进行比较。

I am trying to read the contents of two different tabs in a worksheet and compare them by using ADODB and querying techniques 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.

尝试改变我使用1,3选项打开记录集的方式，但是我再次获得相同的错误。

Tried to change the way i open the recordset by using 1,3 option but again i was getting the same error.

任何人都可以帮忙吗？

推荐答案

问题是与

LCASE([GL_Activity_totals$].TRXNTYPE)

和GROUP BY。
在这种情况下，rst.Fields.Item（0）是一个表达式，而不是表值。您无法更新表达式。此外，由于您使用GROUP BY，所以记录集并未链接到任何特定的记录以进行编辑。您可以在SQL中完成相同的任务

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

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 <> '';")

这篇关于VBA ADODB更新记录集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！