How do I update entire table where anything changes from Ms Excel to mySQL using vba?-CodePudding

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

CodePudding user response：

So, as said in the comments, my understanding is that you would like to copy all the data from the excel table into the MySql Table. As you did not give any hint how to identify a single row by a key I assume that one just can delete the data from the MySql table and then insert all the data from the excel sheet into the MySql table.

Let

Dim cn As ADODB.Connection
' your code to establish the connection

be the connection to the MySql database then you can delete the data quite simple

Dim sSQL As String
sSQL = "DELETE * FROM tbl1"
cn.Execute sSQL

tbl1 is the name of the table in question from the MySql database

After you have deleted the data from the MySql table you just insert all data from the excel table into the MySql table

    sSQL = " INSERT INTO tbl1 SELECT * FROM [Excel 12.0;HDR=YES;DATABASE=" _
        & ThisWorkbook.FullName & "].[Sheet1$A1:B5]"
    cn.Execute sSQL

The tricky part here is left for you as you know in which excel sheet the data is stored and what range it covers. That means you probably have to adjust the sheetname Sheet1 and the range A1:B5. Please do not remove the $ beetween the sheet name and the range. It is needed for the code to work.

As you have seven columns I guess the range will be similar to " .... [Sheet1$A1:G" & Height & "]" where height is the number of the rows (see your code above).

Your code might look like that at the end

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim Cn As ADODB.Connection
    Set cn = New ADODB.Connection
    cn.Open "DRIVER={MySQL ODBC 8.0 ANSI Driver};" & _
        "SERVER=localhost;" & _
        "DATABASE=engine;" & _
        "USER=root;" & _
        "PASSWORD=;" & _
        "Option=3"
    
    'number of rows with records. testingupload is the sheetname.
    Dim height As Integer
    height = Worksheets("Feed").UsedRange.Rows.Count

    Dim sSQL As String
    sSQL = "DELETE * FROM tbl1"
    cn.Execute sSQL

    sSQL = " INSERT INTO tbl1 SELECT * FROM [Excel 12.0;HDR=YES;DATABASE=" _ 
       & ThisWorkbook.FullName & "].[Sheet1$A1:G" & height & "]"
    cn.Execute sSQL
    
    cn.Close
    
End Sub

CodePudding user response：

Just edited my code to

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim Cn As ADODB.Connection
    Set Cn = New ADODB.Connection
    Cn.Open "DRIVER={MySQL ODBC 8.0 ANSI Driver};" & _
        "SERVER=localhost;" & _
        "DATABASE=engine;" & _
        "USER=root;" & _
        "PASSWORD=;" & _
        "Option=3"
    
    'number of rows with records. testingupload is the sheetname.
    Dim height As Integer
    height = Worksheets("Feed").UsedRange.Rows.Count

    Dim sSQL As String
    sSQL = "DELETE FROM feed"
    Cn.Execute sSQL

    sSQL = " INSERT INTO feed SELECT * FROM [Excel 12.0;HDR=YES;DATABASE=" _
       & ThisWorkbook.FullName & "].[Feed$A1:G" & height & "]"
    Cn.Execute sSQL
    
    Cn.Close
    
End Sub

Its now giving this error