Library Databases

Welcome! Shared VBA Forms Reports Across DBs

Welcome to Access Developer 63. We will learn how to create Library Databases in Microsoft Access to share reusable VBA functions, forms, and reports across multiple applications. We will cover references, public and private procedures, opening shared forms and reports, connecting library forms to application data, using SQL IN clauses and linked tables, passing parameters with OpenArgs, and avoiding issues with broken references, file locking, and duplicate object names.

Navigation

Prev: D63
Next: Lesson 1

Keywords

Access Developer, Access shared library database, VBA library reference, shared VBA functions, shared forms, shared reports, SQL IN clause, OpenArgs, public and private procedures, broken references, library file locking

Subscribe to Library Databases
Get notifications when this page is updated

More Information

Transcript

Welcome to Microsoft Access Developer Level 63, brought to you by Access Learning Zone. I am your instructor, Richard Rost.

Today, we're going to learn how to build shared library databases in Microsoft Access. If you've got several databases that use the same VBA functions, forms, or reports, there's no reason to keep separate copies of everything. We're going to put that shared functionality into one database, a library database, that all of your other databases can reference.

I'll show you how to work with public and private procedures, open forms that are stored in another database, and connect those forms to your application's data. We'll also look at passing parameters using open args, sharing reports, and some of the little quirks that you need to watch out for when working with library files.

This class follows Access Developer Level 62. You should be comfortable with working with VBA, standard modules, public procedures, and form events. Developer 16 is especially helpful for understanding database objects and recordsets. Developer 25 covers passing parameters and working with function arguments. And, as always, I recommend taking all my previous classes in order and don't skip levels.

I'll be using Microsoft Access for Microsoft 365, but the techniques we're covering in this level also apply to Access versions going as far back as 2007.

All right, let's take a closer look at what we're going to cover today.

In lesson one, we'll create our first shared library database and connect two separate Access databases to it using VBA references. We'll move some commonly used functions into the library, so both databases can use the same code without keeping duplicate copies. We'll also look at broken references, library file locking, and some of the things you need to be careful about when editing shared code.

In lesson two, we'll take things a step further and start sharing forms between databases. We'll look at public and private procedures, add speech to our status routine, and create a reusable notice form that lives in the library. Then we'll build a shared customer form and learn how to connect it to tables in the calling database using the SQL IN clause. We'll also see how shared linked tables can make this process easier.

And in lesson three, we'll learn how to pass information to our shared library forms. We'll use parameters and open args to filter records, read and update controls on library forms, and create a shared report that can be opened from another database. We'll also demonstrate what can happen when the library and calling database contain forms with the same names and why it's important to keep those names unique.

By the end of this level, you'll know how to create a library database containing reusable VBA code, forms, and reports that multiple Access applications can share. This can save you a lot of duplicated work and make maintaining several databases much easier.

If you have any questions about the material covered in today's class, just scroll down to the bottom of the page and post your questions in the questions section. You'll also find a discussion thread with questions and answers from other students, so take a minute to look through those first. Someone may have already asked the question and gotten an answer.

And don't forget to click the subscribe button in that section. This way, you'll be notified whenever someone else posts a new question, answer, or comment about the class.

And if you've got questions about Microsoft Access that aren't directly related to the material we're covering in this class, please post those questions in the Access Forum instead. That's the best place for general Access questions, troubleshooting, and discussions about your own database projects.

Posting in the forum also allows other members of the Access Learning Zone community to join the conversation, including people who aren't enrolled in this particular class. You'll have a much larger group of people who can help share ideas and learn from the discussion.

As always, I recommend you watch each video once through completely and then watch it a second time and follow along with the examples.

All right, now sit back, relax, grab your coffee, and it's time to start lesson one.

Intro

Welcome to <B>Access Developer 63</B>. We will learn how to create Library Databases in Microsoft Access to share reusable VBA functions, forms, and reports across multiple applications. We will cover references, public and private procedures, opening shared forms and reports, connecting library forms to application data, using SQL IN clauses and linked tables, passing parameters with OpenArgs, and avoiding issues with broken references, file locking, and duplicate object names.

Summary

Today's video from Access Learning Zone introduces Microsoft Access Developer Level 63, where I will show you how to create and use shared library databases.

A shared library database allows multiple Access applications to use the same VBA functions, forms, reports, and other reusable objects. Instead of copying the same code and interface objects into every database you build, you can store those shared resources in one library database and reference that library from your other applications. This reduces duplicated work and makes maintenance much easier because updates can be made in one central location.

This class follows Access Developer Level 62. You should already be comfortable working with VBA, standard modules, public procedures, and form events. Developer Level 16 is especially useful for understanding database objects and recordsets. Developer Level 25 covers passing parameters and working with function arguments. I recommend taking the previous classes in order so you have the necessary background for this material.

I will be using Microsoft Access for Microsoft 365, but the techniques in this class work with Access versions back to Access 2007.

In lesson one, I will create a shared library database and connect two separate Access databases to it using VBA references. I will move commonly used functions into the library so both databases can use the same code without maintaining duplicate copies. I will also cover broken references, library database locking, and important considerations when modifying shared code.

In lesson two, I will begin sharing forms between databases. I will explain the difference between public and private procedures, add speech capabilities to a status routine, and create a reusable notice form stored in the library database. I will also build a shared customer form and show you how to connect it to tables in the calling database with the SQL IN clause. You will also see how shared linked tables can simplify this process.

In lesson three, I will show you how to pass information to forms stored in the shared library. We will use parameters and OpenArgs to filter records, read and update controls on library forms, and create a shared report that can be opened from another database. I will also demonstrate what can happen when the library database and the calling database contain forms with identical names, and why it is important to keep object names unique.

By the end of this level, you will understand how to build a library database containing reusable VBA code, forms, and reports that can be shared by multiple Access applications. This approach can save considerable development time and make it much easier to maintain several databases that rely on the same functionality.

If you have questions about the material covered in this class, you can post them in the questions section for the lesson. You can also review the existing discussion thread, since another student may already have asked the same question. Subscribing to the discussion will notify you when new questions, answers, or comments are posted.

For general Microsoft Access questions that are not specifically related to this course, use the Access Forum. The forum is the best place for troubleshooting, discussing your own database projects, and getting help from the broader Access Learning Zone community.

I recommend watching each lesson once from beginning to end, then watching it again while following along with the examples in your own database.

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

Shared library databases in Microsoft Access

Primary Topics

shared library databases, VBA references, reusable public procedures, shared forms, shared reports, cross-database data access, OpenArgs parameters

Secondary Topics

public versus private procedures, broken references, library file locking, SQL IN clause, linked tables, form name conflicts, recordsets