X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> Making A Dll... Or Should I?    
post Jul 6 2019, 03:01 PM

Posts: 1,872
Joined: 5-February 06
From: Ohio, USA

Background... saw Visual Studio was available for my work computer (through IT app). Didn't install, kept hanging. A few years later, company switches to O365, and I end up with a new computer, and IT puts VS on it.

So finally, I can play/learn VS.

I thought, as a first project to learn with, I'd take a VBA module and convert it into a DLL, see if it would run faster that way. The module hasn't changed and has been running fine since 2003, so I thought it would be a good candidate. It's simply a date "converter"... for example, give it a year and it will return the first date in the fiscal year. Give it a date, and it will return the fiscal year the date occurs in... there are a few other "conversions", but I'm certain you get the point.

So... my question is... should I? Would it really make it faster? I know I would learn something, but is it the right thing to learn? What are the pitfalls of creating a DLL like this, besides registration and distribution to the users of the database(s)?

Looking for good feedback...

"Nuclear" Nick
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
Start new topic
post Jul 8 2019, 12:06 AM

UtterAccess VIP
Posts: 3,101
Joined: 12-April 07
From: Edmonton, Alberta Canada

As noted, this is a GREAT skill to learn. I am constantly writing some bits and parts in .net and consuming the resulting .dll in Access.

In most cases, I use .net for several things are that far batter done.

Remember, Albert’s rule of software:

Be lazy, write the least amount of code!!

Interesting enough, there are 4 VERY different ways and approaches to creating and consuming .net code as a .dll from Visual Studio.

Most common:
You create what is called a “COM” object. This used to be called ActiveX, but it all the same technology.

Next up:
Create a standard windows x32 or x64 bit .dll. This is NOT a COM object. Such .dll’s don’t have to be installed, or registered on the target computer. You use these .dll’s JUST LIKE when you call the windows API. This is reffered to un-managed code (but, you can actually write and use managed code – this requires a VERY cool trick). It also means you can use say vb.net as opposed to learning c++

Next up:
Use and install the VB6 interop UserControls for VS.

Remember there are TWO types of Activex controls.

There are the ones that you create a “instance” in VBA like this:

MyExcel = CreateObject("Excel.Application")

In above, we use code to create the instance of the object.

But, you can also open a form in design mode, and drop in the ActiveX control right into the form. Some often do this say to display a PDF document.

So, access supports TWO types of COM objects.

The above CreateObject()

This is the NUMBER ONE approach you will use in .net.
(or you set a reference to the library in VBA tools->references).

However, Visual Studio does provide some tools and a “wrapper” to allow you to create a ActiveX control that will appear in the Ribbon (form design mode) were you can insert the ActiveX component right into a form. (this library is deprecated (since 2010, or 2013), but you can copy + use these setup in later versions of Visual Studio. This road opens up the possible to add the .net treeview control, and one that will work for access 32, or access x64.

So SOME TYPES of these objects ALSO allow you to insert + drop as a UI right into a form.

Third way:
Side load .net .dll’s. this method I really like, since then you don’t have to register the COM object.

So, be it a COM object, or a ActiveX COM object (that you can drop into forms), in BOTH of these cases, the big downside is that you have to register the .dll on the target computer. This can be a pain – especially today in that SO VERY MANY computers in a corporate environments are now locked down.

However, lets create a standard .net com object as a .dll.

As for speed? Well, VBA code now runs VERY, but VERY fast. The reason of course is the stunning amount of processing that we have available today. Most of the bottle necks are drive speed, or network speed, and not the speed of the CPU.

And often, the setup time and calling the external code does cost you some time.

.net code is compiled down to native assembler code and does run as raw native code. So for some types of processing loops etc., then you can gain substantial increases in performance. And native libraries to work with say XML or especially JSON data can see HUGE speed ups by adopting the .net libraries.

However, for your case? Not going to make much if ANY difference here.

I am pressed for time, and I will post a standard un-managed .dll example in vb.net (this can be a ASTOUNDING help if you have to write some un-managed code, but want to use a VBA like code (vb.net), and write the library as managed. (I done this several times – a really cool trick that beyond saved my bacon).

However, let’s just do the most plane jane and recommend approach.

We will create a standard .net com object.

Let’s just toss in a few function functions.

We will have

HelloWorld (display hello)

Times2 (multiply a given number by two).

So, in Visual Studio, we create a vb.net class.

Set the project to x86, and check the box register for com-inter op.

That’s it!!!

The vb.net code is thus this:

Imports System.Runtime.InteropServices

Public Class Test1

    Private m_MyValue As String

    Public Sub Hello()
        MsgBox("hello from .net")
    End Sub

    Public Function Times2(v As Integer) As Integer

        Times2 = v * 2

    End Function

    Public Property MyValue As String

            Return m_MyValue
        End Get
        Set(value As String)
            m_MyValue = value
        End Set
    End Property

End Class

So the above code is NEAR identical to using and writing class modules in VBA (and I do this a lot these days).

Now build the above.

Now, in access create a standard VBA code module (or even in form behind a button).

We can set a reference in VBA via tools->references.


And now how even in the VBA editor, we get intel-sense like this:

Or this:

The above shows the VBA code I typed, and the output is of course this:

this is my value as property

So, the above creates a .dll.

I have attached the sample Access (2010) example along with the .dll

Unzip to a folder.

But, you MUST first run the batch file to register the .dll.

And on most machines today, that means you have to right click, and choose run as administrator. You REALLY want to do this before you launch + open the access sample.

So, the above is the standard way to create a .dll.

However, creating win32 .dll that does not require registration can often be a choice, since then you don’t have to register on locked down computers, but only place the .dll on the target machine. As noted there are HUGE other reasons for this approach. (do ask if you are interested).

And then, last but not least, there is a way to “side” load .net .dll’s, and that can eliminate the requirement to register.

Edit: So, just unzip the Access sample with the .dll, and the bat file. Run the bat file to register the .dll (right click run-as admin)

And then open up the Access sample, open the code module, and in debug window type in the the name of the first sub to run, or just place your cursor in the code editor and hit f5 (or better yet, f8 to single step).

Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
Attached File(s)
Attached File  AccessDemo.zip ( 22.96K )Number of downloads: 8
Go to the top of the page
post Jul 8 2019, 06:47 AM

Posts: 1,872
Joined: 5-February 06
From: Ohio, USA

Thank you, Albert. Certainly a lot to digest, so I just wanted to say thank you for now, and I'll let you know how things turn out. It may be a few weeks, at least. (I do have a day job... smile.gif )

"Nuclear" Nick
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
post Jul 8 2019, 01:08 PM

UtterAccess VIP
Posts: 3,101
Joined: 12-April 07
From: Edmonton, Alberta Canada


Just keep i mind:

The VB.net code is easy to read, write and is much like VBA.

Building a class module in VBA/Access and vb.net are VERY similar - same coding approach.

Start small and simple like the above.
Ignore the examples on the web that go on for 20 pages and suggest you have to build a custom interface (you don't).
Ignore the examples on the web that talk about having to setup a GUID. (you don't).

good luck
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

Go to the top of the page

Posts in this topic

Custom Search

RSSSearch   Top   Lo-Fi    4th July 2020 - 01:48 AM