Getting Error 430: Automation error

Question

I'm working on getting SQL into Excel using vba with the following script (censored to protect some db info, however the parts of the script work) When I go to compile I get the Run Time Error 430: Class Does not support Automation or does not support expected interface

I am using Excel 2007, VBA 6.5 and SQL 2005

Here's the script

Sub GetDataFromADO()

    Dim objMyConn As ADODB.Connection
    Dim objMyCmd As ADODB.Command
    Dim objMyRecordset As ADODB.Recordset

    Set objMyConn = New ADODB.Connection
    Set objMyCmd = New ADODB.Command
    Set objMyRecordset = New Recordset

    objMyConn.ConnectionString = "Provider=SQLOLEDB;SERVER=M$#$%#@@$1;DATABASE=i*&^*;Trusted_Connection=yes;HDR=yes"
    objMyConn.Open

    Set objMyCmd.ActiveConnection = objMyConn
    objMyCmd.CommandText = "use i*&^* select * from $#%.^&_d**^%_p**^^l where year(d****_date) = 2012 and month(d***_date)= 03 and deal_id < 5*******0 order by d***_id"
    objMyCmd.CommandType = adCmdText
    objMyCmd.Execute

    ActiveSheet.Range("A2").CopyFromRecordset (objMyRecordset)

End Sub

I am having difficulty seeing how that script can work. For one, the connection string does not match anything here. Which line gives the compile error? — Fionnuala
– Fionnuala, Commented Apr 10, 2012 at 8:10

Pradeep Kumar · Accepted Answer · 2012-04-10 10:23:23Z

2

You have initialized the objMyRecordset but never fill data into it. Then you are trying try to import data from objMyRecordset into your excel file which will not work.

First ensure that you have filled the recordset, and then only let the CopyFromRecordset be called.

edited Apr 10, 2012 at 10:23

answered Apr 10, 2012 at 10:15

Pradeep Kumar

7,0294 gold badges24 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Getting Error 430: Automation error

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related