Cannot retrieve data via Excel if query got user-defined function

[klar]

Hi Access experts,

I would like to retrieve data from Access using Excel VBA through ADO. This was successfully done in the past until a user-defined function was added to one of the fields in the Access query. On Access, the query can run successfully. On Excel, an error "Microsoft Access Database Engine -> Undefined function in expression" is returned.

Has someone encountered similar problem before? Any workaround? I think using user-defined function in queries should be quite common because it is so useful.

Thank you.

 

[Trevor G]

Take a look at the Attached workbook and database, I have created some code that will look at a database and using a query. When you select the drop down in the Excel it will list some Department Names on selecting the Department name it then clears the data in the sheet and fetches the new data from the database.

The database has only 2 elements in it. tblStaff and a query qryGoToExcel

Save them in a folder and then adjust the code in the Workbook to look for the database in that folder.

I hope it helps you out.

 

[klar]

Thanks for the sample files. My problem actually comes from using a user-defined function inside an Access query and getting Excel to extract data from this query. There are no UDF functions in your query. But, thank you for your help.

 

[Trevor G]

What is the UDF you have and what is its purpose?

Is it possible to upload an extract of the database with the UDF and Query in it. No real data to test and see if I can help...

 

[trgz]

I appreciate it's very old thread but I just had a similar problem across several queries which I've just resolved this morning by substituting the UDF into the query (I actually did this programmatically by looping through all the querydefs in the currentDB and replacing strings in the queryDef.SQL). So where I had 'wkst()' (a weekstart function I wrote) I substituted it with 'Int(Now()) - Weekday((Now),2)+1' thus moving the function into the query itself - messier IMHO but it works (and there were 140-210 instances of it in each query!)