An easier way than this to SQL update ?

JumboCactuar

Well-known Member
Joined
Nov 16, 2016
Messages
785
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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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:
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,344
Members
448,570
Latest member
rik81h

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
Back
Top