The Hermit Hermit's Free Library  Database

A quick review of basic database terms such as table, record, field, etc and an overview of the MS-Access DBMS. Describes the MS-Access program window as well as the purpose and characteristics of the common MS-Access database objects, including queries, forms, and reports.

MS-Access Database Management System (DBMS)

MS-Access Program Window

Like all recent MS-Office applications, the MS-Access program window has:

Earlier MS-Access versions used a specialized dialogue box called the Database Window to show database objects such as tables, queries, etc. (see below). When Microsoft introduced the ribbon in MS-Access 2007, it also replaced the Database Window with the Navigation Pane, which performs the same function.

The Navigation pane displays each Database object (tables, queries, etc.) as an aggregate. The data content of an object is displayed in the larger Contents pane to the right of the Navigation Pane. The Contents pane becomes tabbed as multiple database objects are opened. The Navigation pane can be collapsed in order to grow the Contents pane and display more data.

MS-Access Database Objects

There are six major groups of objects associated with MS-Access databases. Each object has a specific purpose.

Tables

Tables are used to store a collection of related records. Recall that a record is the collection of information from all fields which pertains to a single entity (a person, for example), and that fields are the areas within a database which hold a specific type of information (name, date of birth, height, for example).

There are two specialized views of tables:

The Design view is used to create or modify a table's structure. A table's structure is defined by the size and data type of its fields. Modifying a table structure means to add or delete fields and make changes to their properties.

The Datasheet view, in which field contents are shown in columns and records are shown as rows, is used to display a table's data.

Datasheet view is also used to maintain data. Maintaining data means adding or deleting records and editing the information in their fields.

Queries

Queries are used work selectively with table data. Examples of selective data would include only employees who work in one particular department, or only those customers who reside in a particular state or postal code. There are different types of queries.

Select queries select only records which match one or more selection criteria. Select queries can display information contained in fields from one or multiple related tables. They can also perform and display calculations based on table data, averages, for example.

An update query is used to make changes to fields and records based upon a selection criteria. For example: increase wages by ten percent for all employees whose pay rate falls below a certain level.

A delete query is used to automatically delete records which match a selection criteria. For example, a delete query could be used to delete all customers whose most recent order was placed more than two years ago.

Like tables, queries are created and modified in design view, while their results are displayed in datasheet view. Queries can also serve as the basis for Forms and Reports.

Forms

Forms offer a safer, more convenient alternative to datasheet view for adding, deleting, and editing records. Unlike a datasheet, a form shows only one record at a time. Forms can include fields from related tables and they can be customized by changing the order and position of the fields, on-screen and in print-outs.

Reports

Reports are used to display information contained in one table or two or more related tables. They can be displayed on-screen or printed. Information on Reports cannot be deleted or modified.

Report layout is extremely flexible and can be customized in many ways. When based upon a select query, a report can show calculations such as totals and averages.

Macros

Macros are a method of recording multiple keystrokes and actions and then playing them back upon command at a later date. They are used to automate frequently-performed tasks to save time and effort and provide unfailing accuracy.

Modules

A module is a group of related programming procedures. Procedures are groups of Visual Basic statements, or commands. So modules provide developers and end users with a way to create custom programs that work with MS-Access databases and their objects.