Return to IFS 105 Notes!

Access
Project 3

Maintaining a Database
Using the Design and Update Features of Access

Maintaining a Database

Restructure Database

1. Add additional fields
2. Change characteristics of fields
3. adding secondary indexes

Maintaining Records

Adding, Deleting, and Changing records 
(mass updates or deletions)

General Terms

• Searching - looking for records satisfying some criteria (use Find Button (binoculars))
• Update Query - easy way to update contents of new field in table (Update Query button on toolbar)
  • easier then typing all entries
  • less mistakes

• Note - To have only the record or records that satisfy the criterion display, use a filter.
• filter by selection - simplest type of filter (single criterion)
  • to use give Access an example of the data you want by selecting the data within the table and then clicking the Filter By Selection button on the Datasheet View toolbar
• Remove Filter button - on Datasheet View toolbar used to display all records again
• Filter by Form - filter records based on values in more than one field
  • after setting the field values click the Apply Filter button

Creating Validation Rules

• Validation Rules - rules that data entered by user must follow (specify legal values)
• Validation text - message displayed if user violates validation rule
• Required Field - field in which user must enter data
• Range of Values - entry between limits
• Default Value - entry for field before user begins

Creating a Lookup Field

• Lookup field - allows user to select from a list of values
  • used to assist the user in the data-entry process
  • to change a field to a lookup field that selects from a list of values, use the Lookup Wizard data type.
  • prevents the user from typing in the wrong code for a field like dept. number

Referential Integrity

• Foreign key - is a field in one table whose values are required to match primary key of another table
• Referential Integrity - property that value in foreign key must match that of another table’s primary key (use Relationships button in Access)
• One-to-many relationship - one record in 1st table is related to many records in 2nd table

Creating and Using Indexes

• Index key - field used to index records
• Primary index - most important ordering field (Access automatically creates using primary key)
• Secondary index - order records on additional fields (Single-field, Multiple-field)

Advantages :

1. Retrieve records quicker
2. List records in different order

Create an Index 
if one or more of the following conditions are present

1. The field is the primary key of the table
   • (Access will create this index automatically)
2. The field is the foreign key in a relationship you have created
   • (Access will create this index automatically when you specify the relationship)
3. You frequently need your data sorted on the field
4. You frequently need to locate a record based on a value in this field

Backup and Recovery

• Recovery - returning a database to a correct state
  • simplest approach to recovery involves periodically making a copy of database called a backup copy or a save copy
  • to backup a live database use the Back Up Database command on the File menu
    • Access suggests a backup name that is a combination of the database name and the current date (Ashton James College_2007-04-20)

Campacting and Repairing a Database

• Compacting - removes wasted space from the database
  • compacting the database makes an additional copy of the database
    • contains same data but not the wasted space
    • can be used to repair a database in case of problems

3 step process

1. compact original database give compacted database a different name
2. if original database compacted successfully, delete original database
3. rename the compacted database to original name

• problem of not enough space can occur on a floppy disk if a large file is involved
• Steps: Click Tools on menu bar, point to Database Utilities, click Compact and Repair Database

In the Lab 2 (HOMEWORK #9)

• Page AC 169

End

Maintaining a Database Using the Design and Update Features of Access

Computer Assignment 9

Return to IFS 105 Notes!