ADODB.Connection Insert into sql server table

Hi Experts,

Im trying to insert a record from Excel [Sheet2$] from Range (A2) to Range (E2) into a table on MS SQL server:
But I get the following error:

Error-2147217900(The INSERT INTO statement contains the following unknown filed name:F1).

Here is the ADODB.Connection:

Sub DB_con1()


Dim cn As ADODB.Connection
Dim strSQL As String
Dim lngRecsAff As Long
On Error GoTo test_Error

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=D:\Book1.xls;" & _
"Extended Properties=Excel 8.0"

'Import by using Jet Provider.
strSQL = "Insert INTO [odbc;Driver={SQL Server};" & _
"Server=titan;Database=dev;" & _
"UID=sa;PWD=welcome1@.].abk_import " & _
"Select * FROM [Sheet2$]"
Debug.Print strSQL
cn.Execute strSQL, lngRecsAff ', adExecuteNoRecords
Debug.Print "Records affected: " & lngRecsAff

cn.Close
Set cn = Nothing

On Error GoTo 0
Exit Sub

test_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure test of VBA Document ThisWorkbook"

End Sub

Thanks in advance for any help.

Regards,

AbrahamHi Abraham - Welcome to the forum :D

How does the below SQL change do you?

strSQL = "Insert INTO MyTable (ColA, ColB, ColC, ColD, ColE) " & _
"SELECT * " & _
"FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; Database=D:\Book1.xls;', 'SELECT * FROM [Sheet1$A1:E2]')"

EDIT - I've just reread your code. Your server is Excel. This code assumes your server is SQL Server. You would need to change your connection string to:
cn.Open "odbc;Driver={SQL Server};" & _
"Server=titan;Database=dev;" & _
"UID=sa;PWD=welcome1@."

HTH|||Hi HTH,

Thanks for the quick respond and solution!

My problem was that I was selecting a wrong Worksheet

Here is the code that I used and is functional:
Sub DB_con1()


Dim cn As ADODB.Connection
Dim strSQL As String
Dim lngRecsAff As Long
On Error GoTo test_Error

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=D:\Book1.xls;" & _
"Extended Properties=Excel 8.0"

'Import by using Jet Provider.
strSQL = "Insert INTO [odbc;Driver={SQL Server};" & _
"Server=mydbserver;Database=DEV;" & _
"UID=sa;PWD=Welcome1@.].abk_import " & _
"Select * FROM [Sheet1$]"
Debug.Print strSQL
cn.Execute strSQL, lngRecsAff ', adExecuteNoRecords
Debug.Print "Records affected: " & lngRecsAff

cn.Close
Set cn = Nothing

On Error GoTo 0
Exit Sub

test_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure test of VBA Document ThisWorkbook"

End Sub
'''

Reagrads,

Abrahim