Start by creating a blank Access file anywhere (this will be your library).

  • The only restriction here is that it should be accessible as a file either on the same computer or a shared drive of another computer on the same network as the Access app that will reference it.
  • I would also recommend not using special characters in the file name as this could make things more complicated in other areas potentially.
  • In this example I have created an Access file in d:\Temp called Jon’sLibrary.accdb

 

Create a module in the blank Access file.  By default it will be named Module1.  You can rename it if you want.

Now let’s change your Project name from the default of “Database”.

  • Hit Ctrl-G to go into the VBA Editor
  • On the left hand side you should have the Project explorer window, note where it says Database (Jon’sLibrary):
  • Click on the Database item at the top of the tree.
  • Look down at the lower left and you will see a window that says Properties – Database:
  • Change the (Name) field at the bottom from “Database” to something else.  John’sLibrary is ok, but I would suggest not using special characters and just going with JohnsLibrary or something like that.
  • Now in the Module you can add a function.  I also use Option Explicit because it helps me avoid mistakes with variables.  Use the Public keyword to declare the function.  Here is what I have created:
  • Now close out the database and save all the changes.

 

Your library is now created!  Remember to use the Public keyword in front of your functions and subs, otherwise you will not be able to call them from the other app (I could be wrong about that, but that’s what I think right now)

 

Ok, now, I’ll make an app to use the HelloWorld function from the library.

  • I created a file called Jon’sApp.accdb in the same folder (D:\Temp) that I put the library.  It doesn’t have to be the same folder, but that’s what I’m doing.
  • Next, I open up my new app and hit Ctrl-G to open my VBA editor (Ctrl-G is actually the hot key to go to the “Immediate Window” in the VBA Editor.  Just hitting Alt-F11 is the key to just open the VBA editor window)
  • I go to the Tools -> References… menu item to bring up the references window.
  • Then I hit the Browse… button and navigate to the folder my library is in… D:\Temp in my case.
  • You will not see your file right away, because the file type is set to only display *.olb;*.tlb;*.dll files.  You will need to select Microsoft Access Databases (*.accdb) from the list to see your library db.
  • Now select your library and click OK, then click OK again on the references window.
  • Now I see both my new app project and my library project listed in the project explorer window!

At this point you can run the HelloWorld function in the Jon’sApp Database code and it will run just like you had the function in the database.

Edit by Steve:
I would suggest updating the Project Name via the Tools ! projectname Properties menu instead of using the Properties window. This way the user can also put in a Project Description. In addition, they could set Protection options if they like.