Introduction

Welcome! Import, Link, Split, Multiuser

S M L XL FS | Slo Reg Fast 2x | Bookmark

Welcome to Microsoft Access Expert Level 21. In this course we will continue learning how to import data into your Microsoft Access databases, starting with how to import and link objects such as tables, queries, forms, and reports from other Access databases. We will discuss multi-user database theory, setting up your database for sharing on a local area network, and the importance of splitting your database into front-end and back-end files. We will also cover record locking and preventing data loss when multiple users edit the same data. Access Expert Level 20 is a recommended prerequisite for this course.

Navigation

Prev: X21
Next: Lesson 01

Keywords

Access Expert, import data from access database, import tables queries forms reports, link database tables, split database, front end back end, multi user, shared database, record locking, database splitter, network sharing, sample database, northwind tra

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

Welcome to Microsoft Access Expert, level 21, brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.

In today's class, we are going to continue learning how to import data into our Microsoft Access Databases. We are going to start by importing data from other Access Databases. We will learn how to import all the different objects: tables, queries, forms, reports, and so on. We will then learn to link to the tables in other Access Databases. For example, if someone else on your network, maybe the Accounting Guys, has a database and you want to be able to access one of their tables, you can link to their table and have a real-time connection to work with their data.

Once you know how to link to another database, this allows us to start setting up multi-user database solutions. We will learn how to set up your Access Database to be used by multiple people on a local area network - your in-house network.

We will start off by going over some shared database theory. It is wise to have a good understanding of databases and how they work in a multi-user setting before you start actually building things. Then we will learn how to split the database using the database splitter wizard, which will create two files for us: a front-end database that has all of your forms, queries, and reports in it, and a back-end database that houses your shared tables.

Then we will learn about record locking, how to prevent two users from editing the same record at the same time, and we will see how to properly set up your database to prevent data loss.

This class follows Microsoft Access Expert Level 20. Level 20 covered sending mass email from your Access Database, but more importantly, it covered importing data from a variety of different data sources, including Excel spreadsheets, text files, XML, Microsoft Outlook, and many more. I strongly recommend you watch Level 20 before watching 21.

Today's class was recorded using Microsoft Access 2013. Most of the lessons should work just fine, even with 2007 and 2010, but if you come across something in these versions that is significantly different, please let me know, post a message in the forums below, or send me an email.

If you are using Access 2003 or earlier, you should get my Access 308 class, which covers the importing of data, and the split database component is covered in Access 313.

My courses are broken up into Beginner, Expert, Advanced, and Developer Level Classes. Beginner level classes are for novices. You should understand all the topics covered in them by the time you get to the Expert level classes, which you are in now. When you finish all the Expert level classes, the Advanced classes will cover event programming and macros, and the Developer classes will cover Visual Basic for Applications.

Each group of classes is broken down into multiple levels: level 1, 2, 3, and so on. In addition to my normal Access classes, I also have seminars designed to teach specific topics.

Some of my seminars include building web-based databases, creating forms and reports that look like calendars, securing your database, working with images and attachments, writing work orders and running a service business, tracking accounts payable, learning the SQL programming language, creating loan amortization schedules, and many more. You can find details on all of these seminars and more on my website at accesslearningzone.com.

If you have questions about the topics covered in today's lessons, please feel free to post them in my student forums. If you are watching this course in the online theater on my website, you should see the student forum for each lesson appear in a small window next to the class video.

Here you will see all of the questions that other students have asked, as well as my responses to them and comments that other students have made. I encourage you to read through these questions and answers as you start each lesson and feel free to join in the discussion.

If you are not watching these lessons on my website, you can still visit the student forums later by visiting accesslearningzone.com/forums.

To get the most out of this course, I recommend you sit back, relax, and watch each lesson completely through once without trying to do anything on your computer. Then, replay the lesson from the beginning and follow along with my examples. Actually create the same database that I make in the video, step by step. Do not try to apply what you are learning right now to other projects until you have mastered the sample database from class. If you get stuck or do not understand something, watch the video again from the beginning or tell me what is wrong in the student forum and I will do my best to help you.

Most importantly, keep an open mind. Access may seem intimidating at first, but once you get the hang of it, you will see that it is real easy to use.

Now, I strongly encourage you to build the database that I build in today's class by following along with the videos. However, if you would like to download a sample copy of my finished database file, you can find it on my website at accesslearningzone.com/databases. Sometimes, if you get stuck, the easiest way to learn is to tear apart someone else's database.

One of the ways that I taught myself Access years ago was by tearing apart the Northwind Traders database that comes with Microsoft Access. You will find there is a sample database for each of my courses on my website.

Now, let's take a few minutes and go over exactly what we are going to cover in today's class.

In lesson one, we are going to learn how to import and link Access database objects from one database file to another.

In lesson two, we are going to learn about multi-user database theory, sharing a database on a network, why you want to split your database, the difference between front-end and back-end databases, and the pros and cons of splitting your database. If you do not like database theory, feel free to skip this lesson. However, I do not recommend it.

In lesson two, we talked about all of the multi-user database theory, while in lesson three, we are going to put that theory into practice. I am going to start showing you how to split your database into front-end and back-end files so that you can set it up for use on your network.

In lesson four, we are continuing with splitting your database.

In lesson five, we will talk about record locking issues when two people are in the same database and they start to edit the same record, what happens.

Quiz

Q1. What is one of the main objectives in this class?
A. Learning to import data from other Access databases
B. Learning how to create pivot tables in Excel
C. Understanding how to build custom Excel add-ins
D. Learning to use SQL Server Management Studio

Q2. What does it mean to link to a table in another Access database?
A. Importing a snapshot copy of the table
B. Creating a real-time connection to another table
C. Copying part of the data to another database
D. Exporting your tables to another program

Q3. Why is it important to understand shared database theory before building a multi-user database?
A. To properly set up and avoid potential data problems
B. To avoid using queries altogether
C. To make the database faster on a single computer
D. Because Access cannot handle multiple users

Q4. What does the database splitter wizard do?
A. Splits a table into two fields
B. Creates front-end and back-end database files
C. Merges two databases into one
D. Compresses the database file size

Q5. What is housed in the back-end database after splitting?
A. Only reports and forms
B. Shared tables
C. VBA code modules
D. Queries and macros

Q6. What is record locking used for?
A. To protect a database with a password
B. To prevent two users from editing the same record at the same time
C. To archive old data automatically
D. To add encryption to a database

Q7. What should you do before trying to apply new concepts to your own projects?
A. Master the sample database from class
B. Install a newer version of Access
C. Immediately start a new business application
D. Call Microsoft customer support

Q8. If you are using Access 2003 or earlier, which class should you use for split databases?
A. Access 101
B. Access 313
C. Access 210
D. Access 403

Q9. What is the benefit of linking tables in a multi-user network environment?
A. Only one person can access the data at a time
B. All users get real-time access to shared data
C. No data backup is necessary
D. It increases the size of the database

Q10. What is the recommended approach to learning from the video lessons?
A. Watch through once, then follow along by building the example database
B. Only read the video transcript
C. Memorize every sentence in the video
D. Skip the practice and apply concepts directly to your live database

Answers: 1-A; 2-B; 3-A; 4-B; 5-B; 6-B; 7-A; 8-B; 9-B; 10-A

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 video from Access Learning Zone is Microsoft Access Expert Level 21. I am Richard Rost, and in this lesson we are taking your skills a step further with importing and linking data between Access databases, as well as setting up your database for multiple users.

We'll start off by discussing how to bring in data from other Access databases. You'll see how to import different kinds of objects, such as tables, queries, forms, and reports from one Access file into another. Beyond simply importing, I'll show you how to link tables from other databases. For example, if your accounting team keeps their records in a separate database, and you need access to those tables, you'll see how to create a live connection so you can work with their data in real time.

This ability to link to other databases lays the foundation for setting up multi-user solutions. I'll go over how to get your Access database ready for multiple people to use on a local area network, meaning others in your office can access and work with the data at the same time.

It's important before you jump in and start building these kinds of solutions to understand how sharing databases works. We will start out with some database theory about multi-user environments. Once you have a handle on how multi-user databases should work, I'll walk you through splitting your database using the database splitter wizard. This splits your project into two files: a front-end file that contains all your forms, queries, and reports, and a back-end file that holds your shared tables. This is the best way to set up a database that multiple users will access on a network.

We'll also get into the details of record locking. I'll teach you how to protect your data from being overwritten if two people try to edit the same record at the same time. You'll learn the techniques and settings necessary to avoid conflicts and prevent data loss.

If you just finished Microsoft Access Expert Level 20, this course picks up right where that left off. In Level 20, we talked about sending mass emails from Access, but more importantly, we covered importing data from other sources like Excel, text files, XML, and Outlook. If you missed Level 20, I recommend you watch it before this one to ensure you've got a solid foundation.

This class was recorded with Access 2013, but most of these techniques also work in Access 2007 and 2010. If you find any version-specific differences, let me know on the online forums or by email. For those still using Access 2003 or older, I have other classes on my site that cover these topics for those editions.

If you are new to my Access curriculum, my classes are grouped into Beginner, Expert, Advanced, and Developer Levels. Each group includes multiple levels, with the Beginner series for those just starting out. By the time you are in the Expert class, you should be comfortable with all the foundations. Once you finish the Expert series, the Advanced courses will introduce you to event programming and macros, and the Developer classes will focus on Visual Basic for Applications.

In addition to these structured courses, I offer focused seminars on certain topics, like building web databases, creating calendar layouts, database security, handling images and attachments, managing work orders, accounts payable tracking, SQL programming, and loan amortization schedules. You can get the details on all of these on my website.

If you have questions about any topic we cover, I encourage you to participate in the student forums. If you're watching this on my website, you should see the lesson forum alongside the class video. This is where you can view existing questions and discussions, add your own, or see responses from myself and other students. It's a great place to get clarification, since many common questions are already answered there.

If you're not watching on my website, you can always visit the forums by going to accesslearningzone.com/forums.

To get the most out of this course, I recommend that you watch each lesson the whole way through the first time without touching your computer. Then go back, start the video again, and follow along building the same database I make in the lesson. Reproduce my steps just as you see them before you try to use these techniques in your own projects. If you get stuck or confused, watch the lesson again from the start, or ask for help on the forum and I will do my best to assist you.

Keep an open mind; I know Access might feel overwhelming at first, but once you get the basics down you'll find it much easier to use than you might expect.

As always, I strongly suggest you build the database along with me, but if you need it, you can download my finished sample database from my website at accesslearningzone.com/databases. Sometimes the best way to learn is to pull apart a complete working database and see how all the pieces fit together. This is exactly how I taught myself Access years ago with the Northwind Traders sample database. For each class, you can download the corresponding sample file.

Here's what we'll be covering in today's class:

In lesson one, we'll cover importing and linking objects (like tables, queries, forms, and reports) from one Access database to another.

Lesson two will focus on the theory behind multi-user databases, sharing databases on a network, why and how to split a database, the roles of front-end versus back-end files, and advantages and drawbacks of splitting. Even if you're not fond of theory, this lesson is important groundwork.

After discussing the theory in lesson two, lesson three will put those ideas into action by showing you how to split your database for network use, creating front-end and back-end files.

Lesson four continues the process of splitting your database, reinforcing all of the necessary steps.

Lesson five deals with record locking: what happens when multiple people try to edit the same record at the same time, and how to handle those situations properly.

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

Importing data from other Access databases
Importing tables, queries, forms, and reports
Linking tables from other Access databases
Setting up real-time data connections to linked tables
Shared database theory for multi-user environments
Splitting databases using the Database Splitter Wizard
Front-end and back-end database architecture
Best practices for sharing Access databases on a network
Setting up databases for multiple users on a LAN
Record locking in multi-user databases
Preventing data loss with proper record locking
Pros and cons of splitting Access databases

Article

Welcome to this guide on importing data into Microsoft Access databases and setting up your database for sharing and multi-user environments. In this article, I will walk you through the steps involved in importing and linking objects from other Access databases, discuss the theory and practical aspects of splitting your database for use on a network, and explain how to handle editing conflicts between multiple users.

The first thing to understand is that Microsoft Access allows you to bring in objects from other Access database files. This means you can import tables, queries, forms, reports, macros, and modules into your current database. Suppose your organization has a separate Access database maintained by the Accounting department, and you want to gain access to one of their tables, perhaps for reporting or combining data. You have two options: import the table, which copies it into your database and creates an independent copy, or link to the table, which gives you a real-time connection to their live data. Linking is particularly useful for collaborative, multi-user environments because it keeps everyone working with the most up-to-date information.

Importing data from other databases is simple. Within Access, open your target database and go to the External Data tab, then choose the appropriate option to import Access objects. Select the source database file, and Access will show you all available objects. Choose the ones your project needs and follow the wizard to finish the process. Once imported, those objects are now part of your database.

If you choose to link instead of import, follow the same approach but choose the linking option when prompted. Access will create a linked table in your database, which acts like a pointer to the actual data in the other database. Any updates to the data in either database are immediately reflected in both places, so everyone can work with the same information in real time.

Once you understand linking, you are ready to set up your database for multiple users. In a typical office, several people need to access the same set of data, often at the same time. To configure Access for this, you should split your database into two separate files. The back-end database stores only the tables and data, and is placed on a shared network location that all users can reach. The front-end database contains all the forms, queries, and reports. Each user gets their own copy of the front-end database, which links to the shared tables in the back-end database.

The purpose of splitting the database is to improve performance, simplify maintenance, and reduce the risk of corruption. By keeping the front-end on each individual's computer and only putting the necessary tables on the shared drive, you prevent unnecessary data traffic over the network. If you need to update a form or report, you only have to distribute a new front-end file. The back-end stays in place with all your data intact.

To split your database, Access provides a built-in database splitter wizard. Start the wizard from the Tools menu, choose to split your database, and follow the prompts. The wizard will save a copy of your tables in the back-end database and convert the tables in your front-end into linked tables. Afterward, test your setup by opening the front-end, making sure the linked tables connect successfully.

Working in a multi-user environment brings up questions about record locking. Imagine two people open the same customer record and make changes at the same time. By default, Access uses record-level locking. When a user starts editing a record, it becomes locked for editing by others until the user finishes. If two users try to save changes to the same record at the same time, one will receive a notification that the record changed since it was opened, and that user's changes cannot be saved until the record is reloaded. This helps prevent accidental overwrites.

It is important to understand how Access handles record locking to prevent data loss. Encourage your users to save their work frequently and avoid leaving records open for editing when not necessary. Access allows you to adjust locking options in the database settings under the Advanced tab, but for most environments, the default settings work well.

Splitting your database and setting up linked tables is the foundation of any multi-user Microsoft Access application. By following these instructions, you can enable your team to collaborate effectively and keep your data secure and consistent. If at any point you want to see how this works in a real-world example, you can explore sample databases like the Northwind Traders database or download practice files from trusted sources to learn even more.

By understanding these concepts, you are now prepared to import and link data from different sources, split your Access database for network use, and manage concurrent data editing to ensure a safe and efficient multi-user environment. Keep practicing these steps, and you will find that Microsoft Access is a powerful tool for building reliable database applications in your organization.