Solved: How to run external UDF's in a query?

[andrew93]

Hi everyone

I've created some custom functions in VB6 which I converted to a DLL file and I can now use these functions within VBA code. However, I cannot use these functions within a query (function not found error).

What do I need to create to be able to use these function in standard Access queries?

I tried creating an OCX file - but I'm not even sure that is the right thing to do. If I go down the add-in path, that requires an mda database with a USysRegInfo table or some other such thing and I can't get that to work. I've searched the internet without luck, read up on ActiveX controls in a VB6 book, checked out MSDN (I'm finding the MS instructions confusing) and I'm not sure what I am meant to do. I have a DLL already and that works with VBA but not queries.

Has anyone done this and if so, do you have any advice as to how to get functions written in VB6 working in Access queries? I know I could just write the functions in VBA but I am seeking a distributed solution via add-in / DLL / OCX etc.

TIA, Andrew

P.S. I found this thread : http://vbaexpress.com/forum/showthread.php?t=10592 in which Tom gave a very concise and handy tutorial on creating Excel DLL functions which I adapted for Access.

{edited typo}

[XLGibbs]

You might consider converting the UDF's to VBA and inserting them into the Access database as active code therein. Otherwise, I think you would have to create an Access add-in (which would likely be in VBA as well) much like an Excel add-in would be employed to house various UDF's

[andrew93]

Hi and thanks for your reply.

I was hoping to supply an add-in of some description (rather than embedding the code) but I'm not sure which method to use - is the mda database the way to go with this? If so, what's up with the USysRegInfo table? I can't get my add-ins to recognise the table exists. Or is there an easier way?

Andrew

[XLGibbs]

Hi and thanks for your reply.

I was hoping to supply an add-in of some description (rather than embedding the code) but I'm not sure which method to use - is the mda database the way to go with this? If so, what's up with the USysRegInfo table? I can't get my add-ins to recognise the table exists. Or is there an easier way?

Andrew

Not sure on this. Make sure you go to Tools>Security>Passwords and Permissions (or something similar). Go to the table in question, and check of the read data property..

With the Access system tables you need to check of the read data property for them to be accessed via code externally...could be the problem on why your code is not recognizing the object.

[andrew93]

Hi

The settings on the table were ok. What appears to have made a difference is placing a copy of the mda database into the C:\windows\add-ins directory. Now Access recognises the add-in and I can install it, BUT I still get the 'undefined function' error if I try to use any of the UDF's in a query or form (even after closing and restarting Access). There must be an easier way, I've been using Access for eons and it is never this hard!!!! I'm not sure what I am doing wrong....

:banghead:

TIA, Andrew

[XLGibbs]

Seems it is probably something really simple, do you have a sanitized sample I could try out on my end and see what I might figure out?


If you use a the formula builder utility in a query, can you see your function in the available options? If so, it would seem the function is available.

You may need to precede the function with the preceding object reference, kind of like how it works in SQL.

[andrew93]

Hi

No I cannot see the functions in the expression builder. I have an extremely sanitised version of the mda database attached.

In this version there are 4 functions (BOMonth, EOMonth, BOYear and EOYear). If you install this database as an add-in to another existing database of your own, can you get these functions to work in a query? If so, how did you do it? There is one module in that mda called MyDates and the one additional system table.

Thanks for looking

Andrew

[XLGibbs]

I can't access the functions. It appears that the add-ins for Access don't necessarily allow for the use of UDF's form within the .mda file itself. I could only see the functions present in a the expression builder from within the .mda file itself.

Tried a few things, but am stuck. Will keep looking into it though. I know you can have toolbars and builders and userforms and new menus, but can't find anything on external UDFs ... will keep trying, hopefully one of the real geniuses around here has experienced this one before...

[andrew93]

Thanks again for looking. You are experiencing the exact same issues that I am. I've read various threads on the 'net and I gather it can be done - however the MS site only refers to, as you mentioned, toolbars, menus and buttons etc. This is why I queried the best way of doing this in my first post. I can use a DLL file and call the functions using VBA but not in a query. Stan mentioned in this post:

http://vbaexpress.com/forum/showpost.php?p=82837&postcount=7

that he made something simlar using OCX for Excel but I couldn't get that to work either. Maybe Stan or Tom might swing by this thread sometime soon......

Cheers, Andrew

[XLGibbs]

I wonder if you couldn't have the add-in actually import/export the module containing the functions.

Maybe, once the add-in is installed it imports the VBA module into the users Access from a module in the add-in. Similar to how events might happen in excel during an on-open event..

I don't know, would be good to see an actual solution though! LOL

[stanl]

I may be mis-reading your intent, but I am assuming you want a query like "SELECT field1,field2 where BOMonth(field3)='12/01/2006'" and you want this query built and saved in Access. [and I'm guessing the query would return rows where field3 had a date in December 2006]. There are Access Basic functions that can simulate the results w/out need for your function. Otherwise if the dll/ocx was ActiveX and registered you could return values from you functions as variables and insert them into a query as module code. Then... I'm not sure but can't a query call a module:dunno Stan

[andrew93]

Hi Stan

Thanks for your reply. The sample I provided was a slimmed down version of what I'm trying to do. For example, the Excel analysis toolpak has functions like EOMonth etc but Access doesn't. The intent is to create a file that users can download and install to provide the ability to use various custom functions (like BOMOnth, EOMonth etc) in their forms, queries and reports etc.

You mentioned "if the dll/ocx was ActiveX and registered you could return values from you functions as variables and insert them into a query as module code" - can you elaborate on this? I've tried creating OCX and DLL files in VB6 and then installing in Access without success. Can you enlighten me to the steps or point me somewhere which will allow this? Per my earlier post I can get this working in VBA but not queries.

TIA, Andrew

[CBrine]

Andrew,
I use something similiar, I link a functions database into an existing database. The only way I was able to get access to the functions in a query, was to set them up locally as vba calls, and use the local vba calls in the local query.

ie. Function ConvertStores(StoreID)
Your Code
End Function
in funcations database.
I then setup a reference, and added this code locally.

Function ConvertStores2(StoreID)
ConvertStores(StoreID)
End Function

The VBA call works, it just that you can't use in a query for some reason.

I usually setup a default module to store all the FUNCTION database calls, to make copying and pasting easy. You should now be able to use ConvertStores2 locally, which calls ConvertStores using vba.

HTH
Cal

[stanl]

You mentioned "if the dll/ocx was ActiveX and registered you could return values from you functions as variables and insert them into a query as module code"

Yes, but a module is what you can already do... write VBA code call the functions and insert return params into SQL Statements. You could try this: create a generic function [in your dll/ocx] to accept an SQL Statement, that statment can include calls to functions within the dll/ocx and should be resolved there. Then you will need a generic VBA module which passes the SQL query. Of course this requires a user interface to assure the user can construct valid SQL... probably why I wanted to see a specific query statement. .02 Stan

[andrew93]

Hi Cal and thanks for your reply.

You nailed it in one. I missed the obvious - given I already had it working in VBA I need the users to create a local function to call the imported one. It's not perfect but it's going to have to do.

Thanks very much
Andrew

[CBrine]

Andrew,
Yeah, I ended up with the same unsatisfactory solution. I worked on it off and on for about a week, and I wasn't able to find anything else. Having a single source to modifiy for code changes for all the linked database's made it worth while though.

Glad I could help.

Cal