Q:

How to update MSSQL from Excel using excel VBA

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?

A:

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.

