UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Making A Dll... Or Should I?    
 
   
nuclear_nick
post Jul 6 2019, 03:01 PM
Post#1



Posts: 1,779
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
 
Doug Steele
post Jul 6 2019, 07:50 PM
Post#2


UtterAccess VIP
Posts: 22,191
Joined: 8-January 07
From: St. Catharines, ON (Canada)


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.

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
GroverParkGeorge
post Jul 7 2019, 07:28 AM
Post#3


UA Admin
Posts: 35,681
Joined: 20-June 02
From: Newcastle, WA


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.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
nuclear_nick
post Jul 7 2019, 01:25 PM
Post#4



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


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!

--------------------
"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
 
GroverParkGeorge
post Jul 7 2019, 04:14 PM
Post#5


UA Admin
Posts: 35,681
Joined: 20-June 02
From: Newcastle, WA


Do let us know your test results.

Good luck with the rest of your project.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
AlbertKallal
post Jul 8 2019, 12:06 AM
Post#6


UtterAccess VIP
Posts: 2,844
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:

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
Attached File(s)
Attached File  AccessDemo.zip ( 22.96K )Number of downloads: 3
 
Go to the top of the page
 
nuclear_nick
post Jul 8 2019, 06:47 AM
Post#7



Posts: 1,779
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
 
AlbertKallal
post Jul 8 2019, 01:08 PM
Post#8


UtterAccess VIP
Posts: 2,844
Joined: 12-April 07
From: Edmonton, Alberta Canada


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

Go to the top of the page
 
nuclear_nick
post Jul 9 2019, 06:04 AM
Post#9



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


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.

--------------------
"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
 
DBQueen
post Jul 18 2019, 06:43 PM
Post#10



Posts: 17
Joined: 13-July 17



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.

Go to the top of the page
 
PhilS
post Jul 19 2019, 04:20 AM
Post#11



Posts: 617
Joined: 26-May 15
From: The middle of Germany


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.
This post has been edited by PhilS: Jul 19 2019, 04:20 AM

--------------------
Go to the top of the page
 
nuclear_nick
post Jul 19 2019, 05:59 AM
Post#12



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


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

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


Custom Search


RSSSearch   Top   Lo-Fi    20th September 2019 - 06:54 AM