Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Other Microsoft Products _ Making A Dll... Or Should I?

Posted by: nuclear_nick Jul 6 2019, 03:01 PM

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...

Posted by: Doug Steele Jul 6 2019, 07:50 PM

While it would doubtlessly be a great learning experience, I doubt there would be any noticeable improvement in performance, and potential issues associated with ensuring version compatibility if the DLL changes would seem to outweigh any possible advantages. YMMV.

What you described is easily accomplished using simply VBA. No need to add the complexity of an additional external component.

Posted by: GroverParkGeorge Jul 7 2019, 07:28 AM

I would urge you to engage in this project as a LEARNING experience if you want to try out one of the programming languages supported in Visual Studio. That way you can also see how the two compare (native VBA vs VB or C# or whatever you decide to learn.) Like Doug says, I don't think the results would be what anything like what you seem to expect. On the other hand, learning something new is always a valuable and exciting experience in itself. Keeping your expectations at a realistic level is a pretty good idea too, though.

Posted by: nuclear_nick Jul 7 2019, 01:25 PM

I will just use it to learn, then... keep it out of production.

I was thinking/hoping that it would improve query processing time when I use the VBA functions in a query. I'll test.

Is there another way besides a DLL? Just curious.

Oh, and I do thank you Doug and George, for the quick feedback. Awesome!

Posted by: GroverParkGeorge Jul 7 2019, 04:14 PM

Do let us know your test results.

Good luck with the rest of your project.

Posted by: AlbertKallal Jul 8 2019, 12:06 AM

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:

CODE
Imports System.Runtime.InteropServices

<ClassInterface(ClassInterfaceType.AutoDual)>
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

        Get
            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.

Eg:




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:

CODE
Output:
10
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).

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
kallal@msn.com

 AccessDemo.zip ( 22.96K ): 4
 

Posted by: nuclear_nick Jul 8 2019, 06:47 AM

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 )

Posted by: AlbertKallal Jul 8 2019, 01:08 PM

Excellent.

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
Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada


Posted by: nuclear_nick Jul 9 2019, 06:04 AM

I'd already figured on starting simple... or so I think...

I was planning on starting with a VBA module I wrote for (mostly) database use, simply converting a number of things about dates into fiscal dates. They are mostly used in reports (as functions in queries, and report parameters) that I have been using since 2003 and they've only been streamlined once since then, so it's been 16 years and they are still in use and haven't changed, so I thought it would be a good candidate... I know it works, I know how it works, I know how to test it, and no forms (like I keep seeing in examples) are needed.

I had the idea for I don't know how long that if I ever got VS that is what I would do. It's just taken this long to finally get VS.

Off I go, into the wild... yonder.

Posted by: DBQueen Jul 18 2019, 06:43 PM

Hi Nuclear_Nick,

I'd like to thank you for this thread post and the contributions of others especially AlbertKallal that it made me think to try VS.


Posted by: PhilS Jul 19 2019, 04:20 AM

QUOTE
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.

Just one thought: When I started learning .Net, I deliberately choose to use C# instead of VB.net. This forces you to learn things from the ground up instead of just replicating VBA approaches that might work, but are not the recommended way to do things in .Net. - On the other hand, this creates a much steeper learning curve and will slow you down tremendously at first. So, if you got actual work to complete, it might not be the best idea, but if your focus is rather on learning than on building stuff, it is worth to consider this.

Posted by: nuclear_nick Jul 19 2019, 05:59 AM

@DBQueen

NN or Nick is fine, if you need something shorter... smile.gif

I seem to come up with some doozies lately.

@PhilS

Thank you for that. I sort of thought of that... I found a few sites that will take VB and convert to C++, thought I could cheat that way, but I'm assuming eventually I'll need something besides VBA and SQL, as we progress from Access to SQL Server (BE) and whatever they want as FE's. Being 'self-taught' as I have been so far... it's going to be fun.

@ the rest of you...

Progress is slow. 40-hour/week job, wife, two kids... I'm hanging in there.