VBA ADODB update recordset

Question

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.

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

Can anyone help with this?

Answer 1

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

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