Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    CA
    Posts
    8

    Unanswered: 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,

    Abraham

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Abraham - Welcome to the forum

    How does the below SQL change do you?

    Code:
    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:
    Code:
    cn.Open "odbc;Driver={SQL Server};" & _
    "Server=titan;Database=dev;" & _
    "UID=sa;PWD=welcome1@"
    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    CA
    Posts
    8

    Cool ADODB.connection

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •