title: VBA code to loop and update MS access database column from Excel
body: <p><strong>Background:</strong>
  I have an excel spreadsheet that retrieves data from an MS Access database. That code works fine. It retrieves records that have the "comments" field as blank. Users update the comments field in Excel and click a button.</p>
  
  <p><strong>The Ask:</strong> Once the button is clicked, the VBA code must loop through all retrieved records in my excel sheet and those records that are marked "completed" in excel must update the same comment in the "comments field" in my database.</p>
  
  <p>I have looked at this article and <strong>Gord Thompson</strong> posted some code that could work for my situation; except that i dont know how to tailor that code to work for me :(
  Link--
  <a href="https://stackoverflow.com/questions/15709156/vba-code-to-update-create-new-record-from-excel-to-access">VBA code to update / create new record from Excel to Access</a></p>
  
  <p>**Snapshot of the structure of my database and excel at this ** <a href="http://pallottichurchbangalore.org/structure.zip" rel="nofollow noreferrer">link</a></p>
  
  <p>excel:
  <img src="https://i.stack.imgur.com/FgRML.png" alt="enter image description here"></p>
  
  <p>database:
  <img src="https://i.stack.imgur.com/nhxwO.png" alt="enter image description here"></p>
  
  <p><strong>Will this code work</strong></p>
  
  <pre><code>Sub Update()
  Dim cn As ADODB.Connection, rs As ADODB.Recordset
  Dim xComments As String
  Dim xType As String
  Dim xIBES_Ticker As String
  Dim xEditor As String
  Dim xPRD_Year As String
  Dim xPRD_Month As String
  Dim xEvent_Date As String
  Dim xReporting As String
  Dim xNotes As String
  
  ' connect to the Access database
  Set cn = New ADODB.Connection
  cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " &amp; _
  "Data Source=C:\Database1.mdb;"
  
  ' open a recordset
  Set rs = New ADODB.Recordset
  rs.Open "tablename", cn, adOpenKeyset, adLockOptimistic, adCmdTable
  
  Range("A2").Activate  ' row 1 contains column headings
  Do While Not IsEmpty(ActiveCell)
  
  'filter all columns and update all records back instead of looking for those marked with "complete"
  'guessing this will be easier to do
  rs.Filter = "Type='" &amp; xType &amp; "' AND IBES_Ticker='" &amp; xIBES_Ticker &amp; "' AND Editor='" &amp; xEditor &amp; "' AND PRD_Year='" &amp; xPRD_Year &amp; "' AND PRD_Month='" &amp; xPRD_Month &amp; "' AND Event_Date='" &amp; xEvent_Date &amp; "' AND Reporting='" &amp; xReporting &amp; "' AND Notes='" &amp; xNotes &amp; "' AND Comments='" &amp; xComments &amp; "' "
  If rs.EOF Then
  Debug.Print "No existing records found..."
  rs.Filter = ""
  Else
  Debug.Print "Existing records found..."
  End If
  
  rs("Type").Value = xType
  rs("IBES_Ticker").Value = xIBES_Ticker
  rs("Editor").Value = xEditor
  rs("PRD_Year").Value = xPRD_Year
  rs("PRD_Month").Value = xPRD_Month
  rs("Event_Date").Value = xEvent_Date
  rs("Reporting").Value = xReporting
  rs("Notes").Value = xNotes
  rs("Comments").Value = xComments
  
  rs.Update
  Debug.Print "...record update complete."
  
  ActiveCell.Offset(1, 0).Activate  ' next cell down
  Loop
  rs.Close
  Set rs = Nothing
  cn.Close
  Set cn = Nothing
  End Sub
  </code></pre>