[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Importing Excel file into SQL Server Using JET Provider

Posted on 2006-04-03
8
Medium Priority
?
334 Views
Last Modified: 2007-12-19
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
0
Comment
Question by:dan5460
  • 4
  • 4
8 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 16362293
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 16362321
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
 

Author Comment

by:dan5460
ID: 16363096
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:dan5460
ID: 16364394
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 16364826
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
 

Author Comment

by:dan5460
ID: 16370622
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
 
LVL 65

Accepted Solution

by:
rockiroads earned 1200 total points
ID: 16371982
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
 

Author Comment

by:dan5460
ID: 16391685
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question