How to register library/reference from vba code?

[Albert D. Kallal]

I have used zip files that includes runtimes for installs, but only if the target has no Access installed.

Well, I do have some interop apps that have constants, so do you suggest hardcoding magic values as a workaround?

Yes, 100% correct in terms of hard coding such values.

And a simple example?
While I used the Office "FileDialog"?
It does require the Office 16 library?
You can over the years have used late binding, and thus that file dialog code would become:

' Original posted code:
Dim f    As FileDialog
Set f = Application.FileDialog(msoFileDialogFilePicker)
f.Show
MsgBox "file choose was " & f.SelectedItems(1)

'You can late bind if you wish:
'If you remove the reference to the 16.0 object library, then the following
' code will work without any references:

   Dim f    As Object
   Set f = Application.FileDialog(3)
   f.AllowMultiSelect = True
   f.Show

   MsgBox "file choosen = " & f.SelectedItems.Count

So, yes, those constants (like msoFileDialogFilePicker in this example), do require changes.

As noted, you can during development do a "regasm", and use early binding.

In fact Visual Studio has a option to do a regasm for you during the build.
(thus, this feature/option ONLY applies to your dev computer - it does NOT change the .dll one bit).
That option is this one in VS:

So, if you tick above, then .net will do a regasm for you. This VERY much suggests then that you MUST force the project to x86, or x64, and ANY CPU will not work. But, if you do check the above box, and build? Then you launch Access, and under VBA->tools->references?
You will indeed find the COM object.

So, the above can be handy during development, but that Register option as noted, is ONLY for development - it does not change the project or .dll in anyway.

So, often during development, we often do use early binding. And I suppose the REALLY cool question?
Is how to debug the .net code when it called from VBA? (and again this is really easy, and really cool).
If you wish, do ask, and I'll post how to do this trick - but, probably in the next day or so....


R
Albert

 

[Albert D. Kallal]

Yes, that FileDialog requires a office reference. To be fair, that is one reference that RARE breaks.

>>My code is clearly using early binding, so you're saying I cannot call the msoFilePicker if I use late binding because that office picker is dependent on a specific Office library version?

Actually, I'm saying you can!

And the reason is that at least ONE office library will be working and registered on your computer if you have office programs installed.

So, if you go late binding, you can make a safe bet such code will work!!!

So that library will exist if you have Access (even JUST the runtime installed).

But, with late binding, you don't care if it is 16, 13, or who knows what!!!

But, it will work with late binding.....

I thus suggest that you still risk a broken reference (with early binding).

As noted, the above example is not the best, since office on startup will fix and re-set that reference in near all cases.

The basic concept here?
Well, the less references you have, the less chance of something breaking and going wrong......

But, like so many things? I don't want to say this is a 0 or 1 type of issue (ONLY one way, or ONLY the other way)!

As developers, we should always have some flexibility in preaching such choices.

So, while I suggest less references = better?

Well, sure, but some references are better then others, and the FileDialog one (requires the Office 16, or Office xx library in tools->references)?

It almost for sure will exist anyway, and it rare breaks or goes wrong....

I as a general rule have used late binding for the FileDialog, and thus not setup/used the Office reference.
However, the filedailog example is one that I would not much worry about....

R
Albert

 

[Josef P.]

Note: late/early binding
If you create a tlb file and link it via the file path, you can use early binding without having to register the dll. This is useful for development.

 

[isladogs]

If you have the excellent Office add-in VBE_Extras, you can use 'special binding' to get all the benefits of early binding whilst using late binding i.e. you get the best of both worlds! For more details, see

Special binding

If you develop your VBA code using early binding but switch to late binding when deploying, then 'special binding' may be useful to you.

www.thevbahelp.com

 

[RonPaii]

When using Albert's loader, I wrap the external dll inside a VBA class. This allows the class to do lazy loading and handle broken references giving me intel-sense in VBA.

On a side note, I have always had an issue were the 1st call of LoadLibrary returning a LastDllError = 203 "The system could not find the environment option that was entered". Albert has never been able to duplicate it. The fix is simple, trap the error, if 203 retry LoadLibrary on the 1st attempt. All other calls to LoadLibray work on the 1st attempt.

    Private Declare PtrSafe Function LoadLibrary _
        Lib "kernel32" _
        Alias "LoadLibraryA" _
            (ByVal lpLibFileName As String) _
        As LongPtr

    Private Declare PtrSafe Function MyCreateObject _
        Lib "nloader64.dll" _
            (ByVal strDll As String, _
             ByVal strClass As String) As Object

 

[sonic8]

So, late binding is a REALLY nice approach

Does your .NetLoader also support connecting the even sinks?
If not, you should mention that one will lose the ability handle events with Late Binding. - With many objects/libraries this can be tolerated but with some it is a complete deal breaker.


I did a presentation on using .Net libraries, including User Controls, with Access/VBA at the AEK10 conference. The slides and script included with the downloadable files are in German but you probably can push it though an auto-translator.

 

[Josef P.]

You still have to register the underlying COM DLL on the target machine where the app is going to run. Windows needs to know how to locate and instantiate the COM component at runtime. The type library file only provides the interface definition, it doesn't replace the need for COM registration for runtime execution.

The type library is only for types (interface).
The .net COM DLL types do not need to be added to the registry if you load them with mscoree.CorRuntimeHost & co, for example.

Steps:
1. Reference to tlb => VBE can check the interface
2. Load com dll class instance via mscoree.CorRuntimeHost / mscorlib.AppDomain
3. Use COM instance

 

[Albert D. Kallal]

The type library is only for types (interface).
The .net COM DLL types do not need to be added to the registry if you load them with mscoree.CorRuntimeHost & co, for example.

Steps:
1. Reference to tlb => VBE can check the interface
2. Load com dll class instance via mscoree.CorRuntimeHost / mscorlib.AppDomain
3. Use COM instance

Well, if you add a reference to the runtime host, then that's another reference that can break.
So, with my .net loader, then zero additional references are required.
So, if the .net .dll is written to support regasm, (exposes a com interface) then it should work with my loader.
Only difference is that you just have to drop the .dll in the same folder as the Access front end - nothing more is required
(well, the .net loader module in Access is required).

R
Albert

 

[Albert D. Kallal]

Does your .NetLoader also support connecting the even sinks?

I not tested nor attempted to sink events into such .dll's. But, few require this ability.

So, for last 10+ years of building interfaces to accounting packages, PDF merging, and a whole lot more in .net?

Never needed or wanted to sink events into such external library code anyway.

However, while ActiveX, COM, oleDB are all actually the same underlying "COM" interface?

The ActiveX road is COM again, but it also includes a UI part - and right now, I would say that events, or using UI controls?

Probably would not work, since ActiveX controls need additional registry and setup - something not being done here....

I should also point out that the nloader.dll's I use?

They are .net code, but using "dll exports", they appear as a win32 standard .dll (unmanaged code) to applications with entry points.

I did this, since then I can build win 32 .dll's with .net (and they appear as standard un-managed .dlls - the first type I note above, and ones that never did require to be registered - but then again, those are not COM objects....).

So, I not really addressed the ActiveX issue as of yet (more R&D reqiured).

Of more need right now? I want to get COM+ working. This would allow interface from x32 code from x64, (or reverse) when building COM objects.

Hence, I would like to run Access x64, but use a COM interface to a x32 bit Accounting package.

And as my posted example shows? It's super easy to build a .net COM object - not even required to write interface code!

So, this example is a clean, dead simple, and allows one to use .net code from VBA - it's a "kiss" example.

In fact, all those .net examples with writing all that complex interface code etc.? Ones that go on for pages and pages?
One's that have/show interface code, but don't explain why you need as such? (and in 99% of cases you do NOT!!!).

Well, the Access community just passed on that stuff - too much work, too complicated for new users.....

R
Albert

 

[Papa_Bear1]

OK, first... references are found via Application.References, which you can look up with a browser. If you have Google with Gemini, you can ask for it to tell you "vba to manipulate ms access references" and you'll get a list. You can do AddFromFile or AddFromGUID to add a reference, and Remove to remove a reference. Look up those items to get some example code.

Another good search string is "Add references programatically with VBA"

So - I've been able to carry out a command like:

Application.References.AddFromFile strAccessDBPathFile

...where the strAccessDBPathFile is the full path to an external Access DB/file.

And was then able to establish which VBE.VBProject index this new referenced one is, and then pull data from that external DB. Cool.

But when I'm done, even after using

Application.References.Remove refProc

(Where refProc is the reference just added and processed etc.)

Then I still have the Access DB that I connected to, in my Project - viewer window.
So, it seems that ".Remove" isn't really "remove"? It is just "deselect" from the References?

When I hover over the project to remove in the Project viewer, the "Remove" option is grayed out as well.
Compiling did not remove it - but - as it happens, closing it out entirely and re-opening it did result in its disappearance.
I suppose that is OK - but - I'd rather be able to make that happen from code - especially if I intend to run this against numerous DBs etc.

I'm wondering - How do I programmatically remove the connected project from my local code/system/DB?

 

[The_Doc_Man]

So - I've been able to carry out a command like:

Application.References.AddFromFile strAccessDBPathFile

...where the strAccessDBPathFile is the full path to an external Access DB/file.

And was then able to establish which VBE.VBProject index this new referenced one is, and then pull data from that external DB. Cool.

But when I'm done, even after using

Application.References.Remove refProc

(Where refProc is the reference just added and processed etc.)

Then I still have the Access DB that I connected to, in my Project - viewer window.
So, it seems that ".Remove" isn't really "remove"? It is just "deselect" from the References?

When I hover over the project to remove in the Project viewer, the "Remove" option is grayed out as well.
Compiling did not remove it - but - as it happens, closing it out entirely and re-opening it did result in its disappearance.
I suppose that is OK - but - I'd rather be able to make that happen from code - especially if I intend to run this against numerous DBs etc.

I'm wondering - How do I programmatically remove the connected project from my local code/system/DB?

The problem is that as long as the file is still open (for ANY reason), it cannot be fully removed. But, as you point out quite correctly, when you close and re-open Access, the reference goes away. This is a common experience in Windows regarding file deletion, not just for Access, but for ANYTHING that uses a file. Something can be "marked for delete" but the deletion cannot be finished until ALL potential users are done with it.

In order to use that reference, Access has to open a file handle to the .DLL file, and that increments a "usage" counter. The act of adding a reference links Access to the .DLL file's internal "entry-point dispatcher" table, if I remember correctly. The .Remove operation marks the reference for deletion, but unfortunately, there is no way to know whether any module's code is still open that could still use the linked .DLL file. (Hint: Since general modules tend to stay open once opened, the module using the link probably IS still open.) So Access has to wait until it is ready to shut down to test and update its own external usage counts. And a reference to a library IS definitely an external usage.

Here's what happens if you actually DO remove the reference incorrectly. The instruction that activates whatever subroutine is involved will try to call the registered entry point - but you just removed that module. This will lead to an error caused by Access crashing due to a fatal memory management fault. The error code would look like 0xCxxx0005, where xxx is a hexadecimal string specific to the DLL you used or specific to the order in which you installed that library or to the order in which you installed Access. The "0xC" part says "fatal error" and "0005" is a memory fault.

 

[Papa_Bear1]

The problem is that as long as the file is still open (for ANY reason), it cannot be fully removed. But, as you point out quite correctly, when you close and re-open Access, the reference goes away. This is a common experience in Windows regarding file deletion, not just for Access, but for ANYTHING that uses a file. Something can be "marked for delete" but the deletion cannot be finished until ALL potential users are done with it.

In order to use that reference, Access has to open a file handle to the .DLL file, and that increments a "usage" counter. The act of adding a reference links Access to the .DLL file's internal "entry-point dispatcher" table, if I remember correctly. The .Remove operation marks the reference for deletion, but unfortunately, there is no way to know whether any module's code is still open that could still use the linked .DLL file. (Hint: Since general modules tend to stay open once opened, the module using the link probably IS still open.) So Access has to wait until it is ready to shut down to test and update its own external usage counts. And a reference to a library IS definitely an external usage.

Here's what happens if you actually DO remove the reference incorrectly. The instruction that activates whatever subroutine is involved will try to call the registered entry point - but you just removed that module. This will lead to an error caused by Access crashing due to a fatal memory management fault. The error code would look like 0xCxxx0005, where xxx is a hexadecimal string specific to the DLL you used or specific to the order in which you installed that library or to the order in which you installed Access. The "0xC" part says "fatal error" and "0005" is a memory fault.

OK --- Cool.
As usual - your answer is spot on!

You've got me a bit nervous about the fatal crash thing - especially since the process of connecting that new reference apparently always triggers an error that we must skip. Kind of scary. If you can elaborate on what would constitute removing the reference incorrectly - that would give me some assurance that I won't do that inadvertently.

Thanks again!

 

[The_Doc_Man]

OK --- Cool.
As usual - your answer is spot on!

You've got me a bit nervous about the fatal crash thing - especially since the process of connecting that new reference apparently always triggers an error that we must skip. Kind of scary. If you can elaborate on what would constitute removing the reference incorrectly - that would give me some assurance that I won't do that inadvertently.

Thanks again!

No, actually you CAN'T remove the reference until Access closes because the file handle (a behind-the-scenes structure that is a pipeline into a file's data) is set when you load the reference. It cannot be unset until Access runs its shut-down code because there is no mechanism (or at least, no easy one) to close a reference file handle and also there is no easy mechanism for an Access app to directly fiddle around with its memory management.

When you declare a .DLL file, that is reference to a "dynamic link library." Addressing schemes exist on Intel processor chips that allow you to make memory references that are relative to an address rather than to a fixed (absolute) address.

For statically-linked as well as dynamically-linked .DLL files, when that file becomes linked to your Access app, Windows allocates a previously unused chunk of VIRTUAL memory, big enough to encompass the .DLL file. It also allocates space in the system dynamic memory file and some memory pages are taken (borrowed) from the system's Unused memory pool.

Using relative-mode addressing and virtual memory mapping, that .DLL becomes part of what is called your "working set" - the memory that is working for you. Part of the .DLL file is the dispatch table for "methods" (routines) and data sections. This part gets messy fast, but the idea is that the Windows Link Loader handles address diddling. The point of this ramble is that you can't easily call the Windows APIs that would release that code since you didn't allocate it - Windows did, on your behalf.

So... the thing that would cause the fault I mentioned would be if you could call a memory management API call to unmap a section of memory that contained code that could be called from a different (and as of yet, still mapped) section of memory. Because the REAL meaning of the error is "you tried to reference an address that is not now and cannot reasonably soon be mapped virtually." If you can't unmap the memory, you can't easily do anything that would trigger the error.

However, sometimes, external entry point declarations can lead to this problem due to a poorly designed call interface that would allow you to pass an object variable (which is actually an address pointer to the object structure) and somehow pass an object that is "Nothing" (the object equivalent of "Null".) We've seen a few of those in the forum and it always seems to have involved trying to do something obscure with a ByRef argument to a 3rd party routine that might be just a little bit buggy.