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 error '800a0e7a' in sql server 2005

i've developed one small application in asp with some vbscript and some javascript.

i connect to the database by:

Set DB = Server.CreateObject("ADODB.Connection")
DB.Open "Provider=sqloledb;Data Source=(local);Initial Catalog=HelpDesk;User Id=AAAAA;Password=********;"

all were going well (local sql server 2000) but... when i've moved the application to the application server with sql server 2005 i get the following error when i try to acced throw ie explorrer:

i have insttaled the latest mdac (2.8) and register the msdasql.dll

ADODB.Connection error '800a0e7a'

Provider cannot be found. It may not be properly installed.

Help pls.

NS.

Please try to register sqloledb.dll, because you are using sqloledb provider.

Hope, it helps.

ADODB.Connection error '800a0e7a' in sql server 2005

i've developed one small application in asp with some vbscript and some javascript.

i connect to the database by:

Set DB = Server.CreateObject("ADODB.Connection")
DB.Open "Provider=sqloledb;Data Source=(local);Initial Catalog=HelpDesk;User Id=AAAAA;Password=********;"

all were going well (local sql server 2000) but... when i've moved the application to the application server with sql server 2005 i get the following error when i try to acced throw ie explorrer:

i have insttaled the latest mdac (2.8) and register the msdasql.dll

ADODB.Connection error '800a0e7a'

Provider cannot be found. It may not be properly installed.

Help pls.

NS.

Please try to register sqloledb.dll, because you are using sqloledb provider.

Hope, it helps.

ADODB.Connection error 800a0e7a

I am getting the following error, when I try to run my ASP Web pages on an NT4 server has anyone got any Ideas ?

ADODB.Connection error '800a0e7a'

Provider cannot be found. It may not be properly installed.

I have installed every service pack and rebooted even reregistering .dlls

Anyone got any Ideas ?If your problem is like mine, you are getting this error because your MDAC got hosed.
I was un-installing something seemingly harmless and before I knew it I saw a flurry of mdac dlls being deleted.
After that, SQLServer7's Enterprise Manager and other VB6 database apps started failing with this error.
I re-installed the MDAC (26) and it (thankfully) fixed the problem.
Hope this helps.|||I agree - reinstall your mdac. You can try to reregister the individual dll that is causing the problem - but that may only be a partial fix.

adodb.connection > DBMS Name property equivalent in ADO.NET ...

Hi,

One of my team member was porting a Visual Basic function into VB.NET. We were struck up while retrieving the DBMS Name property from the connection object. Actually in VB, the ADODB.connection object's properties will have an item called DBMS Name which will hold the database name like "Oracle" , "Access" , "SQL Server" like that based on the database that I connect.

I wonder if there is any equivalent property in ADO.NET. The Server Version property of ODBCConnection object returns "8.0...." in case of SQL Server and returns "09.01.0000 Oracle9i Enterprise Edition Release 9.2.0.1.0" in case of oracle. This is not as precise as it's ADODB counterpart.

I'm hardly in need of a solution or workaround for this scenario. Can anybody help.

Thanks in advance.

Hi Prathap,

The .Net Framework Data Access and Storage forum is the best forum with which to seek your answer (I see that you've already posted this question there).

Il-Sung.