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

[John Colby]

It sounds like you are missing a dll.  Or it is the wrong version.


On 11/29/2017 6:36 PM, Bill Benson 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

-- 
John W. Colby