CopyFromRecordset Worked Fine In Access2000 But Not in Access2003 1

[hazlehurstga]

When I execute this statement ".[a1].Offset(1).CopyFromRecordset NAVICPGListTable" from code below I receive the following error: "Run-time error '430': Class does not support Automation or does not support expected interface"
Does anyone know why this statement does not work in MSAcess 2003? NOTE: The recordset 'NAVICPGListTable' refers to a table in an external database, not the current db.

Thanks. Code excerpt follows:

"With Workbooks("OverallGAge.xls").Worksheets("Chart Data")
' Clear existing data
.Range("A2:CJ300").Clear
Set db = currentDB
' Define the SQL statement to retrieve NAVICP average G age data
SQLString = "SELECT a.SOURCEFILENAME, Avg(a.[AvgOfG AGE]) AS [AvgOfAvgOfG AGE] " & _
"FROM [NAVICP COMPONENT AVG G AGE CALCULATION] AS a " & _
"GROUP BY a.SOURCEFILENAME " & _
"ORDER BY a.SOURCEFILENAME;"
' Open the recordset
Set NAVICPGListTable = db2.OpenRecordset(SQLString)
' Transfer the records into excel
.Visible = True
Excel.Application.Visible = True
.Activate
".[a1].Offset(1).CopyFromRecordset NAVICPGListTable
' Get total rows

 

[PHV]

Seems you have an implicit instanciation of Excel here:
With Workbooks("OverallGAge.xls").Worksheets("Chart Data")
instead of:
With yourExcelObject.Workbooks("OverallGAge.xls").Worksheets("Chart Data")

Seems you instantiate db (Set db = currentDB) and want to use db2 (Set NAVICPGListTable = db2.OpenRecordset(SQLString)).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[hazlehurstga]

Thanks PHV! The problem was resolved when I created a workbook object and referred to that object vice using the implicit reference. Apparently Access2003 requires that I declare workbooks as objects vice using implicit statements. Thanks again

 

[bernie10]

Hello,
I am having the exact same problem and getting the exact same error message. I'm assume I have the same error as hazlehurstga but I am not seeing where it is. Here is the relevant code:

Private Sub Command0_Click()
Dim objExcel As Excel.Application
Dim objWB As New Excel.Workbook
Dim objSht As Excel.Worksheet
Dim rs As New ADODB.Recordset

Set objExcel = CreateObject("Excel.Application")
Set objWB = objExcel.Workbooks.Add
Set dbMyDB = CurrentDb
Set rs = New ADODB.Recordset
Set objSht = objWB.Sheets(1)

rs.Open "select * from [MyTable], CurrentProject.Connection, adOpenKeyset, adLockOptimistic"
With objSht
    .Cells(firstrow + 1, 2).CopyFromRecordset rs
    countrecords = rs.RecordCount
End With
End Sub

Thanks in advance for any help.

Collen

 

[RoyVidar]

Seems you are not closing/releasing the objects when exiting the sub. That may create anomalities. Else, I'm not sure, but the CopyFromRecordset method is a method of the Range collection, have you tried something like this?

[tt].range(.Cells(firstrow + 1, 2)).CopyFromRecordset rs[/tt]

And, I think I'd work a little on the select statement, try:

[tt]rs.Open "select * from [MyTable]" , CurrentProject.Connection, adOpenKeyset, adLockOptimistic[/tt]

Roy-Vidar