title: Automation Error upon running VBA script in Excel
body: <p>I'm getting an Automation error upon running VBA code in Excel 2007.  I'm attempting to connect to a remote SQL Server DB and load data to from Excel to SQL Server.</p>
  
  <p>The error I get is, </p>
  
  <p>"Run-time error '-2147217843(80040e4d)': Automation error".</p>
  
  <p>I checked out the MSDN site and it suggested that this may be due to a bug associated with the sqloledb provider and one way to mitigate this is to use ODBC.  Well I changed the connection string to reflect ODBC provider and associated parameters and I'm still getting the same error.</p>
  
  <p>Here is the code with ODBC as the provider:</p>
  
  <pre><code>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',   &amp; _
      "'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
  </code></pre>