?
Solved

Automation Error when importing Excel spreadsheet to SQL Server

Posted on 2011-03-07
9
Medium Priority
?
1,184 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
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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.

Join & Write a Comment

As a person who answers a lot of questions, I often see code that could be simplified, made easier to read, and perhaps most importantly made easier to maintain if the code was modified to use the Select Case statement. This article explains how to…
This is a comprehensive review of a bundled Toolkit designed for use by IT Professionals and End Users to help Microsoft Outlook fans manipulate Outlook files and repair some common problems. Enjoy...
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

589 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