Importing Excel file into SQL Server Using JET Provider

Hello Expert,
I am trying to import an Excel file into a SQL server database.   I had previous version of my VBA code working, but found out that are DBA which is IBM, has a restriction on Creating tables, even tough I am only appending the data.  So, I now have to use a different method.  The following is the VBA code that I am trying to get to work.  When I run the code below I get an error message ODBC -- Call failed.  any suggestions?  Thanks Danny

Private Sub Command4_Click()

      Dim cn As ADODB.Connection
    Dim strSQL As String
    Dim lngRecsAff As Long
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=E:\MainDatabase\Inputfile_Template\MonthlyInputfile_Template.xls;" & _
        "Extended Properties=Excel 8.0"
     

    'Import by using Jet Provider.
    strSQL = "SELECT * INTO [odbc;Driver={SQL Server};" & _
        "Server=s9hazas3;Database=Trade_fin;" & _
        "Trusted_Connection=Yes;Integrated_Security=SSPI].MONTHLY_INPUTDUMP " & _
        "FROM [All$]"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff

    cn.Close
    Set cn = Nothing

End Sub
dan5460Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rockiroadsCommented:
Have a look at http://www.connectionstrings.com/  to get a better idea


Standard Security:
"Driver={SQL Server};Server=Aron1;Database=pubs;Uid=sa;Pwd=asdasd;"

Trusted connection:
"Driver={SQL Server};Server=Aron1;Database=pubs;Trusted_Connection=yes;"

Prompt for username and password:
oConn.Properties("Prompt") = adPromptAlways
oConn.Open "Driver={SQL Server};Server=Aron1;DataBase=pubs;"



0
rockiroadsCommented:
The only difference I see is the ODBC word.

Try the simple one as suggested.

Next test out your ODBC connection. Does that connection work properly?

You could try in Access   File/Get External Data and select source ODBC
see what happens
0
dan5460Author Commented:
Hello,
I got the connection to work.  But when I run it, nothing happens.  The table does not get populated with any data.  Here is now my current code
Private Sub Command4_Click()
  Dim cn As ADODB.Connection
    Dim strSQL As String
    Dim lngRecsAff As Long
    Dim conn As ADODB.Connection
   
    Set cn = New ADODB.Connection
   
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=E:\MainDatabase\Inputfile_Template\MonthlyInputfile_Template.xls;" & _
        "Extended Properties=Excel 8.0"
     
      Set conn = New ADODB.Connection
      conn.ConnectionString = "Driver={SQL Server};Server=s9hazas3;Database=trade_fin;Trusted_Connection=Yes;Integrated_Security=SSPI"
     
     
      conn.ConnectionTimeout = 30
    conn.Open
 
     

    'Import by using Jet Provider.
    strSQL = "INSERT INTO conn.MONTHLY_INPUTDUMP " & _
        "SELECT * FROM [All$]"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff


    cn.Close
    Set cn = Nothing

End Sub
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

dan5460Author Commented:
Here is an update
I am now getting the following error. "The insert to statement contains the following unknown field name: cust_abbrev.  Make sure you typed the name correctly, and try the operation again."

I checked the monthly_inputdump table where I want to insert the records into and there is a field called cust_abbrev.  I am not sure what its trying to tell me.  Here is the updated code.

Thanks

rivate Sub Command6_Click()
 Dim cn As ADODB.Connection
    Dim strSQL As String
    Dim lngRecsAff As Long
    Dim conn As ADODB.Connection
   
    Set cn = New ADODB.Connection
   
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=E:\MainDatabase\Inputfile_Template\testImport.xls;" & _
        "Extended Properties=Excel 8.0"
     
      Set conn = New ADODB.Connection
      'For Local SQL Server
      conn.ConnectionString = "Driver={SQL Server};Server=(local);Database=tfdb;Trusted_Connection=Yes;Integrated_Security=SSPI"
      'conn.ConnectionString = "Driver={SQL Server};Server=s9hazas3;Database=trade_fin;Trusted_Connection=Yes;Integrated_Security=SSPI"
     
     
    conn.ConnectionTimeout = 30
    conn.Open
 
     

    'Import by using Jet Provider.
    strSQL = "INSERT INTO conn.MONTHLY_INPUTDUMP " & _
        "SELECT * FROM [ALL$]"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
   ' Debug.Print "Records affected: " & lngRecsAff


    cn.Close
    Set cn = Nothing
End Sub

0
rockiroadsCommented:
ok, have a look at the fields in the table

MONTHLY__INPUTDUMP

do they match the exact number and order of the table [ALL$] ?

If they dont, then do this

INSERT INTO conn.MONTHLY_INPUTDUMP (field1, field2, field3) SELECT field1, field2, field3 FROM [ALL$]
0
dan5460Author Commented:
THanks, I am on to another problem. I fixed my original problem, now I am getting a message
"The field is too small to accept the amount of data you attempted to add.  Try inserting or pasting less data."

What is this?  The file that I have is 3000 records, I get this message even if I am sending 8 records.  I check each field length and they = to or less then the data field structure.  

Any suggestions.

Thanks

Danny
0
rockiroadsCommented:
its not number of records but looks like field sizes

have a look at the data you are inserting
and for each field, look at its length on table and compare length to data

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dan5460Author Commented:
Thanks for the helop.  It worked out perfectly.  Noticed that some of the fields in the excel spreadsheet had blank characters in the field that exceed the lenght of the field.  So I just had to trim the fields.  

Thanks again

Danny
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.