Home

      Hosting

     Domains

      Support

      Contacts

Microsoft Access 2003 Tutorial


1. Introduction to Databases and RDBMS

A database is a collection of files, a Database Management System (DBMS) is a program (set of programs) that stores and organizes data.  One such organization method is relational where data is organized by means of related tables called relations and hence the name Relational Database Management System (RDBMS).  RDBMS stores information in related relations (tables, subjects) which can be related using common fields.
MS Access  is a Relational Database Management System (RDBMS).  Like all database systems MS Access stores database information in a database file (MS Access uses .mdb extension), MS Access .mdb files store information about your database objects such as tables, queries, forms, macros, reports, access web pages, and modules.   An  RDBMS is used to store, retrieve and update data via facilities and mechanisms that are supported from within the database system, these mechanisms include tools to:      MS Access 2003 Book

  • view the data.

  • update the data.

  • facility to store data (Tables).

  • present data (Forms, Reports, Access Web Pages).

  • generate reports from the database dynamically (Wizards).

  • Security utilities – only authorized users can access the database.

  • retrieve subset of the data based on a defined selection criteria (Queries).  MS Access uses Structured Query Language (see query section) to support query services.

  • backing up and restoring data.

  • Facilities to share the date

MS Access 2003 supports all these features and provide objects that are used to implement each tool.  the relational database objects that are supported by MS Access 2003 are organized in a database file.  A database file is a container for organized tables, queries, forms, reports, access web pages. and modules.  A table object is the fundamental structure that is used to store information about subjects related to your application (Students, Courses, Instructors, Employees, etc.). A Query object allows retrieving a subset of the records from a table or tables. A Form object allows you to create Graphical User Interface (GUI) for accessing your information including adding, updating, deleting, and viewing your data. A report object allows you to print information, calculate total among other things.  Pages object  allows us to generate html files that are ready to be published on the web. Macros object allows us to create individual macro actions in response to events (close a form, open form, run a module, etc.). Modules object allows us to store Visual Basic Code (VBA) to carry certain tasks such as open a form, update data, trap errors.  In this tutorial we will examine all these objects and show you how to develop a reliable database applications using MS Access 2003. 
RDBMS uses storage and retrieval  methods that are based on mathematics which makes RDBMS more reliable than other DBMS systems.  RDBMS uses the following languages:

  • Data Definition Language (DDL) for defining and organizing tables and their relationships.

  • Data Access Language (DAL) to retrieve and update stored data.

  • Structured Query Language (SQL) is used to support quires (answer questions about the data).

DDL, DAL and SQL are usually supported form within the database faculties via a GUI to assist building the language commands.  MS Access supported for example the query builder view to build your commands.


 

2. MS Access Environment

MS Access 2003 Environment

Figure 1 MS Access 2003 environment

when you first open MS access there will be no objects loaded. Once you create at least one table you will be able to add other objects such as forms, reports, data access pages or queries.  As shown in Figure 1 from the task pane you can open existing database files, create new database file, use existing database file to create a new database, or use MS templates to create new database.  The task pane also lists the most recently opened files and common tasks.

2.1 Database Window

the database window is made of a set of panes.  The object bar pane lists buttons for all database objects (tables, queries, forms, reports, macros, data access pages, and modules).  Once one object pane is selected (tables for example) a set of options related to that object  appear in the group bar.  For example Figure 2 shows the database window with the tables object selected and hence we see create table in design view, create table using wizards and create table by entering data options as they relate to the tables object.  We also see al existing tables.  the database tool bar also changes depending on which object is selected.

Figure 2 - MS Access 2003 Database Window

3. MS Access 2003 Objects

In this section we will cover all access objects in details.  Section 3.1 discusses the tables object

3.1 Tables Object

    RDBMS stores information about subjects in tables called relations (each relation contains information about one subject, i.e., Students).  Tables are the foundation for any database management system.  Tables are used to store data values and information about subjects.  For example we can create a table that stores information about students, customers, products, orders, books, etc.  In MS Access 2003 a table consist of rows and columns of related data. A row also referred to as record is a set of related data values.  For example in Fig 1 the set {355,John Smith,2425 E. Ohio,3.9,Graduate,(630) 923-9945} represents one row (or record) in the Students table.  Columns on the other hand contain information about a unique attribute of one entity (StudentID, StudentName, StudentGPA). Columns are refereed to as fields which are identifiers for individual pieces of data, each column represent one attribute. A table is made of rows (records) and columns (fields). Referring back to Fig 1 the StudentName field identifies the name of the student.  RDBMS is a collection of related tables (also refereed to as relations). Each relations is identified by its key attribute. A key is a field in the relation that uniquely identifies an entity (for example StudentID is a key in the Student relation as it uniquely identifies a student).

Figure 3: Table Basics

Tables are collection of records about a particular subject (i.e., Student).  Each record in the table is uniquely identified by the primary key of the table.  Primary Keys are fields in the table that uniquely identifies a row (record, one student).  For Example, StudentID is the primary key of the Students table and hence each student must be assigned a unique identification number such his or her social security number (note that no 2 students can have the same ID ever).  Microsoft Access 2003 uses  Button image to denote primary fields.  Microsoft Access 2003 also provides an AutoNumber field for keys that access automatically assigns unique value for each record. if and when more than one field is required to uniquely identify a record in a table then we need a Composite key.  a Composite key is a primary key that is made of 2 or more fields {first name, last name}.

Table Definitions:

  • Primary key Button image, are fields in the table that uniquely identifies a record. One or more fields whose values uniquely identify each record in a table. Null values are not allowed in a primary key and a primary key must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.

  • Foreign Key, One or more table fields that refer to the primary key field in another table. A foreign key indicates how the tables are related.

  • Candidate key, if a field could uniquely identify a record then its called a candidate key.

  • Composite key, is a primary key that is made of 2 or more fields.

  • Domain, all entries in table for one attribute belong to the same domain which is a list of all possible values of an attribute.  For example US states is the domain for a states field.

  • Indexes Button image ,  A feature that speeds up searching and sorting in a table based on key values and can enforce uniqueness on the rows in a table. The primary key of a table is automatically indexed. Some fields can't be indexed because of their data type such memo fields.

Relationships

relationships are used to extract information form multiple tables (i.e., to join one more tables).  Primary and forign keys are used to join tables in one of the following forms of relations:
1. one-to-one relationship, when one record in the first table relate to one and only one record in the related table (foreign key).
2. one to many relationship, when one record in the primary table is related to many records in the related table.  this is the most common relationship.  For example there is a one to many relationship between Student table and the Courses table since each student can register for multiple classes.
3. many to many relationship, when many records in one table are related to many records in the related table.  For example table Orders has a one to many relationship with table Orders.  MS Access does not support many-to-many relationship , we can implement it in access by breaking the relationship into two one to many relationships using Junction tables.  Junction tables are tables that are used to to link two tables in a many-to-many relationship by including the primary keys from both tables.

Relationships Definitions

  • Referentional Integrity, when all values in related tables have a primary values in the primary table.

  • Cascading updates, a feature that is supported by MS access that is used to preserve the Integrity of the data (Referentional Integrity).  For example updating a record in the Products table results in cascading the updates all records in the Orders table.

  • Cascading Deletes, a feature that is supported by MS access that is used to preserve the Integrity of the data (Referentional Integrity).  For example deleting a record in the Products table results in cascading the delete of  all records in the Orders table.

3.1.1 Microsoft Access 2003 Table Views

Microsoft Access provides multiple views to work with tables that are custom to your particular task.  For example if you are trying to edit records and modify information in the table then you can work with the Datasheet view to directly enter, update and delete data value from the table.  To work with the design of a table such as changing field type and size or adding a primary key to your table you work with the Design View.

3.1.1.1 Datasheet view

Datasheet view is a window that displays data from a table, form, query, view, or stored procedure in a row-and-column format.  Datasheet view is used to enter, edit, search, filter and update data.  Figure shows the Datasheet view for the Customers table. 

Figure 4 - Datasheet view

The Table Datasheet and Query Datasheet toolbars provide many of the tools you need to work with records (search, edit, delete, add, and print)

Datasheet toolbar

Print or preview data.
Check spelling.
Cut, copy, or paste selected text, fields, whole records, or the entire datasheet.
Sort records.
Filter records, and find or replace values.
Add or delete records.

Working with columns, rows, and subdatasheets
By right-clicking a column selector you can find tools for working with columns, rows, and subdatasheets. A subdatasheet is a datasheet that is nested within another datasheet and that contains data related or joined to the first datasheet.

Moving through records

You can use the navigation toolbar to move through the records in a datasheet.

Record navigation buttons
Go to first record.
Go to previous record.
Type a record number to move to.
Go to next record.
Go to last record
Go to a new record.

3.1.1.2 Design View

Using the design view you can change the structure of the table.  For example you can add new field to the table, update the field validation rules, delete a field or change the length of a field.  The figure below shows this view.  The table design below show the field properties properties of the CustomerID field (field size, Format, Input Mask, Caption, Default values, Validation rules, Validation Text, Required, Allow Zero Length, Indexed, Unicode, Compression, IME, Mode, IME Sentence Mode, and Smart Tags).  Field properties might look different depending on the field type.  For example a memo type field properties is different that a Number type.  Read on for detailed discussion of the each field type and its properties.

Figure 5 - Design View

from the field name cells (Area marked with 1 in Figure 5) you can add more fields or rename a field.  From the Field type cells (Area marked with 2 in Figure 5) you can set a field's data type that defines what kind of values you can enter in a field (domain). For example, if you want a field to store numerical values set its data type to Number (Integer, Long Integer, Double) or Currency.  Clicking on the Button image  form the database tool bar sets that field as a primary key.
Fields Data Types
Access supports the following data types

Data type

Description

Example

Text

A Text field can store any character or number up to 255 characters.

Student Name

Number

A Number field can store numbers.

Student GPA

Date/Time

A Date/Time field stores a date and a time.

Graduation Date

Currency

A Currency field stores currency values, numbers, and decimal-place formatting.

Student Balance (Tuition)

AutoNumber

An AutoNumber field stores serial or random numbers assigned by Access, can't be updated.

Student ID

OLE Object

An OLE object field stores objects created by programs other than Access and linked to or embedded in an Access table.

Student Photo

Field properties
Field properties are a set of characteristics that provide additional control over how the data in a field is stored, entered, or displayed. Which properties are available depends on a field's data type.
 

Field Property

Description

Notes

Field Size

The Field Size property sets the maximum number of characters in a field.

Student Name cannot be longer than 50 characters.

Format

The Format property specifies how the field should be displayed and printed.

Graduation Date will be displayed as "May 1, 2006".

Input Mask

The Input Mask property specifies a pattern for data entry into a field.

Zip code field can be entered as follows: #####-####..

Caption

The Caption property provides the label for a field used on a form or report.

The field's label is "Employee's hire date".

Default Value

The Default Value property provides the default information for all new records.

The order date is always today's date unless edited.

Validation Rule

The Validation Rule property validates data entered by users before saving data.

The quantity ordered must be greater than 1.

Validation Text

The Validation Text property displays a message when data is rejected as invalid.

"Please enter an order quantity greater than 1."

Required

The Required property defines a field as requiring data for the record to be complete.

A purchase order must have a ship-to address.

Allow Zero Length

The Allow Zero length property allows a record to be complete with a field that contains Null (no data). Only Text, Memo, and Hyperlink fields can accept zero-length strings

Student email address, and some records will not contain any data in that field.

Indexed

The Indexed property speeds up searching and can ensure that data is unique.

Frequently used data items, such as Student First and Last name.
 

3.2 Queries Object

 

 

| Home | Hosting | Domains | Support |  Contacts |
| Terms & Condition | Privacy Policy | Java 5 Tutorial | MS Access 2003

 

Copyright © 2005 by HostItWise.com Read our Copyright. All rights reserved.