Shared Library
By Richard Rost
3 years ago
Shared Library Procedures in Microsoft Access
Do you have certain functions or subroutines that you use in lots of different databases? And every time you change one of them or make a modification or an enhancement, you have to go and then change it in 10 different database files? Well, in today's video, I'm going to teach you how to create a shared Library database where you can use the modules from it in any of your other Microsoft Access databases.
Mason from Hoffman Estates, Illinois (a Platinum Member) asks: I've got a function that I use in all of my different databases that I wrote myself called FormatMyDate. Some of the reporting my company uses requires a specific date-time format, and that's what this function does. However, they change the format once in a while, and I have to go through and modify it in 15 different database files. Is there any way I can use this one shared function in all of my databases so I don't have to keep updating it everywhere?
Members
Members will learn how to set up a shared library file with an encrypted ACCDE file. There are a few more steps involved, but if you're working with a multi-user networked Access solution, this is what you'll have to do to set up a shared library.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Recommended Courses
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, create shared library database, shared subs, shared functions, Microsoft Access tutorial, library file, shared modules, shared database files, Tools, references, missing or broken reference, project explorer, accdb, accde
Subscribe to Shared Library
Get notifications when this page is updated
Intro In this video, I will show you how to set up a shared library database in Microsoft Access, allowing you to store functions and subroutines in one central location that can be referenced by multiple Access databases. We will talk about how to avoid having to update the same code in several different files, how to create and reference a library database, and some important considerations when managing shared code between projects.Transcript Oh, we got a good one for you today, developers.
You have certain functions or subroutines that you use in all your different databases, and every time you change one of them or make a modification or an enhancement, you have to go and then change it in 10 different database files. This happens to me all the time.
Well, in today's video, I'm going to teach you how to create a shared library database where you can use the modules from it in any of your other Access databases.
Today's question comes from Mason in Hoffman Estates, Illinois, one of my Platinum members.
Mason says, I've got a function that I use in all of my different databases that I wrote myself called Format My Date. Some of the reporting my company uses requires a specific date time format, and that's what this function does. However, they change the format once in a while, and I have to go through and modify it in 15 different database files. Is there any way I can use this one shared function in all of my databases so I don't have to keep updating it everywhere?
Yes, Mason, this is something that I go through too, because I've got several different databases that all do different things, but they all have kind of the same functions in them. I have my regular Computer Learning Zone database with my customer information. I've got a different database that I use for tracking my finances. You guys have seen it - the Account Balance database. I've got my loan amortization database that I use for tracking mortgages. I've got my stock database, all these different databases you've seen me build in some of my classes.
Well, I use them myself too, and a lot of them have the same functions in them. So, when I change it in one spot, I've got to go change it everywhere. That's where a shared database file comes in. Let me show you how to set it up.
Before we get started today, this is a developer level video. What does that mean? Well, it means you need to know some VBA. If you've never done any VBA programming before, go watch my Intro to VBA video. It's about 20 minutes long. It teaches you everything you need to get started. Although today is more of an advanced developer video.
So, this is for people who have been developing for a while. Go watch Intro to VBA, get learning, get started, but this one's for the more advanced developers. Make sure you know how to create custom functions. We're going to use that today. We're going to create a custom shared function that multiple database files can work with, so go watch this too. These are free videos. They're on my website. They're on my YouTube channel. Go watch them and come on back.
First, I'm going to set up a folder to keep all my databases. Whenever I'm working with multiple databases at the same time, I want to put them all in the same database folder, the same shared folder.
Here in my database folder, I'm going to put a copy of my TechHelp free template. We'll copy that there and I'm going to rename this to my front end. We'll call it front-end one. We can use it with front-end two later if we want to.
Now, I'm also going to put in this folder a copy of a blank database. You can create a new blank database if you want from Access. Just go create a file, blank database, and save it. I happen to have one sticking around. I'm going to open that up just to show you what's in it. Look at that - it's blank. There's nothing in it.
So, let's close that guy. I've got my front end and I've got my blank.
Now, in the front end, this is the TechHelp free template. You've seen this a million times if you've been watching my videos for any length of time. In here, I've got a global module where I put some helper functions. This one only has this SleepSecond. That's just to sleep a certain number of seconds. But in some of my other databases, I've got lots of different helper functions that do lots of different things. Let's create a new one.
Let's create a public subroutine, and we're going to call it SayHello. All this guy is going to do is message box "Hi, Rick." And that's it. Save it. Then we'll come back out here. We'll go into my main menu, right-click, build event. Instead of StatusHelloWorld, we're going to SayHello. Just like that, SayHello. Save it. Close it. Close it. Open it. Think. Hi, Rick. There we go.
Now, I need to make another database. Let's say this is my customer database, but I want to use a lot of the same things in here, like my main menu, my customer forms, all that. I'm going to make a different front-end. Let's call it front-end two. I'm going to copy and paste. This will be front-end two. This is a different database, but it's going to use a lot of the same functions that the original had.
Now, right now, since I copied the whole database, that "Hi, Rick" is in here.
But what if I want to make changes? So I go back to front-end one and I go into my module here, and I say, I don't want it to be Rick. I want it to be Richard. Save that. Close it up. It's working great here.
Now, I go back to front-end two. Since I copied the original database, it's still stuck on "Rick." So now I've got to go and change it here too, and change it in the other database. I can change it in all 15 of my databases, let's say.
So, it would be nice if I could have this function, or this subroutine that I just made (works with subs or functions, either way), and if I could put this in a different database file that all the rest of them refer to, where they reference.
We can. Let me show you how.
I'm going to cut this out of here. Snip. Close it. I believe we're in front-end two right now. Let's go back to front-end one, and we're going to delete it out of here as well. Goodbye. Get ready to see you. Close it. Close it.
Now, let's go in that blank database.
The first thing we're going to do is we're going to rename this from "blank." Let's call it our library database. Let's rename this guy "Library." "Blank" is not blank.
Go into here. Let's rename the database project itself. So we're going to Options and then Current Database. We'll call this our Library. While we're in here, let's create a module, and I'll paste in my code.
Public Sub SayHello
Another thing you might want to do while you're in here also is notice over here, if you don't have the Project Explorer, turn it on: View - Project Explorer. It's right there. That way you can see this stuff. I usually don't use this because 90 percent of the time I'm working inside the same database. But if you're going to be working with multiple database files like this, with references, you want to do this.
See where this still says "blank" here? That's the name of the project. We're going to also change that. Right-click, and then click on the Properties. This will bring up the property window. We'll call it "Library" here as well.
Three places you want to change the name: the file name itself, the name of the database (which is inside here), and the name of the project. Just keep everybody unique.
Save this. Close this.
Now let's go back to front-end one. If I hit Hello World now, I get a compile error because it has no idea what SayHello is. That used to be in the global module in this database, but it's not anymore.
So we're going to make a reference to our library database file so that this database - right, blank template - when we can rename this to here if you want to. Right-click and we'll change blank template to front-end one.
Let's add a reference to that library database so that we can use its code.
So, Tools - References. That brings up this. Normally we come in here to use other third party libraries or things like Microsoft Outlook code or whatever, but we're going to add our own reference to our own file here. Click Browse. That brings up this window, which lets you browse for a library file. Now, the default is Type Libraries (*.tlb, *.dll). These are pre-compiled library files, but we're going to drop this down and pick Microsoft Access Databases (*.accdb).
Now browse to where your library file is and hit Open. You'll see it on the bottom. You can see the library. Let's leave it there and hit OK.
Look at that - Library is over here with your Module1. Open that up. There's your SayHello.
Now, my front end can use anything in that Module1. Watch this. Close it. Hit the button. Oh, look at that. Hi, Rick. It's getting the code out of the other database.
Close this database. Let's go back to front-end two. Hit Hello World. It doesn't know what you're talking about. You know why? Because we haven't added the reference to this database. So you'll have to do this to each database that you want to add this code to. But once you've done it, that's it. You don't have to worry about it anymore. Change the back end, then you're good. If you change the library file, it's good everywhere.
Tools - References - Browse - Access - Library. There it is. Hit OK.
You can throw in a Debug Compile to make sure everybody's good. Close it. Now library tools work. See?
Now, if you change your library file (and by the way, if any of the databases that use that library file are open, notice there's a lock file for Library), you will not be able to edit this shared database file if anybody's in it. Any of those databases that use it - if you're in a multi-user solution where you're sharing on a network - everybody's got to be out of the database before you can edit this library file.
So it might be something you have to do after work. If you're doing development work, you may want to keep a local copy of your functions in your local developer database and then update the library file once you're sure your changes are good. So that is an extra step, but it prevents you from having to update it in 15 different databases.
Once I'm in here now, let's say you want to make a change. Let's say everybody's out of the database. There's no lock file here. Open this guy up, the library file now. We'll change our code here to "Hi, Richard" instead of Rick. Save it. Close it. Close the library.
Now, when I open up my front end and click the button - look at that - now it's "Hi, Richard." All of those databases that rely on that SayHello function will now be updated, and you don't have to maintain a whole bunch of different code everywhere else.
One interesting thing I will point out is that when you have a linked function like that - let me get rid of SayHello here. If you have a variable like FirstName or a function name or something, if I type in "FirstName = Joe" like that, see how it automatically cases like that? I talk about that in a lot of my videos. "FirstName" and "LastName" - the F and the N - get capitalized because Access recognizes it.
Watch this - SayHello. Press Enter. Oh, I got it that time. It does not always get it. It's calling me a liar. But when I was running through a test earlier, it doesn't always get it, so don't panic. Sometimes in those external modules like that, it won't see it.
Let's add another one. Let's add another function. I do not want to be a liar here. I think because we already had it, it saw it. The IntelliSense found the capitalization for it.
Let's go to our library database. Let's add another function. Let's say Public Function DoubleIt. All right. We're going to take an x as a Long and we're going to return a Long. We're going to say DoubleIt = x * 2. That's all it does; it just doubles whatever number you send to it. Save it. Close it. Close your library.
Now, front-end one and front-end two both now have access to that DoubleIt function. It's not even like double mint gum. It's just the DoubleIt function.
Let's go into front-end two. Go to design view, right-click, build event, and let's get rid of SayHello. I just want to type in DoubleIt and press Enter. See? It did not do it.
But I guarantee you it knows DoubleIt. Watch this. I'm going to say:
Dim x As Long, y As Long
x = 5
y = DoubleIt(x)
Right there you get your IntelliSense. So it knows DoubleIt. It just won't automatically auto-case it for some reason. I do not know. Access team, get on that one.
So, y = DoubleIt(x)
And then we're going to say Status (using my Status function):
"x doubled is " & y
Look at that. See, it didn't auto-case DoubleIt, but it knows it. We got the IntelliSense. Save it. Close it. Close it. Open it. Hit the button. Five doubled is ten.
There you go. There's your shared function. You can do whatever you want with it. You can change it and play with it. Use it in all those different databases and only have to make changes in one spot. That's the benefit of a shared library file - whatever you want to call it.
One thing you do have to watch out for is if for some reason this library file gets moved, renamed, deleted, whatever. I just renamed it. If you open up the front-end database, guess what's going to happen? Yep, there it is. See? The database project contains a missing or broken reference to ... and it's going to cause problems. I think it's my project library. If you've got an ACCDE file, it's going to cause issues too, because your users will not be able to see anything.
That's how it is with any - that's one of the reasons why I don't like third party references to other programs that you can add to Access. If it's not a standard part of Office, I don't like using it. So that's just something to keep in mind.
Obviously, if you've got a shared server folder with all your databases in it, if this guy isn't there, the database isn't going to work. Just keep that in mind.
Now, this works great, just the way I showed you, with ACCDB files - regular database files. If you are in a multi-user situation and you're giving your end users ACCDE files (encrypted files, execute-only files, whatever the "E" stands for), you're going to need to do some extra stuff. There are a few extra steps and some things you have to know, and I will cover that in the extended cut for the members.
Silver members and up get access to all of my extended cut videos. Gold members can download these databases and get access to my code vault and lots more. Check it out.
If you like learning this stuff, I have tons and tons of developer lessons on my website. Come and check them out. There is the link right there. There are about 40 levels as of right now, and I am constantly adding new ones.
But that is going to be your TechHelp video for today. I hope you learned something, my friends. Live long and prosper. I'll see you next time.Quiz Q1. What is the primary advantage of using a shared library database in Microsoft Access?
A. It allows you to store all your reports in one place
B. It enables easy sharing of global modules between databases, reducing code duplication
C. It stores backup copies of your databases automatically
D. It helps with database user management
Q2. What must you do in each front-end database to use functions from the shared library database?
A. Import all library modules into each database manually
B. Add a reference to the library database via Tools - References
C. Copy and paste the shared functions into each database
D. Set up a linked table to the library database
Q3. Why is maintaining code in one shared library preferable to keeping copies in multiple databases?
A. It reduces code errors and the effort required for updates across many files
B. It improves the speed of all the databases
C. It compresses your database file size
D. It automatically generates user interfaces for you
Q4. What is a potential issue if the shared library database is open or in use when you try to modify it?
A. You can lose all your functions
B. You will not be able to edit or save changes to the library file
C. The reference will disappear from all databases
D. It creates duplicate copies of the library
Q5. What file type should you select when adding a reference to your shared library database in Access?
A. Type Libraries (*.tlb, *.dll)
B. Microsoft Excel Files (*.xlsx)
C. Microsoft Access Databases (*.accdb)
D. All Files (*.*)
Q6. How does Access indicate a missing or broken reference to a shared library database?
A. It displays a broken link icon next to the reference
B. It warns the user with a compile error message about a missing or broken reference
C. It disables all forms in the database
D. It automatically repairs the broken reference
Q7. When using shared library functions, what might you notice about IntelliSense or automatic casing behavior in Access VBA?
A. It always works normally with external library functions
B. It never recognizes any external function names
C. Sometimes it does not auto-capitalize function names from the library but still understands and runs them
D. It refuses to run the code unless all casing is correct
Q8. What should you do before editing the shared library database if used in a multi-user network environment?
A. Move the library to a different folder
B. Make sure all users are out of all databases that reference the library
C. Send an email to users about updates
D. Compact and repair the database
Q9. What could happen if the shared library database file is moved, renamed or deleted?
A. The reference will update automatically
B. All databases referencing it will lose access to its modules and likely show errors
C. Access will create a new library file automatically
D. Nothing will happen as long as the code was previously working
Q10. Is this shared library technique recommended for users completely new to VBA?
A. Yes, anyone can use it without any prior VBA knowledge
B. No, it is intended for developers with some VBA experience
C. Yes, but only if they use macros as well
D. No, it can only be used with SQL knowledge
Answers: 1-B; 2-B; 3-A; 4-B; 5-C; 6-B; 7-C; 8-B; 9-B; 10-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone will teach you how to create a shared library database in Microsoft Access so that you can manage common functions and subroutines in a single location and reuse them across multiple databases.
As developers, we frequently find ourselves maintaining copies of the same code in various database projects. Anytime we enhance or modify these functions, we have to repeat the updates in every single file that uses them. This is a situation I've faced many times. It is not only tedious but also prone to inconsistency if a database gets overlooked.
To illustrate the point, let me share a scenario from my own experience. I have several Access databases for different purposes: one for customer information, another for tracking finances, a loan amortization system, and even a stock database. Most of these databases use similar supporting functions. Whenever I make a change or improvement to one of these functions, I used to update it manually in each file. It's easy to see how this quickly becomes a maintenance burden.
This tutorial will show you how to solve this problem by creating a shared library database that contains your common modules. By referencing this shared library from your other Access databases, you can update your code in a single place and have all your projects use the latest version instantly.
This is considered an advanced, developer-level technique and assumes you already have some experience with VBA programming in Access. If you are new to VBA, I recommend watching my Intro to VBA tutorial, which introduces the basics you'll need to get started. Similarly, if you need a refresher on how to create custom functions in Access, I have free videos on my website and YouTube channel covering that topic as well.
Let me walk you through the process step by step.
First, I like to organize all my related databases into a single folder. This is especially important when working with shared components. Copy your primary Access database (for example, the TechHelp free template) into the folder and give it a meaningful name, such as "FrontEnd1". If you intend to have multiple front-ends, you can create additional copies, for example, "FrontEnd2".
Next, place a blank Access database in this folder. You can create a new one directly in Access if you don't already have one handy. This blank database will become your shared library.
Open the blank database and rename it to something more descriptive, such as "Library". While you're at it, update the database project's name using the "Options" menu under "Current Database", changing it from the default to "Library". Also, in the VBA editor, rename the project itself for clarity.
Inside the Library database, insert a new module and add your commonly used procedures or functions there. For this demonstration, I wrote a simple subroutine called SayHello, which just displays a message box with the text "Hi, Rick". Of course, this could be any function or subroutine you find yourself copying between databases.
Now it's time to connect your main databases (the front ends) to this library. In Access, open one of your front-end databases. If you removed the relevant function from it, trying to call the procedure will result in a compile error. This is expected.
Set up the shared link by going into the VBA editor and choosing Tools followed by References. In the references window, select "Browse", then change the file type to Microsoft Access Databases (*.accdb). Locate the Library database you set up earlier and select it. You'll notice the project now includes your Library and its associated modules. Any functions and procedures in those modules are now available in your front-end project.
Test this by running your code that calls the function housed in the library (for example, clicking a button that calls SayHello). If everything is set up properly, you should see the expected output - in this case, "Hi, Rick" - even though the function is not physically present in your front end.
Repeat the process to add the reference from any additional databases that should use the shared library. Importantly, when you change or improve the function in your Library database, every connected project will instantly benefit from those changes, as long as they reference the updated Library.
There are a few important things to keep in mind:
- If your library file is open in any project, Access creates a lock file. You cannot edit the shared library while any of the linked databases are open. Make sure everyone closes their front ends before you do your maintenance work on the library database.
- I recommend developing changes to your functions in a local developer copy. Once you have tested your updates, apply them to the shared library database and notify other users to close their databases so you can make the change.
- If the library file is renamed, moved, or deleted, any referencing databases will display an error indicating a broken reference. Restoring the original file path and name will resolve this, but it's another reason to keep your shared folder structure organized and protected.
- When working with these library references, features like automatic capitalization (which formats your code properly as you type variable and function names) may not work consistently. The IntelliSense will still recognize your functions, but you may notice differences in how Access cAses function names as you type.
- To demonstrate adding new functions, I added a simple DoubleIt function to the library, which returns a number doubled. After updating the library and closing it, this new function is immediately available to all front ends that reference the library.
This approach works seamlessly with regular ACCDB files. If you need to distribute ACCDE files (compiled and locked down versions of Access databases so users cannot change the code), there are a few additional steps to be aware of. I discuss these advanced issues and the necessary procedures for deploying ACCDE libraries in the Extended Cut video available to Silver members and above on my website.
Maintaining your code in a shared library database will streamline your development process, reduce errors, and ensure consistency across all your Access projects. If you enjoy learning about topics like this, I have extensive developer lessons available on my website, from beginner to advanced concepts, with dozens of hours of content to explore.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below.
Live long and prosper, my friends.Topic List Creating a shared VBA library database in Access
Setting up a shared folder for multiple Access databases
Copying and renaming database files for use as front ends
Creating and using a public subroutine in a module
Demonstrating a shared function across multiple databases
Removing code from individual databases to centralize it
Renaming the Access database project and properties
Using the Project Explorer in the VBA editor
Adding a reference to another Access database via Tools References
Selecting Access database files when browsing for references
Accessing shared functions from a referenced library database
Updating shared code to reflect changes in all front ends
Understanding lock files when editing shared libraries
Making changes to the library database and distributing updates
Testing shared functions and troubleshooting IntelliSense recognition
Adding additional shared functions to the library database
Using shared functions in VBA event code across multiple databases
Handling missing or broken references to library databases
Renaming or moving library files and its effect on references
Considerations for shared libraries in multi-user environments |
