Solved

Automation Error when importing Excel spreadsheet to SQL Server

Posted on 2011-03-07
9
1,035 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

947 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now