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:
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
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??
Microsoft OfficeMicrosoft ExcelVisual Basic Classic
Last Comment
EspanolaIT
8/22/2022 - Mon
Dirk Haest
Do you have columnnames or values that are longer than 255 characters ?
How many columns do you have and what are their type ?
EspanolaIT
ASKER
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)
which also worked in Query Analyzer but not in the VBA code.
EspanolaIT
ASKER
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??
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
How many columns do you have and what are their type ?