ADODB.Connection and insert into SQL Server

[akalehzan]

Hi all,

I have an excel spread sheet "Sheet1" that i need data from into SQL SERVER's table name "ABC" by using VBA cod. Following is an example of the Excel Sheet1 data.

First_name
Last_name
PS_AGE
Date

1
JIm
Smith
25.5
01/04/2004

2
Sam
Mayer
31.3
02/13/2001

3
Judy
Hall
26.3
01/13/2000
'''''''''''''''''''''''
I have the following ADODB.Connection that I like to use exporting the above example Excel range into SQL Server existing ABC table:


Sub connect_DB()
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset
Dim sConnString As String
Dim iCtr As Integer


sConnString = "Provider=SQLOLEDB.1;User ID=sa;password=XXXXXX;Initial Catalog=DBNAME;Data Source = XXX.XX.XX.XXX;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096"

conn.Open sConnString
Set cmd.ActiveConnection = conn

'REPLACE MYTABLE WITH YOUR OWN TABLE
cmd.CommandText = "select * from abc"
cmd.CommandType = adCmdText
Set rs = cmd.Execute

Do While Not rs.EOF
For iCtr = 0 To rs.Fields.Count - 1
'OutPuts Name and Value of each field
Debug.Print rs.Fields(iCtr).Name & ": " & _
rs.Fields(iCtr).Value
Next
rs.MoveNext
Loop
Set rs = Nothing
Set cmd = Nothing
conn.Close
Set conn = Nothing
End Sub
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

I found the following code on the web that does the sql part, but I need help putting it to gather with the above code:

select First_name, Last_name, Date, PS_age,
from OpenRowSet(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=E:\excel\Blocks.xls;HDR=YES'
,Sheet1$
)


'''''''''''''''''''''''''''''''
Thanks for any help,

Abraham

[littlepd]

If it was me, I'd use two separate ADO connections: one to the SQL Server table you're writing to, and one to the Excel worksheet you're reading from. Then you would Select * from the spreadsheet, .MoveNext through the resulting recordset, and Insert into your SQL table.