Inserting values into SQL SERVER table via VBA.

sherik

Board Regular
Joined
Aug 3, 2012
Messages
68
Suppose I have a table in Excel with two columns (Name, Rate) (say, this table's name tExcel). Table starts at cell (2,1), and Date is static (in cell (1,1))

I want to insert those values into SQL Server 2008 tRate table with following logic

Code:
insert tRate(ID, Rate, Date)
select s.ObjectID
        ,e.Rate
        ,Date--date comes from cell(1,1). DateType is smalldatetime
  from tExcel    e
        ,tSecurity s
 where e.Name = s.Number

I've created a connection

Code:
Sub disp_cust()
Dim adoCN As ADODB.Connection
Dim sConnString As String
Dim sSQL As String
Dim lRow As Long, lCol As Long
Set cn = New ADODB.Connection
sConnString = "Provider=sqloledb;Server=xxx;Database=xxx;User Id=xxx;Password=xxx"
Set adoCN = CreateObject("ADODB.Connection")
adoCN.Open sConnString
adoCN.Close
Set adoCN = Nothing
End Sub

Thanks for help.
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Watch MrExcel Video

Forum statistics

Threads
1,108,979
Messages
5,526,026
Members
409,679
Latest member
smars

This Week's Hot Topics

Top