We help IT Professionals succeed at work.

create dll lib usable to all MS office applications (especially Access and Excel)

wolfking
wolfking asked
on
162 Views
Last Modified: 2010-05-03
I have a function library written in VB as a module in an access .mdb database. Since all my databases call these functions (such as computing month ending date, generating correct date format) quite often in Access sql queries
I would like to create a DLL library so that all my Access database can use the function in queries without setting reference for each individual database. Also if possible I would like to use the functions in Excel spreadsheet as well.

What I am hoping for is that, after I register myLib.dll and set reference to it in Access, I can do the following in all my Access databases:

select  *
from  myDatabase md
where functionFromMyLib (md.date) > 0

Please give me some advice on how to accomplish this. I would really appreciate it.
Comment
Watch Question

You need to create a class module in acces and make those code defined as public there. now you can easily add that
class modules to any code in the access.

Author

Commented:
Thanks for the reply but this doesn't completely solve my problem.

Say the lib functions are in a module of database A.mdb, and i want to use it in B.mdb. What I am doing now is to set a reference in B.mdb to the module in A.mdb. This way I can call all these functions from database B. The thing is I have to set a reference for EVERY  database from which I want to call the lib functions. Plus I cannot use the fuctions in Excel.

The ideal situation is that I can use these functions in ALL my Access databases and Excel workbooks by setting reference for only Access and Excel, not each individual database or workbook.

I've looked into com add-in's, but I am still not sure how this can be done with COM Add-in's and if can whether that's the best solution.



Oh ok. Now I Realize what you mean, as you creating a dll in access is not possible so you have ot use VB:

Step 1:
Create a new project (in VB) as activeX All

In it's  class module put your functions ( as public)


Step2:

Build the project to made a DLL(Assuming project1.DLL is built)

Step3:
Now using those function is easy everywhere:
All you have to do to reference that dll:

Dim a As Object
Set a = CreateObject("project1.class1")  '<========project1 is your dll name, class1 is the class name inside it

'now  calling a fuction is easy


dim b

b=a.FunctionA(4) '<======== note that functionA and value 4 are just examples.


If its not clear please mention.




sorry for the typo
Step 1:

Create a new project (in VB) as activeX DLL

Author

Commented:
Thanks for the suggestion. In fact I have done exactly what you suggested and was able to use the functions in VB coding. But I cannot use them in Access queries the way I described in my original question, which is what I want most. Hope you can help me to accomplish that :)

Hi,

I used this code inside the Access and Excel(Not VB) and both of them worked fine.

Dim a As Object
Set a = CreateObject("project1.class1")  '<========project1 is your dll name, class1 is the class name inside it

dim b

b=a.FunctionA(4) '


so this applies directly to your original question.

Author

Commented:

I agree. This works in VBA or macros. What I mean is, say in Excel, you can use it directly in a cell like the build in functions Exp, Upper, etc.

when I set reference to my library modul in Access databases, I was able to use the functions directly in Access queries. That's the kind of effect I want. The only disadvatage of that approach is that I had to set reference for All my databases, and also it's probably not as fast as a DLL lib.
when I set reference to my library modul in Access databases, I was able to use the functions directly in Access queries.
===========================

How dp you set a reference to a library in access , except the method I mentioned above?

Author

Commented:
I open database A.mdb, create a module, open the VB editor in the module and set reference to the lib module in myLib.mdb by choosing "references ..."  under the "Tools" tab. Now I can use the functions directly in database A.
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Dear unknown,


I am still thinking about COM Add-in's. I do believe this is possible, since that's what COM Add-in is for. Just don't know how to do it exactly. anyway thx for your help.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.