Class does not support automation (err 430) - on copyfromrecordset

[tjharlan]

Hi All - struggling with the below. Simply trying to paste an Access
recordset into Excel. I am getting the "Class does not support Automation"
error at run-time. Please see all details below.

Your advice or direction would be greatly appreciated. Thank you.

Rgds,
T.J.

ERROR: "Run-time error '430'; Class does not support Automation or does not
support expected interface"

LINE OF CODE TRIGGERING THE ERROR MSG:
xlWs.Cells(2, 1).CopyFromRecordset rs2

XL VERSION - Excel 2003 - SP2
ACCESS VERSION - Access 2003 - SP2

REFERENCES USED:
* Visual Basic for Applications
* Microsoft Access 11.0 Object Library
* OLE Automation
* Microsoft ActiveX Data Objects 2.8 Library

CODE:

Sub fileclean2()

DoCmd.Hourglass (True)

Dim strDir As String
Dim strFile As String
Dim strFindText As String
Dim strReplaceText As String
Dim strContents As String
Dim strStart As String
Dim strFinish As String
Dim XlApp As Object
Dim xlwb As Object
Dim xlWs As Object
Dim rs As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset
Dim strSQL As String

strStart = Now()

Const ForReading = 1
Const ForWriting = 2

'open excel
Set XlApp = CreateObject("Excel.Application")
XlApp.Visible = True

'open worksheet to create report
Set xlwb = XlApp.Workbooks.Add
Set xlWs = xlwb.Worksheets("Sheet1")

rs.ActiveConnection = CurrentProject.Connection
rs2.ActiveConnection = CurrentProject.Connection

strSQL = "select distinct location, analyst from tblABCD"
rs.Open (strSQL)

strSQL = "Select contra from tblABCD where location = '" &
rs.Fields("location") & "' and analyst = '" & rs.Fields("analyst") & "'"
rs2.Open (strSQL)

'add headers to report
fldcount = rs2.Fields.Count
For iCol = 1 To fldcount
xlWs.Cells(1, iCol).Value = rs2.Fields(iCol - 1).Name
xlWs.Cells(1, iCol).Font.Bold = True
Next

xlWs.Cells(2, 1).CopyFromRecordset rs2

strFinish = Now()

MsgBox "Finito! " & strStart & " " & strFinish

DoCmd.Hourglass (False)

End Sub

 

[AlanLee]

TJ,

I had this same problem and posted an entry in the excellent
www.utteraccess.com.

Sadly I could not get aresponse there either. The issue I had was the
copyfromrecordset worked perfectly on my home PC, but had this error when on
a client location.

My code was also very similar.

Did you ever get a solution to this.

Thanks, Alan Lee

 

[T Jarratt]

I am supporting a similar product using Access and Excel to copy recordsets.

The exact same problem came up where everything runs fine on my local
machine, but stops in Access with a run-time error of 430 (class does not
support automation). I've been able to figure out that it relates directly to
the active x dlls in program files/system/ado and that the "general" way to
fix this is to install the latest MDAC from microsoft's website.

Unfortunately, even with the latest version, this problem persists.

Anyone have any luck with this?

-T Jarratt

 

[T Jarratt]

I found this on another board after hours and hours of searching. I tried
this and it immediately solved the issue. The post I'm quoting is several
years old, so the example is with a DAO database, but it applies directly to
ADO. Instead of Common Files/Microsoft ... the directory and file in question
is :
program files\common files\system\ado\msado15.dll

Good luck, all.


SOLUTION - here's what worked for me (in case this might help others)

I needed to register the dao360.dll. That's it! To register a dll, run
the following command (Start > Run...):
Regsvr32.exe "C:\Program Files\Common Files\Microsoft
Shared\DAO\DAO360.DLL"

If it's a different DLL you're having trouble with, replace the path
above with the path to the dll in question.
You don't even need to reboot. Magic.