Using Database Library Files in Your Access Application

By Andrew Wrigley (based in Argentina and the UK), upgraded for Access 2007 by Garry Robinson

Intro

You split your apps into Front-Ends and Back Ends, but time and again you’ll find yourself importing generic code modules, forms, reports, queries and tables from other applications. If you think ‘there must be a better way’, then read on.

I will show how to use Access .mdb / .mde files as Libraries that you can reuse with any of your Front-Ends. This allows you to split your Development efforts into application specific functionality and reusable utilities.

You are also able to sell such Libraries or purchase one from a third party. We have various Libraries that we will launch soon (for more details go to http://www.wingspan.info/Products), to provide sophisticated ‘out of the box’ utilities and functionality to Access Developers.
 

  << Click image to expand
Fig 1: Setting a reference to a Library File

But you don’t need to go that far to find uses for Libraries: you can use them to protect your Intellectual Property, as you can pack all your hard work (at least, the bits your client hasn’t paid for) into a Library that you deploy to your clients as an .mde file. This works even if your Client’s Front-End file is an .mdb.

So Libraries are an all round winner and 90% of what they can do for you is really very easy to accomplish. Going the extra mile will always be more taxing, but this article will make it a breeze and give you extra insights to boot.
 

Using .ACCDB/.ACCDE or MDB/MDE Library Files ~ Running the Sample

When you can download the samples for this article, you will find that it contains two apps, SimpleSample and DaisyChainSample. Both do exactly the same thing and for the purposes of this article, use SimpleSample. If you want to try some of the techniques mentioned in the Appendix, only then look at DaisyChainSample. For more details on the Samples, refer to ReadMe.rtf included in the download.

SimpleSample involves a traditional Front-End and Back End plus one Library file (SimpleLibrary.mdb), located in the Resources\Libraries subfolder.


Fig 2: The Sample folder structure

When you open the Front-End file (FrontEnd.accdb) or (FrontEnd.mdb), a Splash form will display (See Figure 3).

 
Fig 3: Splash Form opened from a Library File

Now try and find the Splash form in FrontEnd.accdb. It’s not there! But take a peek inside SimpleLibrary.accdb (Figure 4) and there it is, proof that FrontEnd.accdb is opening a form that is stored in a Library file.

 
Fig 4: Showing that the Splash Form is a Library Form!

The Consequences of Using Physically Distinct Files: CurrentProject and CodeProject

Most of the time, you develop a Library exactly as you would a Front-End. However, your Library code is now in a physically distinct file and this has a few consequences you need to be aware of.

As the Library is a separate file, it has a distinct VBA Project. You can see this by looking in the Project Explorer of the VBA Editor (see Fig 5).


Fig 5: Your Library Files have distinct VBA Projects

To refer to these projects in code you use either CurrentProject or CodeProject. For example:

strConn = CurrentProject.Connection

But how do you know which one to use? It turns out the answer is easy:

CurrentProject will ALWAYS refer to the file you opened in Access (eg, your Front-End), whereas CodeProject refers to the Project (file) where the code is written. So CurrentProject always refers to the same project, whereas CodeProject doesn’t.

To illustrate this, look at the following lines of code:

strConn1 = CurrentProject.Connection 
strConn2 = CodeProject.Connection

If strConn1 = strConn2 Then 
  boolSameFile = True
Else
  boolSameFile = False
End If

If this code is written in a Front-End module, then CurrentProject and CodeProject are one and the same and so boolSameFile is True. However, the same code written in a Library module will return boolSameFile as False: strConn1 will still give you the connection string to the Front-End, but strConn2 will now be set to the connection string for the Library file.

You can see this happening in the Immediate Window. Click on the Front-End Project in the Project Explorer and try:

? CodeProject.AllForms.Count

This will give you the number of forms you have in the Front-End. Now click on a Library Project. Running the same line in the Immediate Window will now give you the number of forms in the Library file.

Similar reasoning applies to CurrentData and CodeData, (or CurrentDB and CodeDB), that give you a reference to the database in each file (as opposed to the VBA Project of each file).

Accessing the Objects and Code in your Library Files ~ Public Constants, Variables, Subs and Functions

We can now look at how to use your libraries.

This is the easiest way to use a library, as a repository of Public methods, variables and constants. The good news is that this is dead easy:

Constants, Variables, Subs and Functions, that are scoped as Public and stored in a Library Code Module, can be used as if they were in the Front-End itself.

It is really easy and straightforward! For example, say you have a Public Function in a Library called MyLibraryFunction. In your Front-End you can write:

Dim strSomeData as String
strSomeData = MyLibraryFunction()

Now you could write:

strLibraryData = strSomeData

where strLibraryData is defined in your Library like so

Public strLibraryData As String

That’s the good news. "The really good news is that there isn’t any bad news! It just works."

For other objects, such as Library Forms, Reports and Classes, it gets just a little more complex but the following sections should provide an easy reference and guide.

Opening Access Objects in a Library File

If from your Front-End you want to open the Splash form, that is stored in the GenericLibrary.mdb file, the following code will fail, with Access complaining that it cannot find the Splash form:

DoCmd.OpenForm “Splash”

So how does the Front-End ever get to open a Form in a Library? The workaround is simple: in the Library file, you define the following Public wrapper method:

Public Sub OpenFormInSimpleLibrary( _
 FormName As Variant, _
 Optional View As AcFormView, _
 Optional FilterName As Variant, _
 Optional WhereCondition As Variant, _
 Optional DataMode As AcFormOpenDataMode, _
 Optional WindowMode As AcWindowMode, _
Optional OpenArgs As Variant)

  DoCmd.OpenForm FormName, View, FilterName, _ 
   WhereCondition, DataMode, WindowMode, OpenArgs

End Sub

In my Front-End code I can now write:

OpenFormInSimpleLibrary “Splash”

This Public Sub can be ‘seen’ from the Front-End, so you can call it OK, but why does the wrapped call to DoCmd.OpenForm now work when before it failed?

In essence, you can think that DoCmd.OpenForm requires the form to be opened to be part of either the CodeProject.AllForms collection or the CurrentProject.AllForms collection. As discussed above, in the Front-End, these collections are indistinct, but in a Library File, CurrentProject refers to the Front-End, whereas CodeProject refers to the Library. Note that this implies that from the Library project you can open a Form in the Front-End, which can be quite useful! Although the Library, to be generic, should have no knowledge of the Front-End, you can pass the form name to a Library method as a string parameter.

You need a similar Sub for closing objects, that wraps the DoCmd.Close method (see the Sample App).

What if a Library needs to use a Class Instance defined in the Front-End?

For example, I use a Class Module called classContext to deal with the retrieval of context information, such as User Preferences. Different apps need different contexts, so this class has to be defined in the Front-End.

As a result, the Libraries have no knowledge of this classContext class (or of its instance, that I call the Context object). However, the Libraries often need to use the Context object’s methods and properties. For example, if a Library form needs to display context information:

lblUserName.Caption = Context.UserName

However, this will cause problems if the Context variable is declared in the Front-End, as the Library cannot ‘see’ it.

The solution is easy. In the Library file, define the following Public variable:

Public Context as Object

This Library variable has Public scope so it can be ‘seen’ from the Front-End where you can ‘late bind’ it to the classContext type:

‘defined in the Front-End:
Public Sub StartContext()
  Set Context = New classContext
End Sub

Just make sure that you call StartContext before you try to use the Context object!

Using Class Instances defined in a Library (Link Table Manager Sample)

There are two possible scenarios:

1. A Single Instance Of A Type That Is Defined In The Library File (Singleton)

If your Front-End needs to instantiate ONE and ONLY ONE object from a given Library Class Module, you declare the variable in the Library:

Public LinkManager as classLinkManager

You then just need to provide a simple Sub in the Library to instantiate the one and only LinkManager:

Public Sub SetLinkManager()
  Set LinkManager = New classLinkManager
End Sub

The Library ‘knows’ what the LinkManagerClass type is (the corresponding class module is stored inside it), whilst your Front-End can ‘see’ both the SetLinkManager sub and the LinkManager, variable because it is declared as Public, so you do not need Late Binding.

All the code in your Front-End has to do is call SetLinkManager and then use the LinkManager object at will. For example:

SetLinkManager()
LinkManager.CheckLinks()

Fig 6: The Link Manager in Action

Note that this way you are limited to precisely one LinkManager object (in OOP speak, this is called a Singleton object).

2. Arbitrary Number Of Instances Of A Class That Is Defined In A Library

In this case, you will need to use the late binding technique we saw earlier. For example, in your Library define the following Sub:

Public Sub NewUsefulObject() As UsefulClass
  Set NewUsefulObject = New UsefulClass
End Sub

This public sub is visible from your Front-End code, so you can now declare a variable of type object, and then late bind it to the UsefulObjectClass type:

Set YetAnotherUsefulObject = NewUsefulObject

You can now use this variable in your Front-End code with all the methods and properties of the late bound class.

The downside is that you won’t get Intellisense due to the late binding.
 

Debugging and Editing Code in a Library File

You can debug code that is in a Library .mdb file from your Front-End, but you CANNOT edit code in a Library from your Front-End.

BEWARE: You can make changes, compile them and test the result, but any changes you make will NOT be stored once you close the file.

To edit your Libraries, you MUST open the Library file itself.

Accessing Data in Library Tables

One possibility is to link the Library Tables to your Front-End. However, this involves binding your Front-End to a specific location of the Library file and it reveals your Library tables in the Front-End’s database window (not always a good thing!). It also involves an extra step that is not, strictly speaking, necessary.

The following techniques will cover most of your requirements.

Library Queries

In your Libraries you can create Queries that use the Library Tables.

To display a DataSheet view of these queries from your Front-End, simply use the OpenQueryInSimpleLibrary style wrapper that we discussed earlier.
SQL Strings and the IN Clause

The best way to access data in your Library file tables is to use the IN sql clause. Eg:

SELECT MyLibraryField FROM MyLibraryTable
IN 'C:\SampleAppFolder\Resources\Libraries\SimpleLibrary.mdb';

See Garry's Article on remote queries

This code requires your Library to be installed in a specific location. To do this in a generic way, you can write a wrapper function in your Libraries such as:

Public Function SqlInSimpleLibrary () As String
  SqlInSimpleLibrary = "IN """ & CodeProject.FullName & """"
End Function

(Note that we have used CodeProject to get the Library file path, not CurrentProject!)

You can then use this Function to build your sql strings as follows:

strSql = “SELECT * FROM MyLibraryTable ” & _
SqlInSimpleLibrary() & “;”

You can now bind your Library data to Front-End forms, create recordsets, etc. Eg:

Forms(“MyFrontEndForm”).Recordset = strSql
 

Subforms? Sadly, no

You cannot use a Library form as a subform of a Front-End form.

Access requires subforms to be part of the same Project’s corresponding AllForms collection so your Library forms, which are NOT part of the Front-End’s AllForms collection, are not eligible. As only Access can add form objects to the AllForms collection, there is nothing you can do about this.
 

Deploying Library Files to your Client’s PCs

One of the great features of Access is that you put so much into just one file, which makes it really easy to just deliver your work onto your Client’s machines.

When using Libraries, you have broken out of this comfort zone and your Front-Ends now store references to precise locations where the Libraries are located. Before your heart sinks, let me say, yet again, that the solution is again quite simple. We will look at two separate case:

1. THE Front-End IS AN ACCDB/.MDB FILE:
An .mdb file's references are 'self healing'. Ie, if the User’s folder structure is different to your development machine, Access will initially look for the Libraries in the absolute path you set in Tools|References dialog of the VBA Editor. If Access does not find the Library file there, it will look for them under the same relative path from the Front-End file. If it finds them, it will reset the references, otherwise it fails and you get an error.

2. THE Front-End IS A ACCDE/.MDE FILE:
In an .mde file, you cannot change the references, so you have to plan carefully how you will deploy your Libraries. You have a variety of strategies that you can use, but all of them require a specific location for the Libraries. For example, most PCs have a C Drive, so you can create a folder on your Development machine, for example C:\AccessLibraries\MyApp, and install the Libraries for MyApp there.

At all times you have to remember that, if your Libraries have tables that your Front-End will write to, for example, UsersColorsTbl in the Sample, you must install them to a location where your User has Write Permissions to. Please note that quite often Users are not give write permissions to the C:\Program Files\ folder, so it is not a good idea to install your libraries there.

Whatever you decide to do, the rule of thumb is: Using Libraries is simple, so keep it simple!

Conclusion

I have shown a bullet proof way of using Access .accdb/.mdb and .accde/.mde files as reusable Libraries of generic functionality.

Using these Libraries is relatively simple if you are a good Access developer: just set the necessary references and then, at most, write a few standardised wrapper methods and away you go, taking care to use CodeProject where appropriate.

In a future article, we will discuss advanced scenarios and architectures that can open up a whole new world. For example, Swift SQL, an Access SQL editing and versioning tool that I have designed, is really just a Library file on steroids, with a bit of Com Add-In technology thrown in. For most cases, however, this article is more than enough to get you started.

Health Warning 1: Fixing Library bugs is tedious, so try to test your code thoroughly before moving it to a Library file!

Health Warning 2: Libraries are extremely useful when used for the right reasons, but they add a layer of complexity that you should avoid in many other scenarios!

So start simple and only add your tried and tested and very generic stuff where necessary.

About the Author

Andrew Wrigley develops Access, SQL Server and ASP.NET apps for medium sized clients around the World.

http://www.wingspan.info

  Your simple library Sample Database Is Called  "LibrarySampleSimple.zip"

Your Access 2007 Database Is Called  "LibrarySimpleSample2007.zip"

 

If you do NOT own "The Toolbox", Click here to find out how to purchase The Toolbox.

 

Other Pages at VB123.com That You May Want To Visit

Remote Queries In Microsoft Access
Taking Outlook and XML to Task in MS Access
Duplicate Data Entry For Access
Form Based Selection Criteria For Queries using Combo Boxes

Further External Reading:

Tony Toews (Access MVP) website: http://www.granite.ab.ca/access/addins.htm

VBA References http://support.microsoft.com:80/kb/824255/

Click on the button for the next help page in this Access Loop.