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

Question

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)

HansUp · Accepted Answer · 2024-08-18 20:46:16Z

1

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

edited Aug 18, 2024 at 20:46

answered Apr 26, 2019 at 14:36

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Peter Over a year ago

Thank you HansUp. This resolved my problem. It runs without giving the run-time error.

Gustav · Accepted Answer · 2019-04-26 14:01:22Z

1

Most threads say it can't be done,

and they are right.

Your only option is to use automation to open an instance of Access and, within this, run the query.

answered Apr 26, 2019 at 14:01

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

2 Comments

Peter Over a year ago

Gustav - This has worked for me and others at the office for many months on the same query. Why would it have worked previously and suddenly not now? And perhaps as a follow-up do you have a suggestion on automation? I will have to google, but any help on it would be appreciated.

Gustav Over a year ago

Can't tell why it has worked, it shouldn't. @HansUp kindly provided the example.

Albert D. Kallal · Accepted Answer · 2019-04-28 05:38:33Z

Well, as noted, most are saying this should not work.

However, if you are 100% sure it was and did work at one time?

You need to set the "sandbox" mode of the JET (now ACE) database engine.

The expression service normally does not allow evaluation of VBA functions as a security setting to prevent SQL injection, or code running outside of Access to allow SQL to run + call VBA functions. At one time, this feature did default to "on", but now the default is set to access only.

You have to set the folder where Access application as trusted. This should allow the VBA functions to now work. so, make sure you set the folder as trusted.

If the location (folder) where your access application is NOT trusted, then Access will use sandbox mode, and VBA in the SQL will not run.

If the location is trusted, THEN access uses the registry setting on your computer.

My bets are that the location is not trusted - so you always get sandbox mode for SQL in Access.

If you are 100% sure that the folder location is set as trusted in Access, and you still receive the errors, then you have to change the registry setting for Access "sandbox" mode.

The setting in the registry is outlined here: https://support.office.com/en-us/article/Turn-sandbox-mode-on-or-off-to-disable-macros-8CC7BAD8-38C2-4A7A-A604-43E9A7BBC4FB

The registry settings are:

for x32 bit access:

Software\Microsoft\Office\ClickToRun\Registry\Machine\Software\
Wow6432Node\Microsoft\Office\16.0\Access Connectivity Engine\Engines

The above is for Office 2016

14 = 2010

15 = 2013

16 = 2016

The key value for sandbox mode is: 0 to 3

0 Sandbox mode is disabled at all times.

1 Sandbox mode is used for Access, but not for non-Access programs.

2 Sandbox mode is used for non-Access programs, but not for Access.

3 Sandbox mode is used at all times. This is the default value, set when you install Access

So, from above, you want a setting of 0.

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related