User Defined Functions (UDF) from Access Query to Excel using VBA OpenRecordset failed - Undefined Function

How do I get the results of a query from Access into Excel if it has a UDF?

I receive the following error: "Run-time error '3085': Undefined function 'XXXX' in expression". The error occurs when opening an (access query) recordset from Excel VBA. The query being opened has a user defined function (UDF) which is triggering the error.

The code is in Excel Office 365. The query is in Access Office 365.

I have successfully utilized the query being called (and others with the UDFs) for about twelve months, and "suddenly" it is not working any more. I have googled and tested many options with no success.

Most threads say it can't be done, or to not use a udf but try a built-in that works. I am challenging those responses because it has worked previously. The main udf I am using is one called "iMax" which is written about in other posts. It functions like max() in Excel. (No max(x,y) function in Access)

I have also seen threads that suggest executing this in two steps: 1 - change the query to a make table query. 2 - pull the table results into Excel. While I could maybe get away with this (after much rework), it would result in me making many temporary tables with thousands and thousands of rows and doesn't seem very slick.

I have compiled vba and compacted the db with no impact to my problem.

As a long shot I created a dummy database with a simple udf public function that returned the number 1, a simple query that returns three records and a field for the function results. This gets the same error when pulling into Excel.

Sub RunQuery()
Dim MyDatabase As dao.Database
Dim qdf As dao.QueryDef
Dim rs As dao.Recordset
Dim qryname As object
Dim SheetName As String

Set MyDatabase = DBEngine.OpenDatabase _
("SomePath\SomeFilename.accdb")

For Each qryname In Range("SomeRange")
    Set rs = MyDatabase.OpenRecordset(qryname)      '<<<ERROR IS HERE
    SheetName = "SomeSheetName"
        With Sheets(SheetName)
            .ListObjects(SomeTableName).DataBodyRange.Rows.ClearContents
            .Range("A2").CopyFromRecordset rs
        End With
    Set rs = Nothing
    Set qdf = Nothing
Next qryname

End Sub

For all queries in the For loop that do not have a udf, the results are pulled and dumped into a series of tables in Excel. Any query with a udf errors at the "Set rs = Mydatabase.OpenRecordset(qryname)

If you run the query within an Access application session, as Gustav suggested, the expression service can handle the UDF in your query.

Here is a quick tested Excel VBA snippet which pulls data from a query which includes a UDF:

Const cstrDbFile As String = "C:\share\Access\Database2.accdb"
Dim objAccess As Object
Dim rs As Object
Dim ws As Worksheet
Dim strSelect As String

Set objAccess = CreateObject("Access.Application")
objAccess.Visible = True ' useful during testing '
objAccess.OpenCurrentDatabase cstrDbFile
strSelect = "SELECT ID, DummyFunction('a', '', 'c') FROM Dual;"
Set rs = objAccess.CurrentDb.OpenRecordset(strSelect)
If Not rs.EOF Then
    Set ws = ThisWorkbook.Sheets("Sheet1")
    ws.Range("A1").CopyFromRecordset rs
End If
rs.Close
objAccess.Quit