CopyFromRecordset Automation Error

Question

ExcelSheetPopulate.txtI had been running Office 2016 MSO (16.0.12624.20278) 32-bit for several years.

I've been using the attached code in MS Access for the past 5 years without any issue.

Suddenly, getting Error 430, 'Class does not support Automation or does not support expected interface', on line 470 CopyFromRecordset.

It runs fine on other machines using various versions of Office. I have been unable to resolve the error on my dev machine.

Repaired Office, both offline and online repairs.

Uninstalled (via the uninstall troubleshooter) and reinstalled to various versions, including the very latest.

Currently running 2019 MSO (Version 2208 Build 16.0.15601.20526) 32-bit.

Unregistered/re-registered both dao360.dll and msado15.dll.

Also ran DISM and sfc.

There are no missing references.

How can I resolve this error?

Answer 1

@Viorel It's the other way around; DAO has been the default, preferred, recommended, and most widely used engine since at least version 2007(!) where ACE were introduced.

Answer 2

A few minutes after my last post, I see that if I change the sample code to use an ADO recordset instead of DAO, the CopyFromRecordset command works fine using the same data. Go figure.

I still want to find out why the DAO method has stopped working. I have too many legacy calls that worked fine with the DAO implementation. I don't want to just assume that all of my old queries will work with a switch to ADO.

For example, I'm not sure if ADO handles user defined function calls in queries the same way as ADO. Same goes for Pivot queries, Unions, embedded references to on form controls, etc.

Please don't try to convince me to "just switch to ADO because it works". I have dozens of applications that I'd have to change, and some where the source code is not longer around.
We really want to know what changed in Windows/Office to cause this issue.

Sub ExportToExcel_ADO()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim xlApp As Excel.Application
    Dim xlWorkbook As Excel.Workbook
    Dim xlWorksheet As Excel.Worksheet
    
    On Error GoTo ErrorHandler
    
    ' Initialize objects
    Set cn = CurrentProject.Connection
    Set rs = New ADODB.Recordset
    rs.Open "qryTest", cn, adOpenStatic, adLockReadOnly
    
    ' Create Excel application object
    Set xlApp = New Excel.Application
    xlApp.Visible = True
    
    ' Add new workbook and set reference to first sheet
    Set xlWorkbook = xlApp.Workbooks.Add()
    Set xlWorksheet = xlWorkbook.Worksheets(1)
    
    ' Copy headers from recordset
    Dim i As Integer
    For i = 0 To rs.Fields.count - 1
        xlWorksheet.Cells(1, i + 1).Value = rs.Fields(i).Name
    Next i
    
    ' Format headers
    With xlWorksheet.Range(xlWorksheet.Cells(1, 1), _
                          xlWorksheet.Cells(1, rs.Fields.count))
        .Font.Bold = True
        .Interior.ColorIndex = 6
        .HorizontalAlignment = -4108 'xlCenter
    End With
    
    ' Copy recordset data starting from row 2
    xlWorksheet.Range("A2").CopyFromRecordset rs ' <-- This line appears to work with an ADO recordset.
    
    ' Auto-fit columns
    xlWorksheet.UsedRange.Columns.AutoFit
    
    ' Clean up
    rs.Close
    Set rs = Nothing
    Set cn = Nothing
    
    Exit Sub
    
ErrorHandler:
    Debug.Print Err.Number, Err.Description
    MsgBox Err.Description, vbCritical, "Error"
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    Set cn = Nothing
End Sub