I get an automation error when running VBA code in Excel 2007. I am trying to connect to a remote SQL Server database and load data from Excel into SQL Server.
The error I get is
"Runtime Error" -2147217843 (80040e4d) ': Automation Error. "
I checked the MSDN site and suggested that this could be due to a sqloledb provider related error, and one way to mitigate this is to use ODBC. Well, I changed the connection string to reflect the ODBC provider and related parameters, and I still get the same error.
Here is the code with ODBC as a provider:
Dim cnt As ADODB.Connection Dim rst As ADODB.Recordset Dim stSQL As String Dim wbBook As Workbook Dim wsSheet As Worksheet Dim rnStart As Range Public Sub loadData() 'This was set up using Microsoft ActiveX Data Components version 6.0. 'Create ADODB connection object, open connection and construct the connection string object. Set cnt = New ADODB.Connection cnt.ConnectionString = _ "Driver={SQL Server}; Server=onlineSQLServer2010.foo.com; Database=fooDB Uid=logonalready;Pwd='helpmeOB1';" cnt.Open On Error GoTo ErrorHandler 'Open Excel and run query to export data to SQL Server. strSQL = "SELECT * INTO SalesOrders FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', & _ "'Data Source=C:\Database.xlsx; Extended Properties=Excel 12.0')...[SalesOrders$]" cnt.Execute (strSQL) 'Error handling. ErrorExit: 'Reclaim memory from the connection objects Set rst = Nothing Set cnt = Nothing Exit Sub ErrorHandler: MsgBox Err.Description, vbCritical Resume ErrorExit 'clean up and reclaim memory resources. cnt.Close If CBool(cnt.State And adStateOpen) Then Set rst = Nothing Set cnt = Nothing End If End Sub
source share