Learn how to work with Microsoft Access 2013 in this chapter from Microsoft Office Professional 2013 Step by Step, including creating a desktop database, importing and exporting data, navigating the user interface, working with data in a datasheet, configuring Access options, linking Access to external data, and compacting, repairing, and encrypting a database.

Microsoft Access allows you to structure and store your information in a set of database tables and can efficiently manage and share large amounts of data. In addition to quickly locating information, the database ensures consistency in the information by linking together the data in different tables. The product allows you to present the data through both professional-looking forms and reports.

If you are moving from Microsoft Office 2003 to Office Professional 2013, then you will notice a big difference in the user interface. Introduced in Office 2007, the ribbon provides a tabbed interface for working in the product. Depending on what you are doing, the ribbon will display additional tool tabs to assist you in a particular task. The database window of Access 2003 has been replaced with a searchable Navigation pane on the left side of the interface that allows you to filter objects of one or more types.

If you are already familiar with Access 2010, then you will find that the desktop database has improved productivity, but the major revision in the product has been the introduction of the Web App.

Access 2013 enables you to create two distinct but related types of databases. The first is a desktop database; this is a database where the components are held in one or more files on your computer or network. The second type of database is called a Web App; this is a new feature in Access and means that the database is stored in Office 365, and the user interface is displayed in a browser window. When developing a Web App, you use a design interface on your computer that is similar to the familiar desktop database interface.

We will leave a discussion of Web App databases until Chapter 40. In this chapter, we provide detailed steps so that you can perform activities and at the same time familiarize yourself with how to productively work with the Access desktop database interface.

Practice Files

To complete the exercises in this chapter, you need the practice files contained in the Chapter37 practice file folder. For more information, see “Download the practice files” in this book’s Introduction. Note that the MSOfficeProBlank.accdb practice file is created in this chapter, so it is not included in the practice files.

An Access desktop database contains different types of objects:

Tables. Your data is held in a number of tables. The data in most of the tables will be linked to data in other tables. Using this approach of linking or relating data between tables is a key feature of the database and reduces the need to duplicate information.

Queries. These bring together the data from one or more tables and present the data either through a datasheet, form, or report. Note that a datasheet is not a separate object, but the interface when displaying data from either a table or query.

Forms. These allow more flexibility in presenting data than that allowed in a datasheet. They are the most popular method for viewing and managing data. Of particular note are parent/child forms and subforms, where the subforms display related data from other tables.

Reports. Allow you to create a paper-based presentation of your data for printing or previewing on the screen. One unique presentation feature of reports is the ability to have multiple layers of grouping when presenting data.

Macros. This is a programming feature for automating operations. Macros can be found in several places in the database; a discussion of macros is beyond the scope of this chapter.

Modules. These are used for advanced programming, using the Microsoft Visual Basic for Applications (VBA) programming language common to the other Office products. This topic is also beyond the scope of this chapter.

Creating a desktop database

Your Access desktop database consists of a single file, which you will create and save on your computer. There are two different methods to get started with a database. You can either choose to create a database by using a predefined template database, or you can start by creating an empty database.

In this exercise, you’ll create a desktop database.

Set Up

You don’t need any practice files to complete this exercise, but the MSOfficeProBlank.accdb database you create here will be used in further exercises in this chapter. Start Access from the Start screen (Windows 8), or from the Start menu (Windows 7) that is displayed when you click at the left end of the Windows taskbar.

When starting Access, you will notice a list of template databases; for example, Asset tracking (Web App) and the templates prefixed with the name Desktop. Desktop asset tracking will create a desktop database. It is worth taking the time to look at a few of these to get some ideas for creating your own applications.

When you create a blank database, Access will open to display a new table called Table1. This feature allows you to start using one of several techniques for creating blank tables, which we will look at in the next chapter.

Click on the X to close the Table1 object.

The main ribbon will be displayed without any design objects in the database. The Navigation pane displaying All Access Objects is empty.

Managing trust locations and macro security

Because we live in a world where some people will try and hack or subvert systems, and because Office files can be sent by email from unknown sources, Microsoft, by default, switches off features, and it is up to you to decide what to enable.

If you click the Enable Content button, then the next time that you open the database, you will not need to again answer this question, because you previously enabled content in the file. However, if you use the file system to copy the file, rename the file, or move the file to a different location, then you will again be challenged to enable the content.

There are two aspects of trust that you can manage. The first is the location of a file. Trust locations allow you to establish folders where you can place files that will be trusted. This means that you will not need to enable content for files placed in these folders (this feature can also be switched off, allowing files to be opened from any location). The second aspect of trust is whether the application should be allowed to execute certain macro commands or VBA programming code. Although we will not cover these topics in this book, you should be aware of this capability.

If you are working with your own databases or databases from a trusted source, then managing the trust locations and macro security should not pose a problem. But if you are downloading content from the Internet and macro security is fully enabled and trusted locations disabled, then it is possible that when you open a database, malicious code could be executed without any warning. The final decision is your responsibility, but in this section, we will show you how to enable macros and how you can manage your trusted locations.

In this exercise, you’ll set security parameters for a database.

Set Up

You need the MSOfficeProBlank.accdb database from the previous exercise to complete this exercise. If you have not already closed the MSOfficeProBlank.accdb database, then close the database and reopen to display the security warning shown at the beginning of this section. If the security warning does not appear, then continue to follow through the exercise, and you should find that your trust settings have already been altered.

The next choice is a bit more complicated, because you can either decide to add specific folders where you will trust files, or you can disable the trust locations; trusting files on any location. To enable content at all locations, proceed as follows:

Click the check box to Disable All trust Locations, or add specific folders to be trusted. Then click OK.

Clean Up

Leave the MSOfficeProCompleted database open for use in later exercises.