Running Access queries containing user defined functions in Excel

Posted on 2011-10-06
Last Modified: 2012-05-12
Hi, I have an Access database with queries which references user defined function located in a module in the database.  The query runs fine in Access.  

I would like to run this query from VBA Excel so I can dump the results into my spreadsheet.  However, I am getting an error saying that my user defined function cannot be found: Run-time error '3085'  Undefined function 'userfunction' in expression.

Is there a work around to this?  I tried cutting and pasting the Access vba code for that function into the Excel module, but am getting the same error.

Thanks in advance!  

Question by:Hammer8
    LVL 92

    Accepted Solution

    How are you attempting to run it from Excel?

    You'll need to automate Access.

    Author Comment

    Yes, trying to run it from Excel VBA.  What does automate from Access mean?  I need to initiate the query from Excel because my macro does more than just pull in the query in question.  Thanks.
    LVL 85

    Assisted Solution

    by:Rory Archibald
    What Patrick means is that you will need to have your code actually start Access and run the query in the Access application. You will not be able to use the queries as they stand outside of Access itself. Your alternative would be to pull in the raw data, without whatever processing the UDFs do, and then process it in Excel instead.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now