An easier way than this to SQL update ?

JumboCactuar

Well-known Member
Joined
Nov 16, 2016
Messages
727
Office Version
  1. 365
Platform
  1. Windows
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.


Code:
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
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,980
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,130,029
Messages
5,639,623
Members
417,101
Latest member
amoverton2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top