Managing Databases and Objects

Each Access database consists of multiple objects that let you interact with data. Databases can include forms for entering data, queries for searching within it, reports for analyzing it, and tables for storing it. Whenever you work with your database, you are working with many of these objects at once. Fortunately, Access makes managing these objects pretty easy.

In this lesson, you will learn how to to open and close databases, as well as how to open, close, and save objects.

Throughout this tutorial, we will be using a sample database. If you would like to follow along, you'll need to download our Access 2016 sample database. You will need to have Access 2016 installed on your computer in order to open the example.

To open an existing database:

Before you enter data or modify your objects, you will need to open your database.

Select the File tab to go to Backstage view.

Click Open.

Click Browse.

The Open dialog box will appear. Locate and select the database, then click Open.

One or more warning messages may appear when you open your database. If the database contains customized functions, a yellow bar with a security warning may appear below the Ribbon. If you trust the source of your database, click Enable Content for your database to display correctly.

After enabling all content in the database, you may see a message asking if you want to make the database a Trusted Document. Click Yes if you would like all content to be automatically enabled each time you open the database.

You may also be prompted to sign in to the database. Select your name from the login list. If your name does not appear, click Add User to enter your information.

To close a database:

Select the File tab to go to Backstage view.

Select Close.

If you have any unsaved objects, a dialog box will appear for each one asking if you would like to save it. Select Yes to save the object, No to close it without saving, or Cancel to leave your database
open.

Working with objects

It's helpful to think of your database as a large binder or folder in which you store your data. The data itself is contained in database objects. Access treats each of these objects as separate documents, which means you will have to open and save them individually in order to work with them.

You may have noticed that this lesson contains no instructions for saving a database. This is because you cannot save an entire database at once. Rather, you must individually save the objects contained within the database.

To open an object:

In the Navigation pane, locate and double-click the desired object.

The object will appear as a tab in the Document Tabs bar.

By default, the most recently opened object will appear in the main window as the current object. To view another open object, click its tab in the Document Tabs bar.

Saving objects

You'll need to save any changes you make to each object before closing your database. Remember, saving early and often can prevent your work from being lost. However, you will also be prompted to save any unsaved work when you attempt to close your database.

To save a new object:

Select the object you want to save by clicking its tab in the Document Tabs bar.

Click the Save command on the Quick Access Toolbar, or press Ctrl+S on your keyboard.

The first time you save an object, you will be prompted to name it. Enter the desired object name, then click OK.

The object will be saved. Click the Save command again to save any changes to the object.

To close an object:

Select the object you want to close, then click the X to the right of the Document Tabs bar.

If there are any unsaved changes to the object, you will be prompted to save it. Select Yes to save, No to close it without saving your changes, and Cancel to leave the object open.

You can also close an object by right-clicking its tab on the Document Tabs bar and selecting Close.

Select Close All to close all open objects.

To rename an object:

If the object you want to rename is open, close it.

In the Navigation pane, right-click the desired object, then select Rename.

Type the new object name, then press Enter on your keyboard.

Read More »

Getting Started in Access

Whenever you're learning a new program, it's important to familiarize yourself with the program window and the tools within it. Working with Access is no different. Knowing your way around the Access environment will make learning and using Access much easier.

In this lesson, you will familiarize yourself with the Access environment, including the Ribbon, Backstage view, Navigation pane, Document Tabs bar, and more. You will also learn how to navigate with a navigation form, if your database includes one.

Throughout this tutorial, we will be using a sample database. If you would like to follow along, you'll need to download our Access 2016 sample database. You will need to have Access 2016 installed on your computer in order to open the example.


Getting to know Access 2016

Access 2016 uses the Ribbon to organize commands, just like in Access 2013 and 2010. If you've used these versions before, Access 2016 will feel familiar. But if you are new to Access or have more experience with older versions, you should first take some time to become familiar with the Access 2016 interface.



1. Quick Access Toolbar: The Quick Access Toolbar lets you access common commands no matter which tab is selected. You can customize the commands depending on your preference.

2. The Ribbon: The Ribbon contains all of the commands you will need to perform common tasks in Access. It has multiple tabs, each with several groups of commands.

3. Tell Me: The Tell me box works like a search bar to help you quickly find tools or commands you want to use.

4. Microsoft Account: From here, you can access your Microsoft account information, view your profile, and switch accounts.

5. Navigation Pane: The Navigation pane displays all of the objects contained in your database. The objects are grouped by type. Double-click an object to open it.

6. Record Navigation Bar: The Record Navigation bar allows you to navigate records one at a time. Click the arrows to navigate through the records. You can jump to a specific record by typing its ID number into the box.

7. Record Search Box: You can use the Record Search box to search for any term in the current object. The first result will be selected automatically. To navigate through additional results, press the Enter key on your keyboard.

Working with your Access environment
If you've previously used Access 2013 or 2010, Access 2016 will feel familiar. It continues to use features like the Ribbon and the Quick Access Toolbar—where you will find commands to perform common tasks in Access—as well as Backstage view.

The Ribbon
Access uses a tabbed Ribbon system instead of traditional menus. The Ribbon contains multiple tabs, each with several groups of commands. For example, the Clipboard group on the Home tab contains commands such as Cut, Copy, and Paste.

Some groups also have a small arrow in the bottom-right corner that you can click for even more options.

To minimize and maximize the Ribbon:

The Ribbon is designed to respond to your current task; however, you can choose to minimize the Ribbon if you find that it takes up too much screen space.

Click the arrow in the lower-right corner of the Ribbon to minimize it.

The Ribbon will be minimized. Click a tab to make the Ribbon reappear. It will disappear again when not in use.

To maximize the Ribbon, click a tab, then click the pin icon in the lower-right corner. The Ribbon will appear at all times.

Using the Tell me feature

If you're having trouble finding command you want, the Tell me feature can help. It works just like a regular search bar: Type what you're looking for, and a list of options will appear. You can then use the command directly from the menu without having to find it on the Ribbon.

The Quick Access toolbar

The Quick Access Toolbar, located above the Ribbon, lets you access common commands no matter which tab you are on. By default, it shows the Save, Undo, and Redo commands. If you'd like, you can customize it by adding additional commands.

Note that the Save command only saves the current open object. In addition, the Undo command will not undo certain actions, like adding a record.
Pay close attention to your information when using the Undo command to make sure it has the desired effect.

Backstage view

Backstage view gives you various options for saving, opening, and printing your database.

To access Backstage view:

Click the File tab on the Ribbon.

Backstage view will appear.

1. Back to Access: You can use the arrow to close Backstage view and return to Access.

2. Info: The Info pane will appear whenever you access Backstage view. It contains information on the current database and tools to help you compact, repair, and encrypt the database.

3. New: Click here to create a new database from scratch, or choose from a selection of templates.

4. Open: From here, you can open databases from your computer, including those you've recently edited.

5. Save and Save As: Use Save to save the current object. Use Save As to save a new version of the current object or even the entire database.

6. Print: The Print pane contains options for printing the current object in your database.

7. Close: Click here to close the current database.

8. Account: From the Account pane, you can access your Microsoft account information, modify your theme and background, and sign out of your account.

9. Options: Here, you can change various Access options. For example, you can choose a form to automatically display when your database is opened or modify the default cell and font style.

The Navigation pane
The Navigation pane is a list containing every object in your database. For easier viewing, the objects are organized into groups by type. You can open, rename, and delete objects using the Navigation pane.

To minimize and maximize the Navigation pane:

The Navigation pane is designed to help you manage all of your objects; however, if you feel that it takes up too much of your screen space, you can minimize it.

To minimize the Navigation pane, click the double arrow in the upper-right corner.

The Navigation pane will be minimized. Click the double arrow again to maximize it.

If you want to make the Navigation pane smaller without fully minimizing it, you can resize it. Simply click and drag the right border of the Navigation pane. When it is the desired size, release your mouse.

Object sorting in the Navigation pane

By default, objects are sorted by type, with tables in one group, forms in another, and so on. However, if you want you can sort the objects in the Navigation pane into groups of your choosing. There are four sort options:

Custom allows you to create a custom group for sorting objects. After applying the sort, simply drag the desired objects to the new group.

• Object Type groups objects by type. 

• This is the default setting.

• Tables and Related Viewsgroups forms, queries, and reports with the tables they refer to.

• Created Date or Modified Date sorts objects based on when they were created or last edited.

To sort objects in the Navigation pane:

Click the drop-down arrow to the right of All Access Objects, then select the desired sort from the drop-down menu.

The objects in the Navigation pane will now be sorted to reflect your choice.

To further customize the appearance of the Navigation pane, you can minimize groups of objects you don't want to see. Simply click the upward double arrow next to the group name. To show a group, click the downward double arrow.

Databases with navigation forms

Some databases include a navigation form that opens automatically when the database is opened. Navigation forms are designed to be a user-friendly replacement for the Navigation pane. They contain tabs that allow you to view and work with common forms, queries, and reports. Having your frequently used objects available to you in one place lets you access them quickly and easily.

To open an object from a navigation form, click its tab. The object will be displayed within the navigation form. Once an object is open, you can work with it as you normally would. In the example below, the navigation form has tabs near the top left for orders, customers, and menu items, and each one will open a corresponding object.



Generally, navigation forms include only the objects a typical user will need to work with fairly regularly, which is why your navigation form may not include every single form, query, or report. This makes it easier to navigate the database. By hiding tables and rarely used forms, queries, and reports, it also reduces the chance of the database being damaged by users accidentally editing or deleting necessary data.

For this reason, it's important to ask your database designer or administrator before working with objects that are not available in your navigation form. Once you have the go-ahead, you can simply maximize the Navigation pane and open the objects from there.

Read More »

Introduction to Objects

Databases in Access are composed of four objects: tables, queries, forms, and reports. Together, these objects allow you to enter, store, analyze, and compile data however you want.

In this lesson, you will learn about each of the four objects and come to understand how they interact with each other to create a fully functional relational database.

Tables
By this point, you should already understand that a database is a collection of data organized into many connected lists. In Access, all data is stored in tables, which puts tables at the heart of any database.

You might already know that tables are organized into vertical columns and horizontal rows.

In Access, rows and columns are referred to as records and fields. A field is more than just a column; it’s a way of organizing information by the type of data it is. Every piece of information within a field is of the same type. For example, every entry in a field called First Name would be a name, and every entry in field called Street Address would be an address.

Likewise, a record is more than just a row; it's a unit of information. Every cell in a given row is part of that row’s record.

Notice how each record spans several fields. Even though the information in each record is organized into fields, it belongs with the other information in that record. See the number at the left of each row? It’s the ID number that identifies each record. The ID number for a record refers to every piece of information contained on that row.

Tables are good for storing closely related information. Let's say you own a bakery and have a database that includes a table with your customers' names and information, like their phone numbers, home addresses, and email addresses. Because these pieces of information are all details on your customers, you’d include them all in the same table. Each customer would be represented by a unique record, and each type of information about these customers would be stored in its own field. If you decided to add any more information—say, a customer's birthday—you would simply create a new field within the same table.

Forms, queries, and reports
Although tables store all of your data, the other three objects—forms, queries, and reports—offer you ways to work with it. Each of these objects interacts with the records stored in your database's tables.

Forms

Forms are used for entering, modifying, and viewing records. You likely have had to fill out forms on many occasions, like when visiting a doctor's office, applying for a job, or registering for school. The reason forms are used so often is that they're an easy way to guide people toward entering data correctly. When you enter information into a form in Access, the data goes exactly where the database designer wants it to go: in one or more related tables.

Forms make entering data easier. Working with extensive tables can be confusing, and when you have connected tables you might need to work with more than one at a time to enter a set of data. However, with forms it's possible to enter data into multiple tables at once, all in one place. Database designers can even set restrictions on individual form components to ensure all of the needed data is entered in the correct format. All in all, forms help keep data consistent and organized, which is essential for an accurate and powerful database.

Queries

Queries are a way of searching for and compiling data from one or more tables. Running a query is like asking a detailed question of your database. When you build a query in Access, you are defining specific search conditions to find exactly the data you want.

Queries are far more powerful than the simple searches you might carry out within a table. While a search would be able to help you find the name of one customer at your business, you could run a query to find the name and phone number of every customer who's made a purchase within the past week. A well-designed query can give information you might not be able to find just by looking through the data in your tables.

Reports

Reports offer you the ability to present your data in print. If you've ever received a computer printout of a class schedule or a printed invoice of a purchase, you've seen a database report. Reports are useful because they allow you to present components of your database in an easy-to-read format. You can even customize a report's appearance to make it visually appealing. Access offers you the ability to create a report from any table or query.

Putting it all together
Even if you have a good idea of how each object can be used, it can initially be difficult to understand how they all work together. It helps to remember that they all work with the same data. Every piece of data a query, form, or report uses is stored in one of your database tables.

Forms allow you to both add data to tables and view data that already exists. Reports present data from tables and also from queries, which then search for and analyze data within these same tables.

These relationships sound complicated, but in fact they work together so well and naturally that we often don't even notice when we're using connected database objects. Have you ever used an electronic card catalog to search for a book at the library? Chances are, you entered your search into something that looks like this:

When you performed your search, you were entering your search terms into a form that then created and ran a query based on your request. When the query finished searching the database's tables for records that matched your search, you were shown a report that drew information from the query and the related tables—in this case, a list of books matching your search terms. You could represent the connections between the objects like this:

Let's say instead of using these tools you had to search within a giant table containing every book in the library system. The relevant records would likely be spread out across many tables: a table for book titles and descriptions, a table containing information on which books are checked in or out, and a table with each branch of the library, just to name a few.

You'd have to search at least three tables just to find a book, learn its location, and see whether it's checked in! It's easy to imagine how difficult it could become to find the right book. If you weren't careful, you might even mess something up by accidentally deleting or editing a record. It's easy to see how the database objects make this search much more manageable.

In our Introduction to Databases lesson, we discussed the concept of a relational database, which is a database that is able to understand how different sets of data relate to one another. Situations like the example above are exactly why people find relational databases so useful. Without a relational database, what should be a simple task—searching for a book and seeing if it's checked in and where—becomes incredibly complicated and time consuming. Knowing how to use the four Access objects can make even complicated tasks fairly user friendly.

Read More »

Introduction to Databases

Microsoft Access is a database creation and management program. To understand Access, you must first understand databases.

In this lesson, you will learn about databases and how they are used. You will familiarize yourself with the differences between data management in Microsoft Access and Microsoft Excel. Finally, you will get a look ahead at the rest of the Access tutorial.

What is a database?
A database is a collection of data that is stored in a computer system. Databases allow their users to enter, access, and analyze their data quickly and easily. They're such a useful tool that you see them all the time. Ever waited while a doctor's receptionist entered your personal information into a computer, or watched a store employee use a computer to see whether an item was in stock? If so, then you’ve seen a database in action.

The easiest way to understand a database is to think of it as a collection of lists. Think about one of the databases we mentioned above: the database of patient information at a doctor's office. What lists are contained in a database like this? To start with, there's a list of patients' names. Then there's a list of past appointments, a list with medical history for each patient, a list of contact information, and so on.

This is true of all databases, from the simplest to the most complex. For instance, if you like to bake you might decide to keep a database containing the types of cookies you know how to make and the friends you give these cookies to. This is one of the simplest databases imaginable. It contains two lists: a list of your friends, and a list of cookies.

However, if you were a professional baker, you would have many more lists to keep track of: a list of customers, a list of products sold, a list of prices, a list of orders, and so on. The more lists you add, the more complex the database will be.

In Access, lists are a little more complex than the ones you write on paper. Access stores its lists of data in tables, which allow you to store even more detailed information. In the table below, the People list in the amateur baker’s database has been expanded to include other relevant information on the baker’s friends.

If you are familiar with other programs in the Microsoft Office suite, this might remind you of Excel, which allows you to organize data in a similar way. In fact, you could build a similar table in Excel.

Why use a database?

If a database is essentially a collection of lists stored in tables and you can build tables in Excel, why do you need a real database in the first place? While Excel is great at storing and organizing numbers, Access is far stronger at handling non-numerical data, like names and descriptions. Non-numerical data plays a significant role in almost any database, and it's important to be able to sort and analyze it.

However, the thing that really sets databases apart from any other way of storing data is connectivity. We call a database like the ones you’ll work with in Access a relational database. A relational database is able to understand how lists and the objects within them relate to one another. To explore this idea, let's go back to the simple database with two lists: names of your friends, and the types of cookies you know how to make. You decide to create a third list to keep track of the batches of cookies you make and who they’re for. Because you're only making cookies you know the recipe for and you're only going to give them to your friends, this new list will get all of its information from the lists you made earlier.

See how the third list uses words that appeared in the first two lists? A database is capable of understanding that the Dad and Oatmeal cookies in the Batches list are the same things as the Dad and Oatmeal cookies in the first two lists. This relationship seems obvious, and a person would understand it right away; however, an Excel workbook wouldn’t.

Excel would treat all of these things as distinct and unrelated pieces of information. In Excel, you'd have to enter every single piece of information about a person or type of cookie each time you mentioned it because that database wouldn't be relational like an Access database. Simply put, relational databases can recognize what a human can: If the same words appear in multiple lists, they refer to the same thing.

The fact that relational databases can handle information this way allows you to enter, search for, and analyze data in more than one table at a time. All of these things would be difficult to accomplish in Excel, but in Access even complicated tasks can be simplified and made fairly user friendly.

Read More »