Solved

Automation Error when importing Excel spreadsheet to SQL Server

Posted on 2011-03-07
9
1,046 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

803 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