CopyFromRecordSet doesn't on DAO in Access 2007?

[cycnus]

Does anyone else have the same issue?
I'm using Access 2007 and trying to export a DAO recordset to excel using
CopyFromRecordset but I systematically get a "Run-Time error 430, Class does
not support Automation or does not support expected interface".
When I use ADO, it works fine.

Everything is up-to-date on my PC, doesn't seems to be any broken references,
and everywhere I look, it says that CopyfromRecordSet should work with both
ADO and DAO, yet it clearly doesn't.

So anyone has more information on this, shared the same experience or can
confirm that I'm not alone with this issue?

Thank you,
--
Renaud Bompuis
http://blog.nkadesign.com
http://etc.nkadesign.com

[Rich P]

The following subroutine is run from an Excel code module and uses a
Reference to the Microsoft ActiveX data objects library 2.+ (can use 2.1
through 2.8)

Sub ReadDataFromAccess()

Dim cmd As New ADODB.Command, RS As New ADODB.Recordset
cmd.ActiveConnection = "Provider = Microsoft.Jet.OLEDB.4.0; Persist
Security Info=false;Data Source=C:\Code\testAcc\db1Test.mdb"
cmd.ActiveConnection.CursorLocation = adUseClient
cmd.CommandText = "Select * from tbl1"
Set RS = cmd.Execute
Range("A1").CopyFromRecordset RS

End Sub

DAO works great inside Access -- easier to use than ADO for manipulating
Access tables/Access dataobjects. But from Excel -- ADO is easier/more
reliable to use for database operations (with Access/sql server/oracle).

DAO is my data access model of choice when I am working within Access
with Access data (data contained in Access tables). But if data resides
anywhere outside of Access (sql Server, Excel...) ADO is my data access
model of choice -- including from within Access.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[cycnus via AccessMonster.com]

Hi Rich,

I know it works fine with ADODB but I'm using DAO in my application.
My aim was to simply pass a DAO RecordSet to a sub that would export the
content of the recordset to Excel.
I know I can export queries and form content to Excel using 'DoCmd.
TransferSpreadsheet' or 'DoCmd.OutputTo', but in my case I'm trying to export
a subform in datasheet mode whose RecordSource is modified in code. The
purpose of this was to export the data viewed by the user to a spreadsheet.
Turns out it's a lot harder than I expected in my case as:
- I'm not using a named query but a SQL statement that I modify on the fly,
so I cannot pass it to the DoCmd methods as they expect named queries.
- my SQL statement is based on another query that is dependent on the value
of a combobox on the main form, and it seems that using ADODB doesn't
correctly interpret that (it keeps generating an error saying that a
parameter value hasn't been provided, even though it works fine in DAO).
- the data I want to export being on a subform, 'DoCmd.outputTo acForm' only
exports the main form, not the subform. I even tried to get a fully qualified
name for the subform to no avail: it only wants a form name, not the name of
a form instance.

Instead, I'll have to reinvent the wheel and code a sub that will take a
form's reference and output an excel file matching the form's datasheet
layout.

That's Access and VBA for me... ;-(

--
--
Renaud Bompuis
http://blog.nkadesign.com
http://etc/nkadesign.com

Message posted via http://www.accessmonster.com