Automation Error upon running VBA code in Excel

Hi experts,

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.

The error I get is,

"Run-time error '-2147217843(80040e4d)': Automation error".

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.

Here is the code with ODBC as the 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};; Database=fooDB;Uid=logonalready;Pwd='helpmeOB1';"
    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.
     'Reclaim memory from the connection objects
     Set rst = Nothing
     Set cnt = Nothing
   Exit Sub
   MsgBox Err.Description, vbCritical
   Resume ErrorExit
   'clean up and reclaim memory resources.
    If CBool(cnt.State And adStateOpen) Then
    Set rst = Nothing
    Set cnt = Nothing
    End If
End Sub
Who is Participating?
Anthony PerkinsConnect With a Mentor Commented:
Try this connection:
cnt.ConnectionString = "Provider=sqloledb;Data;Initial Catalog=fooDB;User Id=logonalready;Password=helpmeOB1;"
Patrick MatthewsCommented:
What line does the debugger jump to?

If it's where you open the connection, try modifying the connection string to remove the single quotes around the password.
brohjoeAuthor Commented:
Thanks for responding matthewspatrick.  I believe you answered a question for me a while back.

I took the tick marks off the password, but I'm still getting the same error.   I also tried using parentheses next to cnt.Open() but that gave a "Compile error: Expected ="

It's definitely dying at the 'cnt.Open' command.
Never miss a deadline with

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

I'm having the exact same problem, I can connect directly to sql using the sql server connection string using the connections dialog and the info gets imported directly in the excel spreadsheet.
When I try to do it trough VBA I get the "Automation Error" error
Any suggestions?
Anthony PerkinsCommented:
>>Any suggestions?<<
Try posting your own thread?
brohjoeAuthor Commented:
brohjoeAuthor Commented:
If someone chimes in on a thread and says they have the same question... why should they post the same question in another thread if it's already been asked and not answered yet?

Just a thought...just a thought.
Anthony PerkinsCommented:
>>why should they post the same question in another thread if it's already been asked and not answered yet?<<
That is all covered in the EE Guidelines that you agree to every time you sign on to this site.  If you do not agree to that feel free to post your comment here:
Anthony PerkinsCommented:
But back to the question, did you try the connection string I posted here http:#a28886826 if so what was the outcome?
brohjoeAuthor Commented:
Ok, I'm connecting fine.  I'm connecting but I'm getting another error past the connection string.  I'll open another question for that.
All Courses

From novice to tech pro — start learning today.