[AccessD] Excel 2013 CopyFromRecordset "Interface not registered" error when copying Ac2013 DAO recordset to a range

[Paul Hartland]

Long time since used copy from recordset, I imagine you have tried both
these suggestions but just in case

1. Have you checked the value being returned to iRecords.

2. Have you tried removing the Resize(iRecords, Rst.Fields.Count). As a
test before looking any further.

Paul


On 29 Nov 2017 23:38, "Bill Benson" <bensonforums at gmail.com> wrote:

I am looking far and wide for anyone who may have an idea what governs the
CopyFromRecordset method of an Excel range, and can help determine where
automation might be getting hung up when using this in connection with a
DAO recordset.

The precise error is

"Microsoft Visual Basic

    Run-time error '-2147467262 (80004002)'"

    Interface not registered "

The only time I get this error is when we are using a Microsoft Access
database and I have written code to create a DAO recordset and want to
populate an Excel range using the CopyFromRecordset method.

This below code works in all environments EXCEPT the  Virtual Desktop
Infrastructure
<http://www.microsoft.com/virtualization/solution-product-vdi.mspx> (VDI).
Our tech support performed a MS Office Repair on the image but this did not
resolve the error. Experimenting, revealed that the same procedure modified
to use an ADO recordset works, but not with a DAO recordset.

Sub TestDAORecordset()

Dim CurDB As DAO.Database

Dim Rst As DAO.Recordset

Dim EX As Excel.Application

Dim iField As Long, iRecords As Long

Dim WB As Excel.Workbook

Set EX = New Excel.Application

EX.Visible = True

Set WB = EX.Workbooks.Add

Set CurDB = CurrentDb

Set Rst = CurDB.OpenRecordset("Select * from [PDR TRACKING]")

With WB.Worksheets(1)

    For iField = 0 To Rst.Fields.Count - 1

        .Cells(1, iField + 1).Value = "'" & Rst.Fields(iField).Name

    Next

    Rst.MoveLast

    iRecords = Rst.RecordCount

    Rst.MoveFirst

    'RUNTIME ERROR ON NEXT LINE

    .Cells(2, 1).Resize(iRecords, Rst.Fields.Count).CopyFromRecordset Rst

End With



End Sub
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com