Distributing a file as a referenced library

[AlxGrim]

I just posted a video on my YouTube channel explaining how an Access files, containing subs, functions, code modules or even classes, can be distributed as if it was a code library. This file can be referenced by any other Access file (using the Tools.References window), so that it can use all the exposed functions. Nothing new, of course, but I found this interesting and I hope someone can find it interesting too. The video is here:

 

[isladogs]

Hi Alessandro
I've already watched the video and its indeed something that should be useful to other developers
Worth mentioning that although you were discussing its use for class modules, the method is equally useful for sharing code in standard modules.

Interesting that you were able to post a link to a YouTube video. Normally new members are unable to post links (an anti-spam measure)

 

[GPGeorge]

As I mentioned in a comment on the YouTube video, I've used this approach for years. In fact I have two now, one of which is linked into all of my personal use Access databases, and one which is used in databases where I need to handle JSON. Rather than import or copy code or class modules, I add a reference to the appropriate accdb.

The most important of the code modules is the standard error handler, which the same across more than half-a-dozen Access applications.

BTW: if I were distributing one of these in a client project, I'd convert it to an accde for obvious reasons.

 

[arnelgp]

my question is after linking the producer database, can you Implement
iTest class from within the current db? I think you can only Implement the iTest
class within the same db (codedb)?

 

[GPGeorge]

my question is after linking the producer database, can you Implement
iTest class from within the current db? I think you can only Implement the iTest
class within the same db (codedb)?

There is a method to do that. I believe Alessandro explained it in the video.

 

[AHeyne]

my question is after linking the producer database, can you Implement
iTest class from within the current db? I think you can only Implement the iTest
class within the same db (codedb)?

Usually you can't instantiate a class in a referenced Access lib database with the 'New' keyword.

But you can make your class in the referenced lib database a static class.
Then there always exists a static default instance of that class which you can access by its name and call a procedure like: "MyLibClass.MyLibProcedure"

You could also implement a kind of factory method in the static class, e.g. “Create()”, which returns a new instance of itself (preferably using an interface, but that's going too far here).

To make a class static you have to
- export it from out the VBE to a text file
- delete it in the VBE
- edit the file
- change "Attribute VB_PredeclaredId = False" to "Attribute VB_PredeclaredId = True"
- import and save it

Alternatively you can create a standard module in the lib having a public factory function which returns a new instance of your class (like shown in the video above).

BTW: I seem to remember in the back of my mind that someone recently mentioned somewhere that you can instantiate a lib class from the referring database using “New”, but I can't remember exactly. I have not tried it yet at all.

 

[arnelgp]

There is a method to do that. I believe Alessandro explained it in the video.

no, there was none. i think you can only Implement the class on a New Class on Same database.

 

[cheekybuddha]

See this almost 20 year old post on UA

 

[GPGeorge]

Usually you can't instantiate a class in a referenced Access lib database with the 'New' keyword.

But you can make your class in the referenced lib database a static class.
Then there always exists a static default instance of that class which you can access by its name and call a procedure like: "MyLibClass.MyLibProcedure"

You could also implement a kind of factory method in the static class, e.g. “Create()”, which returns a new instance of itself (preferably using an interface, but that's going too far here).

To make a class static you have to
- export it from out the VBE to a text file
- delete it in the VBE
- edit the file
- change "Attribute VB_PredeclaredId = False" to "Attribute VB_PredeclaredId = True"
- import and save it

Alternatively you can create a standard module in the lib having a public factory function which returns a new instance of your class (like shown in the video above).

BTW: I seem to remember in the back of my mind that someone recently mentioned somewhere that you can instantiate a lib class from the referring database using “New”, but I can't remember exactly. I have not tried it yet at all.

That sounds like what I was thinking. It's been too long since I set this up and details are hazy. Now you went and made me do research on my own code.

 

[GPGeorge]

That sounds like what I was thinking. It's been too long since I set this up and details are hazy. Now you went and made me do research on my own code.

See this almost 20 year old post on UA

That is probably where I first got the idea to try this approach. Thank you.

 

[MajP]

no, there was none. i think you can only Implement the class on a New Class on Same database.

Isn't that just an issue of setting it from private to PublicNotCreateable

By default a class module has the Instancing property set to Private. That means that only the project which contains the definition of the class can instantiate an object of that class. The only other choice that VBA supports is Public, not creatable. What that means is that an external project can use an object of this class, but it cannot instantiate it. Might seem strange, but that's the way it is. The way to change the property from the default value is to select the class module in the VBE Project Explorer, select the class module of interest, access the Properties Window (if necessary, use F4 to make it visible), and change the Instancing property to 2-PublicNotCreatable.

So you can use it, but still not create it AFAIK. So then you can build a function in a library that creates the instance.

 

[GPGeorge]

Here's an example of what I have in my code library.

In a Standard Module:

modAppConstants

Option Compare Database
Option Explicit

Public Function New_AppConstants() As Appconstants
    Set New_AppConstants = New Appconstants
End Function

In a Class Module:

Appconstants

Option Compare Database
Option Explicit

Public Property Get AppName() As String
    Static strAppName As String

    strAppName = DLookup("AppStringValue", TempVars("AppString"), "AppStringName ='AppName'")
    AppName = strAppName
    TempVars.Add Name:="AppName", Value:=strAppName

End Property

Etc. for other AppConstants

And in the client database, for example:

Public Function InitTempvars()

    Dim appC As Appconstants

    Set appC = New_AppConstants

'etc.

End Function

 

[Isaac]

As I mentioned in a comment on the YouTube video, I've used this approach for years. In fact I have two now, one of which is linked into all of my personal use Access databases, and one which is used in databases where I need to handle JSON. Rather than import or copy code or class modules, I add a reference to the appropriate accdb.


View attachment 120200

The most important of the code modules is the standard error handler, which the same across more than half-a-dozen Access applications.

BTW: if I were distributing one of these in a client project, I'd convert it to an accde for obvious reasons.

What kind of things does your standard error handler cover, just summary/generally speaking?

 

[jdraw]

FWIW,
I had an issue that was resolved in this thread by MarkK. Don't know the applicability to this thread, but thought I'd add the link---it may just help someone.

 

[MarkK]

my question is after linking the producer database, can you Implement
iTest class from within the current db? I think you can only Implement the iTest
class within the same db (codedb)?

If ITest class exists in Lib1, is marked PublicNotCreateable, and exposes...

Public Function TestMethod As String
End Property

Then in Project1, which holds a reference to Lib1, you can freely do...

Implements Lib1.ITest

Private Function ITest_TestMethod() As String
    ITest_TestMethod = "Hello World!!!"
End Function

So yes, you absolutely can implement a public class exposed by a referenced library file in your project file.

 

[GPGeorge]

What kind of things does your standard error handler cover, just summary/generally speaking?

Not a lot, really. I don't have to worry about things like error logging, or error reporting (although that is a feature I left in from client days).
Note that all of the arguments are optional because I might, just possibly, have an older accdb with a previous version that I don't want to break by requiring an argument that wasn't in a previous incarnation.

Public Function GlblErrMsg( _
    Optional ByVal iLn As Integer, _
    Optional ByVal sFrm As String, _
    Optional ByVal sCtl As String) As Boolean
    
Dim appc As Appconstants
Dim strErrMessage As String
Dim strSupport As String
Dim errNum As Long
Dim strErrDesc As String
Dim intLN As Long
    
    If Err.Number = AppErr.actioncancelled Then Exit Function
    errNum = Nz(Err.Number, 9999)
    strErrDesc = IIf(Len(Err.description & "") = 0, " Unidentified Error", Err.description)
    intLN = IIf(Len(Erl & "") = 0, 0, Erl)
    sCtl = IIf(Len(sCtl & "") = 0, " Unidentified Procedure", sCtl)
    sFrm = IIf(Len(sFrm & "") = 0, " Environment", sFrm)
    
    Set appc = New_AppConstants
    GlblErrMsg = False
    strSupport = Replace(appc.SupportEmail, ",", vbNewLine)
    strMBTitle = appc.MBErr
    lngMBBtn = appc.MBYNBtn
    
    strErrMessage = "Please report this error to Your Support Person: " & appc.SupportPerson & "." & strDoubleLine & _
        "The Error Number was: """ & errNum & """." & strSingleLine & _
        "The Error Description was: """ & Nz(strErrDesc, " No Description") & """." & strDoubleLine & _
        "The error occurred at Line Number: " & intLN & strSingleLine & _
        "In procedure: """ & sCtl & """." & strSingleLine & _
        "In module: """ & sFrm & """." & strDoubleLine & _
        "Please submit this error report " & _
        "and a brief description " & "of what you were doing when the error occurred." & strDoubleLine
    On Error Resume Next


    GlblErrMsg = True
                    
    If MsgBox(Prompt:=strErrMessage & vbCrLf & vbCrLf & "Send report?", buttons:=lngMBBtn, Title:=strMBTitle & " Send Report?") = vbYes Then
        DoCmd.SendObject objecttype:=acSendNoObject, To:=strSupport, Subject:="Error Occurred in  " & appc.AppName, messagetext:=strErrMessage, templatefile:=False
    Else
        If Nz(appc.booDebug) Then Stop
    End If
End Function

It's called:

errHandler:
   Call codearchive.GlblErrMsg( _
        sFrm:=Application.VBE.ActiveCodePane.CodeModule, _
        sCtl:="cboSelectCategoryID_AfterUpdate" _
              )
        Resume Cleanup
        Resume
End Sub

Dead simple, I know.

 

[Josef P.]

Note:

So you can use it, but still not create it AFAIK.

In earlier Access versions (I assume with VBA 6) you could export the class, set the attributes VB_Creatable = True and VB_Exposed = True and then import it again. Then the class could also be created from the Lib file with New.
I just tested this with VBA7 (64 bit) and it no longer worked.

 

[MarkK]

A tidy way to expose class instance constructor methods (and whatever else) from a Library is to group them in a Provider class, which is a sort of cross between a factory and service locator pattern. This reduces pollution of the global namespace with a proliferation of unrelated function calls like...

Public Function New_AppConstants() As Appconstants
    Set New_AppConstants = New Appconstants
End Function

... to return new class instances.

For example, I expose a global "UI Provider" instance something like this...

Private ui_ As cUIProvider

Property Get ui() As cUIProvider
    If ui_ Is Nothing Then Set ui_ = New cUIProvider
    Set ui = ui_
End Property

...so it is visible in the global namespace. Then, if I type "ui." in any file that references the Library, an intellisense window pops open showing me all the methods exposed by cUIProvider. The UI Provider, in turn, exposes methods that return class instances (or other handy methods) with parameterized (or not) function calls like...

Public Function Popup(Optional SelKey As String, Optional SelKeyList As Lib.cKeyList, Optional data As Object, Optional Form As Access.Form, Optional Tag As String) As cPopup
    Set Popup = New cPopup
    Popup.SelKey = SelKey
    Set Popup.SelKeyList = SelKeyList
    Set Popup.data = data
    Set Popup.Form = Form
    Popup.Tag = Tag
End Function

Public Function Progress(Optional max As Integer, Optional Caption As String = "Progress") As cProgress
    Set Progress = New cProgress
    If max > 0 Then Progress.Show max, Caption
End Function

Public Function PropertiesViewer(Caption As String, ListObject As Object) As cPropertiesViewer
    Set PropertiesViewer = New cPropertiesViewer
    PropertiesViewer.Initialize Caption, ListObject
End Function

Public Function SmartCombo(cb As Access.ComboBox, SQL As String) As cSmartCombo
    Set SmartCombo = New cSmartCombo
    SmartCombo.Load cb, SQL
End Function

Public Function Splitter(Bar As Access.Rectangle, ctrl0 As Access.Control, ctrl1 As Access.Control) As cSplitter
    Set Splitter = New cSplitter
    Splitter.Load Bar, ctrl0, ctrl1
End Function

'******************************************************** Apr 03 2025 *****
'
'   Modal MsgBox
'
'**************************************************************************
Function MsgBoxDel(msg As String) As Boolean
    MsgBoxDel = vbYes = MsgBox(msg, vbYesNo + vbExclamation + vbDefaultButton2, "Confirm Delete")
End Function

Function MsgBoxConfirm(msg As String, Optional ConfirmWhat As String = "Update") As Boolean
    MsgBoxConfirm = vbYes = MsgBox(msg, vbYesNo + vbQuestion + vbDefaultButton2, "Confirm " & ConfirmWhat)
End Function

The library file just exposes a series of providers, like...

'******************************************************** Jan 31 2025 *****
'
'   Provider
'
'**************************************************************************
Property Get Exc() As cExceptionProvider
    Set Exc = DI.Resolve("ExceptionProvider")
End Property

Property Get ds() As cDataStructureProvider
    Set ds = DI.Resolve("DataStructureProvider")
End Property

Property Get dt() As cDateTimeProvider
    Set dt = DI.Resolve("DateTimeProvider")
End Property

Property Get fs() As cFileSystemProvider
    Set fs = DI.Resolve("FIleSystemProvider")
End Property

Property Get st() As cStringProvider
    Set st = DI.Resolve("StringProvider")
End Property

Property Get ui() As cUIProvider
    Set ui = DI.Resolve("UIProvider")
End Property

Property Get vt() As cValueTypeProvider
    Set vt = DI.Resolve("ValueTypeProvider")
End Property

This approach has really helped tame my Library. It offers a really simple intellisense supported mechanism to clean up the global namespace, and provide focused access to a diverse set of methods.

 

[GPGeorge]

Spoiler:

Not a lot, really. I don't have to worry about things like error logging, or error reporting (although that is a feature I left in from client days).
Note that all of the arguments are optional because I might, just possibly, have an older accdb with a previous version that I don't want to break by requiring an argument that wasn't in a previous incarnation.

Spoiler:

[/QUOTE]
Public Function GlblErrMsg( _
[QUOTE="GPGeorge, post: 1965887, member: 22584"]
    Optional ByVal iLn As Integer, _
    Optional ByVal sFrm As String, _
    Optional ByVal sCtl As String) As Boolean
  
Dim appc As Appconstants
Dim strErrMessage As String
Dim strSupport As String
Dim errNum As Long
Dim strErrDesc As String
Dim intLN As Long
  
    If Err.Number = AppErr.actioncancelled Then Exit Function
    errNum = Nz(Err.Number, 9999)
    strErrDesc = IIf(Len(Err.description & "") = 0, " Unidentified Error", Err.description)
    intLN = IIf(Len(Erl & "") = 0, 0, Erl)
    sCtl = IIf(Len(sCtl & "") = 0, " Unidentified Procedure", sCtl)
    sFrm = IIf(Len(sFrm & "") = 0, " Environment", sFrm)
  
    Set appc = New_AppConstants
    GlblErrMsg = False
    strSupport = Replace(appc.SupportEmail, ",", vbNewLine)
    strMBTitle = appc.MBErr
    lngMBBtn = appc.MBYNBtn
  
    strErrMessage = "Please report this error to Your Support Person: " & appc.SupportPerson & "." & strDoubleLine & _
        "The Error Number was: """ & errNum & """." & strSingleLine & _
        "The Error Description was: """ & Nz(strErrDesc, " No Description") & """." & strDoubleLine & _
        "The error occurred at Line Number: " & intLN & strSingleLine & _
        "In procedure: """ & sCtl & """." & strSingleLine & _
        "In module: """ & sFrm & """." & strDoubleLine & _
        "Please submit this error report " & _
        "and a brief description " & "of what you were doing when the error occurred." & strDoubleLine
    On Error Resume Next


    GlblErrMsg = True
                  
    If MsgBox(Prompt:=strErrMessage & vbCrLf & vbCrLf & "Send report?", buttons:=lngMBBtn, Title:=strMBTitle & " Send Report?") = vbYes Then
        DoCmd.SendObject objecttype:=acSendNoObject, To:=strSupport, Subject:="Error Occurred in  " & appc.AppName, messagetext:=strErrMessage, templatefile:=False
    Else
        If Nz(appc.booDebug) Then Stop
    End If
End Function

It's called:

[/SPOILER]
errHandler:
   Call codearchive.GlblErrMsg( _
        sFrm:=Application.VBE.ActiveCodePane.CodeModule, _
        sCtl:="cboSelectCategoryID_AfterUpdate" _
              )
        Resume Cleanup
        Resume
End Sub

Dead simple, I know.

[/spoiler]
One other thing I just noticed here. In an early version, I was passing the names of a form and a control on the form as strings to the error handler. I had named the arguments sFrm and sCtl accordingly.

However, I later found a way to reference code modules instead. Also MZ-Tools made it easy to insert the name of the event, whether it was a control or form event or not.

That improved the error handler itself. A good thing. It made the named arguments inappropriate, strictly speaking. A slightly bad thing.

Rather than finding and replacing perhaps dozens or hundreds of argument names in multiple events in multiple databases to reflect those enhancements, I left the argument Names as is even though they are no longer referencing a form or a control. I can live with an inconsistency like that, because it's my code and I know why it is like that.

That illustrates one of the potential gotchas of the reference library approach.

 

[arnelgp]

I just tested this with VBA7 (64 bit) and it no longer worked.

That's what I get too, you can't Implement it on the Currentdb.