Solved

Automation Error when importing Excel spreadsheet to SQL Server

Posted on 2011-03-07
9
1,073 Views
Last Modified: 2012-05-11
I have spent hours trying to import the data from a worksheet of an Excel (2003) file into a new table in SQL Server 2000.  I can run the SQL statement with no problems directly in a Query Analyzer window:

        strSQL = "select * " _
            & "into DTE_std_stats8 FROM " _
            & "OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel " _
            & "8.0;Database=c:\StdStats.xls','SELECT * FROM [Datastuff$]')"

Open in new window


But when I attempt to run it in Excel 2003 VBA I get an automation error:
    Dim cn As ADODB.Connection
    Dim strSQL As String
    
        Set cn = New ADODB.Connection
        
        'EITHER WAY OF CONNECTING GAVE THE SAME AUTOMATION ERROR
        'cn.Open "Provider=SQLOLEDB;Data Source=esp-optisql;Initial Catalogue=MyDB;User Id=MYUSER;Password=MYPWD"
        cn.Open "optisql_MYDB", "MYUSER", "MYPWD"
    
        strSQL = "select * " _
            & "into DTE_std_stats FROM " _
            & "OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel " _
            & "8.0;Database=c:\StdStats.xls','SELECT * FROM [Datastuff$]')"
        
        cn.Execute (strSQL)
    
        cn.Close
        Set cn = Nothing

Open in new window

I get "Run-time error '-2147217900 (80040e14)': Automation error" when I get to the line:

cn.Execute (strSQL)

Open in new window


Also-I ensure the table to be created DTE_std_stats does not exist before I run the code.  And I have the following references added:
Visual Basic for Applications
Microsoft Excel 11.0 Object Library
Microsoft Office 11.0 Object Library
Microsoft Forms 2.0 Object Library
Microsoft Scripting Runtime
OLE Automation
Microsoft ActiveX Data Objects 2.7 Library

Please - what am I missing??
0
Comment
Question by:EspanolaIT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 53

Expert Comment

by:Dhaest
ID: 35056659
Do you have columnnames or values that are longer than 255 characters ?
How many columns do you have and what are their type ?
0
 

Author Comment

by:EspanolaIT
ID: 35056738
I pruned down the data in my spreadsheet just so that I can get it to work and go from there.  So essentially all that my spreadsheet now consists of is the following 3 text columns and 5 rows (plus header)

Plant      ValuationType        BWGrade
CA12      ZIES02                E3002
CA12      ZIES02                E3019
CA12      ZIES02                E3019
CA12      ZIES02                E3019
CA12      ZIES02                E3150
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35056778
Try this

strSQL = "Provider=SQLOLEDB;Data Source=esp-optisql;Initial Catalogue=MyDB;User Id=MYUSER;Password=MYPWD"

cn.Open "Product", strSQL, adOpenDynamic, adLockPessimistic

Sid
0
Industry Leaders: 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!

 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35056784
I could be wrong but you were missing the adOpenDynamic, adLockPessimistic ?

Also I have used "Product", Change that as applicable.

Sid
0
 

Author Comment

by:EspanolaIT
ID: 35056787
I also tried the following command:

        strSQL = "insert " _
            & "into DTE_test select * FROM " _
            & "OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=c:\StdStats.xls;','SELECT * FROM [Datatry$]')"

Open in new window


which also worked in Query Analyzer but not in the VBA code.
0
 

Author Comment

by:EspanolaIT
ID: 35056895
The line cn.Open "optisql_MYDB", "MYUSER", "MYPWD"  is only opening the connection to SQL server - its not until further down that I am actually executing a SQL statement against that connection with the line cn.Execute (strSQL)

I don't believe there is any issue with the setup of my database connection because if I replace cn.Execute (strSQL)  or even cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
 with
cn.Execute "drop table DTE_test", lngRecsAff, adExecuteNoRecords
I have no problems.  So I am able to connect to the database and issue certain commands just not OPENROWSET commands??
0
 
LVL 5

Expert Comment

by:Pabilio
ID: 35057600
Espanola,

There is any Key field in the table that you are triying to update from the excel spreadsheet ?
0
 

Accepted Solution

by:
EspanolaIT earned 0 total points
ID: 35057845
I figured out finally that it is the DisallowAdhocAccess registraty setting for the JET provider that is giving me the problem.  "OPENROWSET can be used to access remote data from OLE DB data sources only when the DisallowAdhocAccess registry option is explicitly set to 0 for the specified provider, and the Ad Hoc Distributed Queries advanced configuration option is enabled."  For testing purposes if I use the "sa" admin user account for my database connection, the command runs fine from VBA code with no error thrown.  So now I just have to decide if I want to change the registry setting or learn how to use Linked Servers.  Thanks for everyone's help!
0
 

Author Closing Comment

by:EspanolaIT
ID: 35115190
I figured out the issue myself finally!
0

Featured Post

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!

Question has a verified solution.

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

My experience with Windows 10 over a one year period and suggestions for smooth operation
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
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…

759 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