What is an Access library

Access Libraries

For Access developers a “library” is generally considered to be an Access container used to package reusable code.  Once a module is placed in a library, the library can be referenced and all of the public variables, constants, functions and in certain cases classes can be used from the application which references the library.

I don’t have any statistics but my gut feeling is that a small minority of developers use a custom built library of their own code.  This is unfortunate since a library is a way to package up all of your own code and make it available to any application you write.  Object oriented development emphasizes building reusable code, and furthermore placing all such reusable code in a single location so that if there are bugs or enhancements, they are fixed or created in one place.  The days of copying code from application to application are gone.

As a consultant, I have a standard agreement with my clients which says that “Any code that I write which is generic in nature belongs to me.”  In other words, I am paid for all of my development but if a function or class is something that is useful in a wide range of applications, I own that code even if the client paid me to develop it.

Why do I do that?  Simply because a ton of code is useful across the spectrum of applications.  Take the back color changing behavior of the control wrapper we have looked at.  That code is not specific to an insurance call center or a lumber mill database.  It is useful to any application I build.  So I take such behaviors and embed them in my code (my library) and then turn them on and off using SysVars.  I will discuss SysVars in a future blog.  I own such code and it is placed in my library.  The client app then references my library.

OK, so a library is an Access container which holds mostly code.  Such code can be accessed from outside of the library by setting up a reference to the library.  Microsoft specifies naming conventions for container extensions, for example the MDB is the application, MDE is a compiled container with the source stripped out, and the MDA is a library.  While that is their convention, you can change the extension of any Access container to anything you want and Access will automatically figure out what it really is.  For example I can call my library C2DbFW3G.MDA or I can call it C2DbFW3G.ASD.  Jet doesn’t care what it is.

The thing to remember is that Windows has means of mapping extensions to programs used to open the container.  So Windows is told by the Office install that an MDA, MDB, MDE etc containers belong to and are opened by Access.  However you can open Access and from inside Access you can open an Access container with literally any extension.  Oftentimes developers use this to confuse the user of their program and make them think that the container is not Access.  Literally it is confusing that Windows mapping setup and depending on the user to not attempt to open the container with Access.

I have often “broken into” application databases which are actually access containers so that I can view their tables and such simply by attempting to open the containers with Access.  There have been a couple of Diabetes reader databases “protected” in this manner.  Having said all of that, if you really do compile and create an MDE using Access to do so, the source is gone.  Renaming the MDE to MDA will not get the code back.

OK, so take the last database we created which we named clsCtlCboDemo.MDB, copy it and rename the copy to MyFrameworkLib.MDA.  Now double click on that container and notice that it pops right open.  Close it.  Rename it to the extension .XYZ and double click it again.  Notice that Windows doesn’t know what to do with it and asks what to open it with.  Click “Select program from list” and select Access to open it with.  Notice that it pops right open.  Close it and set the extension to .MDE.  Double click it and notice that it pops right open.  Open any module.  The source is there which confirms that simply calling it .MDE does not make it MDE.  Do not compile it to an MDE at this point.  Doing so will in fact compile it and strip the source code out and you could end up with an uneditable container (lose the source).

So much for extensions.

Rename the container to the extension MDA.  This container will be the basis for your future blog library.

At this point we need to discuss scope.  Most programmers understand scope but I will do a quick rundown.  In simple terms, scope means “where an object name is visible from”.  I will use the word variable but you can substitute constant, function, sub, property or class as well.

  • A variable declared in a function is only visible from inside of the function.
  • A variable declared private in the header of a module or class is visible everywhere inside of that module or class.
  • A variable declared public in the header of a module or class is visible everywhere in the container that the module or class exists in.
  • A variable declared public in a module or class is visible inside of any container referencing the library containing the module or class.
Objects with the same name cause name collisions.  When this happens Access ‘remembers’ the ‘closest’ name.  In other words, if there is a variable MyVar in the header of a module and inside of a function in that same module, then the variable MyVar inside of the function is used.
In MyFrameworkLib.MDA, insert a module.  Immediately save it as basScopeTest1.  Insert the following code in the module:
Option Compare Database
Option Explicit
Public MyVar As String

Function MyVarTest()
MyVar = “Hello”
End Function

In the debug window execute the function
MyVarTest
The word test will be written to the debug window.

Create a second module and immediately save it as basScopeTest2.  In that module insert the following code:

Option Compare Database
Option Explicit

Private MyVar As Integer

Function MyVarTest()
MyVar = 1
End Function

Try to compile.  Notice that you get no error.
In the debug window try to execute the function
MyVarTest
This time you will get a complaint about an ambiguous (function) name MyVarTest.  So the editor tries to execute the function and can’t figure out which function to call since they are both named the same thing.
In basScopeTest2 change the function to the name MyVarTest2
Function MyVarTest2()
MyVar = 1
End Function
In the debug window run MyVarTest2 and you should see the number 1 printed.
The point of this exercise is to allow you to see and test scope issues.  You can try setting the integer MyVar to public.  What happens?  Interestingly, the compiler does not complain.  Even though there are now two public variables with the same name, each function accesses the identically named variable ‘closest’ to it and manipulates it as it should, string inside of the one module, integer inside of the other.  But what happens when we try to access MyVar from outside of either of those modules?
Create a third module and immediately save it as basVarTest3.  In that insert the following code.
Option Compare Database
Option Explicit
Function MyVarTest3()
    Set MyVar = “test again”
End Function
Now try to compile.  Yes, the compiler complains about “ambiguous name detected” because now the third function does not have a “local” (to the module) variable MyVar and so must try to find one outside of the module.  It immediately discovers two MyVar variables and complains.
This discussion is important because scope can cause interesting problems and once we move to a library it can cause even more interesting problems.  The compiler searches for a name from “closest” to “furthest”.  If a name is found inside of the function, that is used.  Inside of a module containing the function, that is used.  Inside a container containing the module containing the function, that is used.  Once a name is not found inside of the container, then Access starts searching through the referenced libraries in the order referenced, in other words from the top down.  If the name is not found before it gets to your library and the name is found in your library, then that name (object) is used.
It is critical to understand this because we often times develop code inside of an application, then copy the code to the library.  We reference the library but forget to delete the code from the application.  What happens when we make changes to the code in the library but it still exists in the application?  We fail to see the changes that we made.  If you ever have “I made a change but it isn’t working” think scope problems.
On that note, delete the three modules that we just created.
OK, this is enough for one blog.  We defined what the term library means to us Access developers.  We showed that extensions don’t (necessarily) mean jack.  We created a library from our last database and used that to discuss scope.  In the next blog I will move on to making the form object wrapper visible from outside of the library.

Leave a Reply

Your email address will not be published. Required fields are marked *