title: VBA ADODB update recordset
body: <p>I am trying to read the contents of two different tabs in a worksheet and compare them by using ADODB and querying techniques VBA.</p>
  
  <p>Below you can find my code:</p>
  
  <pre><code>stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" _
   &amp; "Data Source=" &amp; wbBook.FullName &amp; ";" _
   &amp; "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 &amp; " ([Recon_Daily_Xml_report$].NUMOFENTRIES - [GL_Activity_totals$].NUMOFTRXNS) as Count_Diff "
  'stSQL = stSQL &amp; " FROM [Recon_Daily_Xml_report$], [GL_Activity_totals$]"
  'stSQL = stSQL &amp; " WHERE Lower([Recon_Daily_Xml_report$].RECTYPEGLEDGER) = Lower([GL_Activity_totals$].TRXNTYPE)"
  'stSQL = stSQL &amp; " 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 &amp; " FROM [GL_Activity_totals$] "
  stSQL = stSQL &amp; " Group By [GL_Activity_totals$].TRXNTYPE "
  stSQL = stSQL &amp; " 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 &lt;&gt; "" Then
         strString = Replace(rst.Fields.Item(0).Value, "  ", " ")
  
          rst.Update
          rst.Fields.Item(0) = strString
  
      End If
      .MoveNext
      Loop
  
  End With
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>Run-time error '-2147217911 Cannot update. Database or object is read-only.
  </code></pre>
  
  <p>Tried to change the way i open the recordset by using 1,3 option but again i was getting the same error.</p>
  
  <p>Can anyone help with this?</p>