How to update SQL Server table from Excel data

Question

I've a vendor table in SQL Server with vendor code, vendor ID and vendor name. Another table called disc_mast with columns vendor ID and disc_per. Vendors will revise disc% frequently and we will get the details in Excel with vendor code and disc%. How can I update the discount percent in the disc_mast from the data in excel without importing the data in Excel to SQL Server tables.

Answer 1

You can do something like this.

Sub Update()

'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 = "Your_Server_Name;Database=Northwind;Trusted_Connection=True;"

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

'Open the connection
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-13 WHERE EMPID = 2"

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

'Open the Connection to the database
cnn.Open

'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

This is VBA code that runs in Excel. Also, create a reference to 'Microsoft ActiveX Data Objects x.x Library'.

Tools -. References: