create dll function without class instances to use in access queries

Question

Hi,

I have following problem:

I use Access and want use function of a dll in queries.

The background: I want publish some access solutions, but not the code of the function which are used in the queries.

So the function should be placed in the dll.

The dll is bind to the access db and so the functionality of the function can be used without to publish that code.

The problem is, I found a lot to create a activeX-dll, but with this I can only make class functions. (classinstance.myfunction)

But this function I can't use in the queries!

(or maybe someone of you have a solution?!)

So I would like make function without class functionality - or in other words, functions that I can use in a query though these functions are in a dll bind to this access db.

Hope my descrition is clear,

Thank you,

Joss

Answer 1

Why not simply create a library database to house your various functions?

See: http://www.granite.ab.ca/access/addins.htm

Answer 2

The background: I want publish some access solutions, but not the code of the function which are used in the queries.

If all you want is to protect the VBA code used in your functions compiling to an accde does that. Because an accde is compiled, there is no code to access.

Answer 3

First, there nothing stopping you from using a COM object (ActiveX) in a Access query – but you will have to use public VBA function in the SQL query that in turn calls + uses that external ActiveX .dll. (as a ActiveX object)

You have to use a VBA function regardless if you do have a external .dll anyway.

Also, you given nothing about the tool(s) or platform you going to create the external .dll with? So the answer will MUCH depend on what developer tools you going to make the .dll with.

Are you talking about managed code (.net) to create the .dll, or are you talking about NON managed code such as c++, or VB6?

So with c++, (and with some kluges in VB6), you can create native code .dll’s and such code can be consumed without having to use ActiveX object.

However, the fact that you create an ActiveX object is not really a issue anyway. In fact your code solution at the end of the day will change little.

As I noted above, you can simply create a public function that in turn calls + uses the COM object. You need a VBA PUBLIC function exposed for use in a SQL statement anyway.

And to keep performance high?

Just make sure you NOT creating an instance of the object for each time you call the function, since that VBA function is going to be called for each row of the SQL query - that can be a LOT of function calls.

Thus, simply have the COM object at the VBA module level as opposed to the VBA function level – this will result in the COM object ONLY being created the FIRST time you call the VBA function – not for each additional call to the VBA function. Remember, if you have 2000 rows, you calling that function 2000 times – that is a lot!

Let’s make a simple .dll in vb.net that will multiple the given value by a factor of 2. While this example is dead simple, it could be far more complex and shows how you can call a vb.net .dll from Access.

So in vb.net, we would have this code:

Imports System.Runtime.InteropServices

<ClassInterface(ClassInterfaceType.AutoDual)>

Public Class AlbertCom1

    Public Function Times2(Value As Object) As Single

        If (Value Is Nothing) Then

            Return 0

        Else

            Return Value * 2

        End If

    End Function

End Class

So compile the above (in vb.net, it called “build solution”).

The above results in one .dll. And assuming the “register” for interop box is selected when you build the above few lines of code, then you good to go.

In MS Access, you see this this as a reference in the VBA editor:

 

And the VBA code in the Access module would be this:

Option Compare Database

Option Explicit

Dim MyDLL As New AlbertCom1.AlbertCom1

Public Function MyTwoTimes(v As Variant) As Double

   MyTwoTimes = MyDLL.Times2(v)

End Function

Note how the COM object is defined at module level – it will thus ONLY be created one time during use of the sql string. (in fact the com object will be created the FIRST time the Public Function is called.

In the Query builder I can now go:

SELECT FirstName, LastName, ID, MyTwoTimes([id]) AS ID2

FROM tblHotels2

And the resulting output is this:

 

So nothing really stopping you from using a COM object here (ActiveX).

However, the above is a WILD guess on my part that your development platform is .net and is managed code as opposed to non managed code.

If you platform you plan to create the .dll with is different then above assumptions - they you have to expand on your question.

Regards,

Albert D. Kallal (Access MVP)

Edmonton, Alberta Canada

***Personal information deleted by the moderator. Please see the Microsoft Community Frequently Asked Questions for more information on how you can protect your privacy.***