title: How do I update entire table where anything changes from Ms Excel to mySQL using vba?
body: <p>Picture this, I have a table in excel that is connected to power query that refreshes every minute, Im trying to use vba to export the live table to mysql , whenever the table in excel refreshes , the data also refreshes in the mySQL table, I need it to update the table where anything changes and also decrease rows if they no longer exist in the excel table, whenever the table refresh and update the new values to the SQL table</p>
  <p>The code I have here is of a Insert and delete system, which takes time to load about 3 minutes depending on the data in the table, I want to use the UPDATE statement in my code instead of deleting and inserting the whole
  is it possible, can someone please reply as an answer with the update statement</p>
  <p>How do I Edit That In My Code?</p>
  <p>My SQL Table Has</p>
  <p>COL 1,
  COl 2,
  COL 3,
  COL 4,
  COL 5,
  COL 6,
  COL 7</p>
  <p>As the column Names,same applies to the table in excel, it has 7 columns too</p>
  <pre><code>Dim oConn As ADODB.Connection
  Dim rs As ADODB.Recordset
  
  'remove dangerous characters
  Function esc(txt As String)
  esc = Trim(Replace(txt, &quot;'&quot;, &quot;\'&quot;))
  End Function
  
  Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  
  Set rs = New ADODB.Recordset
  Set oConn = New ADODB.Connection
  oConn.Open &quot;DRIVER={MySQL ODBC 8.0 ANSI Driver};&quot; &amp; _
  &quot;SERVER=localhost;&quot; &amp; _
  &quot;DATABASE=engine;&quot; &amp; _
  &quot;USER=root;&quot; &amp; _
  &quot;PASSWORD=;&quot; &amp; _
  &quot;Option=3&quot;
  Dim del As String
  del = &quot;DELETE FROM `feed`&quot;
  rs.Open del, oConn ',adOpenDynamic, adLockOptimistic
  
  'number of rows with records. testingupload is the sheetname.
  Dim height As Integer
  height = Worksheets(&quot;Feed&quot;).UsedRange.Rows.Count
  
  'insert data into SQL table. testingupload is the sheetname.
  With Worksheets(&quot;Feed&quot;)
  Dim rowtable As Integer
  Dim strSQL As String
  For rowtable = 2 To height
  
  
  
  strSQL = &quot;INSERT INTO `feed` (`COL 1`, `COL 2`, `COL 3`, `COL 4`, `COL 5`, `COL 6`, `COL 7`) &quot; &amp; _
  &quot;VALUES ('&quot; &amp; esc(Trim(.Cells(rowtable, 1).Value)) &amp; &quot;', '&quot; &amp; _
  esc(Trim(.Cells(rowtable, 2).Value)) &amp; &quot;', '&quot; &amp; _
  esc(Trim(.Cells(rowtable, 3).Value)) &amp; &quot;', '&quot; &amp; _
  esc(Trim(.Cells(rowtable, 4).Value)) &amp; &quot;', '&quot; &amp; _
  esc(Trim(.Cells(rowtable, 5).Value)) &amp; &quot;', '&quot; &amp; _
  esc(Trim(.Cells(rowtable, 6).Value)) &amp; &quot;', '&quot; &amp; _
  esc(Trim(.Cells(rowtable, 7).Value)) &amp; &quot;')&quot;
  
  rs.Open strSQL, oConn ',adOpenDynamic, adLockOptimistic
  
  
  Next rowtable
  End With
  
  End Sub
  
  
  
  
  </code></pre>