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

ADODB connection from inside EXCEL

I posted before, but it did not appear. Anyhow, I found
the answer -- sort of.
The problem is that I have two "identical" machines
running identical EXCEL spread sheet. One can do an
ADODB connection and the other can not. I could not
figure out what was different until I installed SQL Server
Client Services on the machine whose EXCEL did not work.
Now it works. Conclusion: Something has been added to
the register which is needed to support ADODB inside
EXCEL.
The question now is: How do I implement the change
without installing SQL Server? What is the minimum
installation that will enable EXCEL to do ADODB
connections?
Ken Jones
Is the missing package the ActiveX set? What isThe machines probably have a different MDAC level. I would check them using
the Component Checker which you can download at:
http://www.microsoft.com/downloads/...&displaylang=en
If you find they're different install the latest MDAC version on the older
system.
Mike O.
"Ken Jones" <kjones@.ziplink.net> wrote in message
news:0d2601c3d6d2$8262b8d0$a501280a@.phx.gbl...

quote:

---

> I posted before, but it did not appear. Anyhow, I found
> the answer -- sort of.
> The problem is that I have two "identical" machines
> running identical EXCEL spread sheet. One can do an
> ADODB connection and the other can not. I could not
> figure out what was different until I installed SQL Server
> Client Services on the machine whose EXCEL did not work.
> Now it works. Conclusion: Something has been added to
> the register which is needed to support ADODB inside
> EXCEL.
> The question now is: How do I implement the change
> without installing SQL Server? What is the minimum
> installation that will enable EXCEL to do ADODB
> connections?
> Ken Jones
> Is the missing package the ActiveX set? What is
>

---