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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
Anthony PerkinsCommented:
Try this connection:
cnt.ConnectionString = "Provider=sqloledb;Data;Initial Catalog=fooDB;User Id=logonalready;Password=helpmeOB1;"

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.