Here’s a hidden gem for any serious developer that doesn’t seem to get enough of a spotlight.
… and no this is not an article about creating a library database to manage you collection of books, CDs, DVDs, …
What is a Library Database?
A Library database, also sometimes referred to as a Code Library, Code Db or Reference Library, is a standard Access database (which can be compiled to secure it from prying eyes) that enables one to create an easily deployable, reusable, maintainable library of objects and code to implement within any other database.
Huh? What does that mean in plain English?
Simply put, when you start a new project, typically, we go around scavenging code modules, forms (dialogues, date picker, progress bar, …) and the likes from different sources to import them into you new project to give us our basis to start building our application?
This is where a Library database can come in.
With a Library database, you place your common code components (Date/Time, Validations, APIs, …) and other objects (Date picker, Progress bar, …) and then simply reference it within your main database so you can use them seamlessly.
Why Use Them? What’s the advantage?
- For one thing, it gives you a single source to update that you can then push out to all your projects instead of having to modify each database individually.
- It permit you to leave the main database unlocked to other developer/clients, while permitting you to secure you library of code blocks and other database objects. This means you can guarantee no one can mess around/alter you key code components, but they can use them without issue.
- If you deploy a compile (mde/accde) Library database, which I recommend, the code always remains compiled and as such optimizes performance.
- By splitting common code into a Library database, you also minimize the file size of your actual application database.
What goes in a Library Database?
As mentioned previous, a Library database is where you place all your common, reusable code. Things like:
- Date/time procedures
- Common APIs
- Network Username
- Computer Name
- …
- Validations Procedures
- Phone Number
- …
- General Object Code
- TableExist
- CloseAllForms
- RedefineQuerySQL
- …
- File/Folder code
- Security Routines
- Error Handler
- Etc.
This is also where you place other common objects (typically forms in my experience). Things such as:
- Tools/Dialogs
- Login form
- Etc.
- Calendar/Date Picker
- Progress bar
- Error Reporting
- Etc.
But you don’t want to place any application specific objects! Only the common, non-database project specific stuff.
Where to place a Library Database?
There are two options for the location of the Library database.
- You can place your Library database within the same folder as your database (FE). Access will automatically relink the Library database at runtime even if the FE is moved as long as the Library is in the same folder. This implies you need to copy the Library database when the db is deployed.
- Another alternative is to place the Library database in a central location accessible to all and link to it there. One option would be to place it in the same folder (or subfolder) as the BE.
I personally privilege the first option simply because I like to eliminate any potential Network issues. By placing the Library database in the same folder as the FE (which mean locally on the user’s PC) we completely eliminate the Network from that part of the equation for the database. Depending on the Network, it can have a performance impact.
Adding/Removing Library Databases Dynamically via VBA Code
It is also possible to add and/or remove such references dynamically via VBA code, such as:
'---------------------------------------------------------------------------------------
' Procedure : LibraryDb_Load
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Load the specified file (Access db) as a reference (Library database)
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sDb : Fully qualified path and filename of the mdb/mde/accdb/accde/... to
' add as a reference
'
' Returns:
' ~~~~~~~~
' Boolean => True : Successfully loaded
' False : Reference not created
'
' Usage:
' ~~~~~~
' LibraryDb_Load("C:\Users\Dev\Documents\Accounting Db\RefLib.accde")
' Returns -> True
'
' Revision History:
' Rev Date(yyyy-mm-dd) Description
' **************************************************************************************
' 1 2025-05-10 Initial Release - Forum Help
'---------------------------------------------------------------------------------------
Function LibraryDb_Load(sDb As String) As Boolean
On Error GoTo Error_Handler
If Dir(sDb) = "" Then
MsgBox "Library database ''" & sDb & "' not found.", _
vbCritical Or vbOKOnly
GoTo Error_Handler_Exit
End If
Application.References.AddFromFile sDb
LibraryDb_Load = True
Error_Handler_Exit:
On Error Resume Next
Exit Function
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Source: LibraryDb_Load" & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Function
'---------------------------------------------------------------------------------------
' Procedure : LibraryDb_Remove
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Removes the specified file (Access db - Library database) as a reference
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding -> none required
' References:
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sDb : Fully qualified path and filename of the mdb/mde/accdb/accde/... to
' remove as a reference
'
' Returns:
' ~~~~~~~~
' Boolean => True : Successfully removed
' False : Reference was not removed
'
' Usage:
' ~~~~~~
' LibraryDb_Remove("C:\Users\Dev\Documents\Accounting Db\RefLib.accde")
' Returns -> True
'
' Revision History:
' Rev Date(yyyy-mm-dd) Description
' **************************************************************************************
' 1 2025-05-10 Initial Release - Forum Help
'---------------------------------------------------------------------------------------
Public Function LibraryDb_Remove(sDb As String) As Boolean
On Error GoTo Error_Handler
Dim oRef As Access.Reference
Dim sLibraryName As String
'Extract just the file name (library name)
sLibraryName = Left(Mid(sDb, InStrRev(sDb, "\") + 1), _
InStrRev(Mid(sDb, InStrRev(sDb, "\") + 1), ".") - 1)
' Loop through all the references to find the one we want to remove
For Each oRef In Application.References
If oRef.Name = sLibraryName Then
' If a match is found, remove it
Application.References.Remove oRef
LibraryDb_Remove = True
Exit For
End If
Next
Error_Handler_Exit:
On Error Resume Next
Exit Function
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Source: LibraryDb_Remove" & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Function
How to link/use a Library Database?
Once you’ve created you Library database, you can easily integrate it into your main database by opening the VBA Editor (VBE) and:
- Tools -> References …
- Click on the Browse button
- Go navigate to and select your Library database file
- You may need to change the default File Type filter to be able to view accdb/accde files.
- Click Open
- Your Library database should now be listed as the last item in the References listing.
- Then close the References dialog by clicking on the OK button
Controlling the way it Displays
You can control how the Library database gets listed in your main application database. Depending where you look, it is controlled by 2 elements:
- The Library database’s file name
- The Library database’s VBA Project Name (under Tools -> Properties)
VBA Project Explorer
In the VBA Project Explorer, the Library database follows the naming format of:
VBA Project Name (File Name)
Reference list
In the VBA Reference list, the Library database follows the naming format of:
VBA Project Name
Some Best Practices
A lot of this is personal preference, but for me a few Best Practices would include:
- Always compile your Library database into an mde/accde so your code is protected
- Do your development using the accdb and when you are about ready to deploy the db, convert the Library db to a mde/accde and relink it and test your db. Once everything is properly validated, deploy everything.
- I highly recommend using Late Binding throughout
- Don’t forget to use Option Explicit everywhere
- Don’t forget error handling throughout all your code
- Ensure everything Compiles without errors
- Always redistribute both Front-End and Library databases when changes are made to the Library database and ensure to compile both!
How did I never know about this before? This will save me a ton of time.
I knew the library db was a possibility but hadn’t explored it yet because of doubts about how to deploy it to end users. Your suggestions above are very helpful. About “scavenging code modules” I get around that by keeping my base code modules in a version control program ( I use FogCreek software’s Kiln product and find it extremely helpful). The only drawback is that you can’t also keep the various form objects in version control (unless you extracted out all of the code for the class object instantiation that represents a given form).
Thanks for motivating me to finally get started on a full library db for my base application set.
Well you learn something new every day.
I had never heard of this feature and I have been using Access a very long time.
Yes, most developers are unaware of this. It isn’t advertised much, or at all by Microsoft, and seldom seen in literature, etc…
I use them extensively, but with considerably more features than outlined above.
It includes the whole or my Menu (Switchboard) , security & login routines.
Form to set up information on your database, such as Company name, address etc. and various control parameters such as imperial or metric measurement, fonts for the menu, accounting information and a lot more.
Paths to various files such as your back end data files, backup files, zip program and path for your zipped files.
Backup routines & Compact & repair routines.
The Library Db only contains forms & code, all the required data is supplied from tables (linked or permanent) in the main database.
Phil,
Are you willing to share your library with others? If so, at what cost?
Great apporach, but be aware!!!
If you are developing in a referenced accdb, then no changes will be save in the referenced accdb!
In easy words/steps:
– Create a new ‘frontend’ accdb
– Create a library accdb
– Open the frontend and reference the library
– In the VBE you now can open for example a module of the library
– Edit it (no warnings from Access here)
– Save it (no warnings from Access here)
– Close the frontend
– Open the library (by frontend or separatly)
– Open the modified module
– Wonder
I lost code many times, until I really got used to ALWAYS edit the library separately and NEVER from out the frontend.
Very good point!!!
fyi because of this I keep a utility – copy.accdb and keep it open. then I duplicate all my changes once tested into that db. At a certain point I’ll close out development and copy the utility – copy.accdb over the referenced Utility.accdb. Doing this I’ve stopped loosing changes.
Great advice. That is what I actually do during development as well.
Daniel! Thank you, this solved my “Out of Memory” issue when trying to compile. I moved a bunch of code into a library, referenced the compiled .accde file that I located in the same folder as the FE app and now it compiles.
It is shocking and alarming at just how little known it is to find a decent “How-To” on this subject. I first became aware of this by reading “Access VBA 2010 – Inside and Out” by Andrew Couch. He mentions developers using them in lieu of Class Modules but does not cover them – but DOES cover the Class Modules!
I can say with complete confidence that this article in the most comprehensive site as far as what they are and HOW to use them.
Going to buy you a cup of coffee for posting this!
Great article. I tried and it works!
My question: can the “library” file be secured with a database password? I can hide the VBA code, but it would be great to be able to secure the whole file…
Yes and No. Yes it can be, but then it won’t work and will require the user input the password whenever you try to run any code from within it, kind of defeating the whole purpose of securing it and annoying the end-user to no end!
You can however add Password Protection to your VBA Project, this is a little extra protection, but as I illustrated in my article Unlocking an Access VBA Project this type of protection takes a total of 2 minutes to defeat.
I still think your best bet is to convert your library database into an mde/accde, perhaps even rename the file extension to try and obfuscate things, and leave it at that.
Thank you, Daniel, for writing! I decided to create an ACCDE for the library database and it works well, except that I need to change the reference to it in my frontend database back and forth whenever I need to debug the library, but hey, life is not perfect 🙂
Daniel, your articles are excellent! Really excellent.
You mentioned “late binding”. How can I use late binding with an accde?
I use late binding with Outlook no problem.
But with an accde it opens in a new window everytime I use the below code.
Dim myStuff As Object
Set myStuff = createObject(“C:\SomeFolder\myStuff.accde”, “access.application”)
I’m thinking the “application” part of “access.application” is the problem…no?
Sorry made a mistake on that last post.
Dim myStuff As Object
Set myStuff = GetObject(“C:\somefolder\myStuff.accde”, “Access.Application”)
I run into the same problem working with CreateObject.
im sorry for the late comment – but can i open a form from a referenced file in my originel access file, or do i have to copy the entire form?
yes you can, you just put the open form code into a sub procedure in your referenced (library) file and you call that sub from your front end.
it seems my queistion did not go under the right place – my queistion is, can you open a form of the referenced file without copying the whole form?
I have the same question as James Muka above
“You mentioned “late binding”. How can I use late binding with an accde?”
Also, can the library database be used and distributed with Access Runtime?
Whether an accdb or accde, you can use Early or Late Binding, the choice is yours, but I privilege Late Binding as it avoids potential versioning issues.
Yes, Library databases work fine with compiled accde files and in a Runtime environment.
As for late binding in a runtime environment, you can’t use CreateObject(“Access.Application”) or GetObject(, “Access.Application”). Look at: https://www.devhut.net/self-healing-object-variables/ for my approach.
I have been using Library Database since Access 2. With Access 95/97 came the referencing, which was great. It was still possible to edit and save the library from the application. But with Access 2000, the library is no longer saveable, as you know without any warning, just an obstinated greyed out save button. I asked an Access developer: why? The real answer I finally got, far from the usual moralistic “it’s a good practice”, was: “it’s too complicated, we abandoned the idea…” Too complicated on Access, but not on Excel or Word ? I still don’t get it.
All of us professionals maintain our code library in a given context, which is different from one task (a client) to another (our own tools). Sometimes, somewhere, the common library sees the need for a fix, or a new procedure, or an optional parameter, etc. You make them, and you lose them. The context makes the library, you never totally develop it “out of the ground”. 20 years later, you still need somehow dangerous tricks to handle a basic task of managing a professional library.
Can I put a reference table in a library file? For example, if I have a table of compass points…
CP_ID CP_Name CP_Bearing
————————————————
N North 0
NE Northeast 45
E East 90
SE Southeast 135
S South 180
SW Southwest 225
W West 270
NW Northwest 315
I don’t think I can directly reference that table so I would need a function in the library file…
Public Function GetCompassDirection(strCompassID As String) As String
GetCompassDirection = DLookup(“[CP_Name]”, “COMPASS_TABLE”, “[CP_ID] = ‘” & strCompassID & “‘”)
End Function
Then just call “GetCompassDirection”. Seams pretty simple but I can’t get it to work.
A little generic, but you can do something like:
Function ddd() As String Dim db As DAO.Database Dim rs As DAO.Recordset Set db = CodeDb Set rs = db.OpenRecordset("SELECT MyText FROM MyTable WHERE ID=1;") ddd = Nz(rs![mytext], "") rs.Close Set rs = Nothing Set db = Nothing End FunctionSo what I’m saying is to build your own DLookUp function with the same style of Input Arguments. Something maybe like:
Function MyDLookUp(Expr As String, Domain As String, Optional Criteria As String = "") As Variant Dim db As DAO.Database Dim rs As DAO.Recordset Dim sSQL As String Set db = CodeDb If InStr(Expr, " ") = 0 Then sSQL = "SELECT " & Expr & " " Else sSQL = "SELECT [" & Expr & "] " End If sSQL = sSQL & "FROM [" & Domain & "] " If Len(Trim(Criteria)) > 0 Then sSQL = sSQL & "WHERE " & Criteria End If Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot) With rs If .RecordCount <> 0 Then MyDLookUp = Nz(![mytext], "") Else MyDLookUp = Null End If End With rs.Close Set rs = Nothing Set db = Nothing End FunctionThen you can simply call it in your db by doing
MyDLookUp("MyText", "MyTable", "ID=1")Thank you so much, worked like a champ! I knew it was simple, couldn’t see the forest for the trees!
Thanks for this handy wrapper!
Just a question: is it “![myText]” or rather “.Fields(0)”? The former doesn’t work for me…
I tried using library file but class module in the library file gave error of user defined not found. For instance, I have class module in library.accde clsABC. When in my project I use code dim ABC as New clsABC, it gives error in compiling.
Thanks for the great article! I’ve been using Library Databases for a while, but came across an issue I am not sure how to overcome. I have a form in my Code DB called “Roles” that I am calling in my main DB. However, now I am trying to open another form from the “Roles” form… this second form is also in my Code DB but I keep getting the exception: “The Open Form action was cancelled…. The expression may not result in the name of…” etc.
In other words the “Roles” form cannot “see” the other form that is also located in the Library or Code DB with it. I know when referencing tables you can use: CodeDB to specify the database… however is there a way to do the same with forms?
You need to define a function in the CodeDB to open forms and use it to perform the open actions.
Hello Daniel,
Library method seems awesome and I tried to use it but can’t reference class modules in the library. For instance, there is class module in library named as clsABC which I want to ise in my project as dim ABC As clsABC. It gives error of custome type not defined on compile. Is it it like that or I’m doing improperly?
Regards