Update Existing Database Values from Spreadsheet

Update existing database values from spreadsheet

You shouldn't need to loop through each row in the spreadsheet. You can use the OPENROWSET command, like in the answer you linked to, to load the spreadsheet data into a sort of temporary table. You can then run a regular UPDATE statement against that table.

It would look something like this

UPDATE YourTable
SET YourTable.A = ExcelTable.NewDataA,
    YourTable.B = ExcelTable.NewDataB,
    YourTable.C = ExcelTable.NewDataC,
    YourTable.D = ExcelTable.NewDataD
FROM YourTable
INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0',
         'Excel 8.0;Database=C:\foldername\spreadsheetname.xls;',
         'SELECT column1name, column2name, column3name, column4name
          FROM [worksheetname$]') AS ExcelTable
ON YourTable.ID = ExcelTable.ID
WHERE (YourTable.A = ExcelTable.OldDataA
  AND YourTable.B = ExcelTable.OldDataB
  AND YourTable.C = ExcelTable.OldDataC
  AND YourTable.D = ExcelTable.OldDataD)

How to update Sql table from excel directly?

There are many ways to do this. I'd recommend something like this, to push data from Excel to SQL Server.

Sub ButtonClick()
'TRUSTED CONNECTION
    On Error GoTo errH
   
    Dim con As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strPath As String
    Dim intImportRow As Integer
    Dim strFirstName, strLastName As String
   
    Dim server, username, password, table, database As String
   
   
    With Sheets("Sheet1")
           
            server = .TextBox1.Text
            table = .TextBox4.Text
            database = .TextBox5.Text
           
           
            If con.State <> 1 Then
       
                con.Open "Provider=SQLOLEDB;Data Source=" & server & ";Initial Catalog=" & database & ";Integrated Security=SSPI;"
                'con.Open
       
            End If
            'this is the TRUSTED connection string
           
            Set rs.ActiveConnection = con
           
            'delete all records first if checkbox checked
            If .CheckBox1 Then
                con.Execute "delete from tbl_demo"
            End If
       
            'set first row with records to import
            'you could also just loop thru a range if you want.
            intImportRow = 10
           
            Do Until .Cells(intImportRow, 1) = ""
                strFirstName = .Cells(intImportRow, 1)
                strLastName = .Cells(intImportRow, 2)
               
                'insert row into database
                con.Execute "insert into tbl_demo (firstname, lastname) values ('" & strFirstName & "', '" & strLastName & "')"
               
                intImportRow = intImportRow + 1
            Loop
           
            MsgBox "Done importing", vbInformation
           
            con.Close
            Set con = Nothing
   
    End With
   
Exit Sub

errH:
    MsgBox Err.Description
End Sub

You can also try this, which uses a Where Clause.

Sub InsertInto()

'Declare some variables
Dim cnn As adodb.Connection
Dim cmd As adodb.Command
Dim strSQL As String

'Create a new Connection object
Set cnn = New adodb.Connection

'Set the connection string
cnn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=Northwind;Data Source=Excel-PC\SQLEXPRESS"
'cnn.ConnectionString = "DRIVER=SQL Server;SERVER=Excel-PC\SQLEXPRESS;DATABASE=Northwind;Trusted_Connection=Yes"

'Create a new Command object
Set cmd = New adodb.Command

'Open the Connection to the database
cnn.Open

'Associate the command with the connection
cmd.ActiveConnection = cnn

'Tell the Command we are giving it a bit of SQL to run, not a stored procedure
cmd.CommandType = adCmdText

'Create the SQL
strSQL = "UPDATE TBL SET JOIN_DT = '2013-01-22' WHERE EMPID = 2"

'Pass the SQL to the Command object
cmd.CommandText = strSQL

'Execute the bit of SQL to update the database
cmd.Execute

'Close the connection again
cnn.Close

'Remove the objects
Set cmd = Nothing
Set cnn = Nothing

End Sub

Related Topics