An easier way than this to SQL update ?

JumboCactuar

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,177
Office Version
365
Platform
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:

Forum statistics

Threads
1,078,462
Messages
5,340,456
Members
399,376
Latest member
Tresfjording

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top