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

[Bryan Fitzpatrick]

I use CopyFromRecordset fairly regularly, and usually just declare it as 
a Recordset variable rather than a DAO.Recordset variable.  I seem to 
remember that CopyFromRecordset is associated with ADO, and there is no 
DAO equivalent.*/

/*Cheers*/
Bryan Fitzpatrick
Mobile: 0418 618 469/*
On 30/11/2017 1:19 PM, Bill Benson wrote:
> Thanks Paul, neither of those is the issue. This has to be something in the
> registry or some type library issue.
>
> On Wed, Nov 29, 2017 at 7:26 PM, Paul Hartland via AccessD <
> accessd at databaseadvisors.com> wrote:
>
>> 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
>> --
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>>



---
This email has been checked for viruses by AVG.
http://www.avg.com