How to update MSSQL from Excel using excel VBA

Question

Current Situation

I have a database in MSSQL and currently it is able to link to excel the following way.

Excel table which is connected to MSSQL as follows

MSSQL table as below

Currently if I update my MSSQL table, excel table will update accordingly

What I need

I want the vice versa operation. Means whenever I update the excel table, MSSQL also needs to update. Is this possible?

You can truncate & load the MSSQL table with the excel file after any update. You can use SSIS to achieve this. — Be1ng_Kr1Sh, Jun 11 '18 at 08:34
Possible duplicate of [How to update Sql table from excel directly?](https://stackoverflow.com/questions/42732802/how-to-update-sql-table-from-excel-directly) — Will Jones, Jun 11 '18 at 15:59

score 0 · Accepted Answer · answered Jun 12 '18 at 09:29

I am able to do it correctly by using following code

Sub updateSqlFromExcel()
Dim cnn As ADODB.connection
Dim uSQL As String
Set cnn = New connection
    cnnstr = "Provider=SQLOLEDB; " & _
            "Data Source=myServer; " & _
            "Initial Catalog=myDatabaseName;" & _
            "User ID=username;" & _
            "Password=password;" & _
            "Trusted_Connection=No"
            Set rngName = ActiveCell
    cnn.Open cnnstr
    
    excelId = ActiveCell.Value 'This is just an example
    excelSeq = ActiveCell.Offset(0, 1).Value 'This is just an example
    excelName = ActiveCell.Offset(0, 2).Value 'This is just an example
    
    uSQL = "UPDATE myTableName SET Name = '" & excelName & "', Seq = '" & excelSeq & "' WHERE ID= '" & excelId & "' "
    cnn.Execute uSQL
    ActiveWorkbook.RefreshAll
    MsgBox "Updated successfully. But please wait until background refresh finish before close excel", vbInformation, "Success"
    cnn.Close
    Set cnn = Nothing
End Sub

Please change myServer, myDatabaseName, username, passwordand myTableNameaccording to your settings in order for this macro to work. Also please add reference library Microsoft ActiveX Data Objects 2.8 Library. In order to add, press Alt+F11 and then goto Tools --> References. Scroll to Microsoft ActiveX Data Objects 2.8 Library and tick the checkbox. Press ok and you are ready to go.

How to update MSSQL from Excel using excel VBA

1 Answers1

Linked