Home > Sample chapters

Create and manage databases

Access databases are made up of database objects: tables, queries, forms, reports, and supporting objects such as macros. In Objective 1.1 from MOS 2016 Study Guide for Microsoft Access, learn how to create a database from a template, and import objects and data.

Objective 1.1: Create and modify databases

When you start Access without opening a recently used database or double-clicking a database file, the program opens to its startup screen. The startup screen displays a list of recent files and a set of thumbnails for templates on which you can base a variety of desktop databases or Access web apps (a type of database stored in the cloud). Access also provides an option for creating a blank desktop database or a custom (blank) web app.

g01ac01.jpg

Click to view larger image

Database templates such as the Task Management template provide a set of database objects you can build on

This topic provides details about how to create a blank desktop database, how to create a database from a template, how to import data to build a database, and how to delete a database object.

Create databases

Access databases are made up of database objects: tables, queries, forms, reports, and supporting objects such as macros. Templates provide some or all of the database objects you need to manage the type of data the template is designed to support. When you create a blank database, Access provides a single table by default. You can set up and define other database objects to expand the databases you create. By default, Access names new database files by using Database n, where n is a number such as 1 or 2. You can enter a more descriptive name when you create the database.

Access provides templates for desktop databases and for what Access calls SharePoint web apps. (The thumbnails for web apps display a globe.) When you work with an Access web app, you work in a web browser, but you design and modify the web app in Access. You can share the data in a web app by using an instance of SharePoint.

A blank desktop database opens with the Navigation Pane open. In a blank database, Access creates a default table, called Table1, which serves as a starting point. Access displays the default Table1 in what Access refers to as Datasheet view. When a table is displayed in Datasheet view, you can define field names and data types and insert records. You can also display a table in Design view. In Design view, you work directly with the structure of the table (the table’s field names and properties) instead of with the records stored in the table.

g01ac02.jpg

Click to view larger image

The Navigation Pane and Datasheet view of the default table in a blank desktop database

The database templates represented by the set of thumbnails that appears on the startup page are not the only database templates you can use. At the top of the startup window is the search box, with the prompt “Search for online templates,” and just below the search box are suggested search terms. You can search by using one of the suggestions or enter the search term you want to use in the search box to locate other templates that might be available.

When you select a thumbnail for a database template on the startup screen, Access displays a window that provides a description of the template.

g01ac03.jpg

Click to view larger image

Use the arrows that appear to the left and right of this window to browse through the set of templates

In most cases, when the new database opens, Access displays a table or opens a form for data input. Other objects in the database appear in the Navigation Pane. The Task Management desktop template, for example, includes tables that define records for contacts and tasks. This template also includes several queries used to analyze the data, forms for working with tasks and contacts, and several reports.

To create a database from a template

  1. On the startup screen or the New page of the Backstage view, click the thumbnail for the template you want to use.

  2. In the File Name box, enter a name for the database.

  3. If you want to store the database in a location other than your Documents folder, do the following:

    1. Click the folder icon to the right of the File Name box.

    2. In the File New Database dialog box, navigate to the folder where you want to store the database.

    3. Click OK.

  4. Click Create. Access downloads the template if necessary, and then creates and opens the new database.

  5. If an Info bar below the ribbon displays a security warning, click Enable Content.

Import database objects and data

Whether you start with a blank database or base your database on a template, you can add some or all of your records by importing data. You can also define part of the structure of the data by, for example, using column headings in a spreadsheet as field names in a new table. Data sources you can use include Excel workbooks, other Access databases, text files, XML files, Microsoft SharePoint lists, and Microsoft Outlook folders.

When you import data, you generally have three options: importing the source data into a new table, appending the data to a table that’s already defined, or linking to the data source to create a linked table. When you are importing objects and data as part of creating a database, you use the first of these options in most cases. Access often provides wizards that help you provide the information Access requires to import data from a specific format.