Sub UpdateTable() Dim cnn As Object Dim wbkOpen As Workbook Dim objfl As Variant Dim rngName As Range Workbooks.Open "C:\your_path_here\Excel_to_SQL_Server.xls" Set wbkOpen = ActiveWorkbook Sheets("Sheet1").Select Set rngName = Range(Range("A1"), Range("A1").End(xlToLeft).End(xlDown)) rngName.Name = "TempRange" strFileName = wbkOpen.FullName Set cnn = CreateObject("ADODB.Connection") cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFileName & ";Extended Properties=""Excel 12.0 Xml;HDR=Yes"";" nSQL = "INSERT INTO [odbc;Driver={SQL Server};Server=Server_Name;Database=[Your_Database].[dbo].[TBL]]" nJOIN = " SELECT * from [TempRange]" cnn.Execute nSQL & nJOIN MsgBox "Uploaded Successfully" wbkOpen.Close Set wbkOpen = Nothing
End Sub
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 = "Server_Name;Database=Your_Database;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