Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1137
  • Last Modified:

Automation Error when importing Excel spreadsheet to SQL Server

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
EspanolaIT
Asked:
EspanolaIT
1 Solution
 
DhaestCommented:
Do you have columnnames or values that are longer than 255 characters ?
How many columns do you have and what are their type ?
0
 
EspanolaITAuthor Commented:
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
 
SiddharthRoutCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
SiddharthRoutCommented:
I could be wrong but you were missing the adOpenDynamic, adLockPessimistic ?

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

Sid
0
 
EspanolaITAuthor Commented:
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
 
EspanolaITAuthor Commented:
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
 
PabilioCommented:
Espanola,

There is any Key field in the table that you are triying to update from the excel spreadsheet ?
0
 
EspanolaITAuthor Commented:
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
 
EspanolaITAuthor Commented:
I figured out the issue myself finally!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now