How do I update entire table where anything changes from Ms Excel to mySQL using vba?
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
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
How do I Edit That In My Code?
My SQL Table Has
COL 1, COl 2, COL 3, COL 4, COL 5, COL 6, COL 7
As the column Names,same applies to the table in excel, it has 7 columns too
Dim oConn As ADODB.Connection
Dim rs As ADODB.Recordset
'remove dangerous characters
Function esc(txt As String)
esc = Trim(Replace(txt, "'", "\'"))
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 "DRIVER={MySQL ODBC 8.0 ANSI Driver};" & _
"SERVER=localhost;" & _
"DATABASE=engine;" & _
"USER=root;" & _
"PASSWORD=;" & _
"Option=3"
Dim del As String
del = "DELETE FROM `feed`"
rs.Open del, oConn ',adOpenDynamic, adLockOptimistic
'number of rows with records. testingupload is the sheetname.
Dim height As Integer
height = Worksheets("Feed").UsedRange.Rows.Count
'insert data into SQL table. testingupload is the sheetname.
With Worksheets("Feed")
Dim rowtable As Integer
Dim strSQL As String
For rowtable = 2 To height
strSQL = "INSERT INTO `feed` (`COL 1`, `COL 2`, `COL 3`, `COL 4`, `COL 5`, `COL 6`, `COL 7`) " & _
"VALUES ('" & esc(Trim(.Cells(rowtable, 1).Value)) & "', '" & _
esc(Trim(.Cells(rowtable, 2).Value)) & "', '" & _
esc(Trim(.Cells(rowtable, 3).Value)) & "', '" & _
esc(Trim(.Cells(rowtable, 4).Value)) & "', '" & _
esc(Trim(.Cells(rowtable, 5).Value)) & "', '" & _
esc(Trim(.Cells(rowtable, 6).Value)) & "', '" & _
esc(Trim(.Cells(rowtable, 7).Value)) & "')"
rs.Open strSQL, oConn ',adOpenDynamic, adLockOptimistic
Next rowtable
End With
End Sub
Comments
Post a Comment