An easier way than this to SQL update ?

[JumboCactuar]

Hi,
i have the following which works

but is there an easier way if the worksheet data matches the table? ie same no of columns
as with 20+ columns to update it gets tedious typing it all out.

Sub updateSQL()

Application.ScreenUpdating = False


    var1 = Sheet3.Range("A2")
    
   Set cnn = CreateObject("ADODB.Connection")
    cnn.Open "xxx"


    Rw = 2
    
    For i = 2 To Rw
    v2 = Sheet3.Cells(i, 2).Value
    v3 = Sheet3.Cells(i, 3).Value
    v4 = Sheet3.Cells(i, 4).Value
    v5 = Sheet3.Cells(i, 5).Value
    v6 = Sheet3.Cells(i, 6).Value
    v7 = Sheet3.Cells(i, 7).Value
    
    MYSQL = "UPDATE MYTABLE " & _
    "SET Country = '" & v2 & "' " & _
    ",Yr_1950 = '" & v3 & "' " & _
    ",Yr_2000 = '" & v4 & "' " & _
    ",Yr_2015 = '" & v5 & "' " & _
    ",Yr_2025 = '" & v6 & "' " & _
    ",Yr_2050 = '" & v7 & "' " & _
    "WHERE PopID=" & var1 & ";"
            
    Debug.Print MYSQL
    cnn.Execute MYSQL
    
    Next i
    
    cnn.Close
    Set cnn = Nothing




Application.ScreenUpdating = True




End Sub

 

[Joe4]

It looks like you currently have 6 columns. It would be easy enough to loop through it without having to create the temporary v2 to v7 variables. However, building your code would be a bit trickier, as there does not appear to be any discernable pattern in your year jumps: 1950, 2000, 2015, 2025, 2050,...

I supposed if you have a known list of all of them, you could store them in an array and loop through those too.