Building The Database

Chapter 2

Chapter Introduction

• Learn different techniques for:– Creating tables – Entering data – Verifying data– Relating tables– Documenting database objects – Backing up database– Repairing database– Securing data

Succeeding in Business with Microsoft Access 2010 2

Tools Covered in This Chapter

• Datasheet view• Design view• Documenter• Import spreadsheet wizard• Input mask wizard

Succeeding in Business with Microsoft Access 2010 3

• Lookup Wizard• Relationships window• Database Security• Subdatasheet

Level 1 Objectives: Creating the Database Tables

• Review the database design• Create the database tables using the database

design• Work in Design view

Succeeding in Business with Microsoft Access 2010 4

Using the Database Design to Create the Database

• Review the design to ensure correct and meets the needs

• Start Access click New tab in the navigation bar on the File tab

• Database window – Main control panel for database

Succeeding in Business with Microsoft Access 2010 5

Creating a Table by Entering Data

• Datasheet view– Shows table’s records in rows and table’s fields in

columns• Table window– Indicates table’s name

• Just start entering data• Access adds ID field to datasheet

Succeeding in Business with Microsoft Access 2010 6

Datasheet View

Succeeding in Business with Microsoft Access 2010 7

Creating a Table by Entering Data (continued)

• Click save button– Save as dialog box– Enter table name

• Change to design view– Click view button in Views group on the Home tab

Succeeding in Business with Microsoft Access 2010 8

Working in Design View

• Design view– Define table’s fields and field properties – Rearrange order of fields as necessary

• Table design grid– Top part of design view– Includes field name data type and description

columns– Each row • Field in table

Succeeding in Business with Microsoft Access 2010 9

Working in Table Design View (continued)

• Field properties pane– Bottom part of design view– Displays field properties for selected field

• Rules when naming fields and database objects– Up to 64 characters– Any combination of:• Letters• Numbers• Spaces• Special characters except ., !, `, [, and ]

Succeeding in Business with Microsoft Access 2010 10

Working in Design View (continued)

• Rules when naming fields and database objects– Cannot begin with space

• Some organizations establish standards for naming objects and fields

• Field size property – Limit number of characters to store in text field – Type of numeric data to store in number field

Succeeding in Business with Microsoft Access 2010 11

Adding Descriptions to Fields in Table Design View

• Description property – Document contents – Identify field as primary or foreign key – Provide users with instructions about entering

values into field• Primary key button

Succeeding in Business with Microsoft Access 2010 12

Creating a Table in Design View

• Click Table Design button on Create tab• Enter field names, data types and properties

Succeeding in Business with Microsoft Access 2010 13

Evaluating and Testing the Table Design

• By moving to next record – Access saves record in table– To cancel record • Press Esc key

• After designing table– Evaluate for potential problems

Succeeding in Business with Microsoft Access 2010 14

Displaying Descriptive Field Names Using the Caption Property

• Caption property – Change way field name displayed– Specifies how field name will appear in different

database objects– Default caption property • Field name for all data types

Succeeding in Business with Microsoft Access 2010 15

Formatting Field Values Using an Input Mask

• Input mask – Predefined format – Applied to field – Values are displayed using format specified– Ensures that all of necessary data entered

• Literal characters– Not stored in database– User does not need to type them

• Input Mask WizardSucceeding in Business with Microsoft Access 2010 16

Input Mask Characters and Descriptions

Succeeding in Business with Microsoft Access 2010 17

Validating Fields to Ensure Accurate and Consistent Data

• Validation rule – Compares data entered by user against one or more valid

values• Validation Rule property – Specifies valid values that users can enter

• Validation Text property– Displayed if user enters invalid value

Succeeding in Business with Microsoft Access 2010 18

Automating Data Entry by Using a Default Field Value

• Default Value property – Enters default value into any type of field – Except for AutoNumber field

Succeeding in Business with Microsoft Access 2010 19

Automating Data Entry by Using a Lookup Field

• Lookup field– Lets user select field value – From: • List of existing field values stored in database • List of values specified when lookup field

created• Change field’s data type to Lookup

Succeeding in Business with Microsoft Access 2010 20

Level 1 Summary

• Create tables by– Entering data– Using design view

• Customizing tables– Captions– Validation rules– Using lookup

Succeeding in Business with Microsoft Access 2010 21

Level 2 Objectives:Populating and Relating the Database Tables

• Create additional tables for the database• Import data into a database• Work with primary and foreign keys• Create one-to-many and many-to-many

relationships between tables• Use a subdatasheet to view related records

Succeeding in Business with Microsoft Access 2010 22

Populating the Database Tables

• Populating database– Load tables with data– Enter data in datasheets– Import data• Copy and Paste• Import from Excel workbook

Succeeding in Business with Microsoft Access 2010 23

Importing and Copying Records from One Table to Another

• Import existing data from another Access table– Table structure must be identical– Select all of records– Click copy button– Open target database table– Click paste button

Succeeding in Business with Microsoft Access 2010 24

Importing Data from an Excel Workbook

• Can import data and create table at same time– Review contents of workbook to understand how

it is arranged• If column heading names comply with rules

for naming fields in access – Access uses them as field names data imported

• If column headings absent or do not comply– Access assigns generic field names

Succeeding in Business with Microsoft Access 2010 25

Importing Data from an Excel Workbook (continued)

• Access can import most data from worksheet– Not graphics– Formulas converted to numbers– Hyperlinks imported as text data

• Usually entire worksheet imported– Can import range of data

Succeeding in Business with Microsoft Access 2010 26

Import Spreadsheet Wizard

Succeeding in Business with Microsoft Access 2010 27

Setting a Primary Key Field

Succeeding in Business with Microsoft Access 2010 28

Working with Primary and Foreign Keys

• Primary key and foreign key counterparts – Must have same data type and field size – Fields must contain identical values

• Primary key value must exist before entering corresponding record– If referential integrity set

• User cannot enter null value into primary key field• Required property– Nonprimary key field– Ensure that users enter value into field

Succeeding in Business with Microsoft Access 2010 29

Working with Primary and Foreign Keys (continued)

• Works to make data retrieval faster– Access creates index for primary key field

• Index– List maintained by database– Associates field values in indexed field with

records that contain field values

Succeeding in Business with Microsoft Access 2010 30

Creating an Index

• Increase speed at which access searches• Open table in design view– Select field to index– Click indexed property list arrow

• Create index for any field– Except fields of type• Calculated• Attachment• OLE object

Succeeding in Business with Microsoft Access 2010 31

Creating an Index for a Nonprimary Key Field

Succeeding in Business with Microsoft Access 2010 32

Creating an Index

• View indexes created in table– Click indexes button on Show/Hide group on Design tab

• Create indexes for as many fields as necessary to optimize searches in database

• Records indexed when table saved• Updated automatically as records added, deleted,

or changed • As database grows– Indexes might slow down database

Succeeding in Business with Microsoft Access 2010 33

Creating an Index (continued)

• Increases size of database • Slows down database – Must update index as users add, change, and delete

records• Add indexes as needed – When improved performance necessary

• Delete indexes to– Increase speed – Reduce file size

Succeeding in Business with Microsoft Access 2010 34

Creating One-to-Many Relationships Between Tables

• Open relationships window– Click relationships button on Database Tools tab

• Relationship has certain properties – Type• One-to-many• One-to-one• Many-to-many

– Attributes • Specify how to manage changes when records

updated or deleted

Succeeding in Business with Microsoft Access 2010 35

Relationships Window

Succeeding in Business with Microsoft Access 2010 36

Edit Relationships Dialog Box

Succeeding in Business with Microsoft Access 2010 37

Using a Subdatasheet to View Related Records

• After importing data into database– Good idea to open each table in Datasheet view – Check data for problems

• Plus box appears for relationships– Click to view subdatasheet

Succeeding in Business with Microsoft Access 2010 38

Level 2 Summary

• Import data from – Other Access tables– Excel

• Index– Used to aid searches

• Create relationships using Relationships Window

Succeeding in Business with Microsoft Access 2010 39

Level 3 Objectives:Maintaining and Securing a Database

• Learn about the role of the database administrator

• Compact repair and back up a database• Document the database design using the

documenter• Secure a database from unauthorized use

Succeeding in Business with Microsoft Access 2010 40

The Database Administrator Role

• Database administration (DBA)– Responsible for maintaining database– Sets security and other features of database

• Default option for opening database– Available to other users at same time

• Open access database in four ways– Open mode– Open read-only mode– Open exclusive mode– Open exclusive read-only mode– Show previous version mode

Succeeding in Business with Microsoft Access 2010 41

Compacting and Repairing a Database

• Compact– Data and objects reorganized– Unused spaces reassigned and deleted– Result• Decreased file size • Improved efficiency

– Can be done manually or every time database closed

• Good idea to compact before backing upSucceeding in Business with Microsoft Access 2010 42

Backing Up a Database

• Creates copy of database – Use to restore in event of loss

• Schedule database backups based on manageable amount of data loss

• Important to store copy in fireproof location offsite• Created on external media such as– CD – DVD– External hard drive

Succeeding in Business with Microsoft Access 2010 43

Backing Up a Database (continued)

• Back up database– Click File tab, Share tab, Save Database As button– Under Advanced, click Back Up Database– Opens the Save As Dialog box for location to save backup

• Default backup database name– Original database name followed by current date

• Might take few minutes to several hours to backup– Depends on database size

• Backup copy can be opened like any other Access database

Succeeding in Business with Microsoft Access 2010 44

Documenting the Database Design

• Database Documenter – Produces report of every object or just selected

objects in database• Click Database Tools tab, and then click the

Database Documenter button– Tabs at top of documenter dialog box – Select objects by type

Succeeding in Business with Microsoft Access 2010 45

Documenter Dialog Box

Succeeding in Business with Microsoft Access 2010 46

Securing a Database From Unauthorized Use

• Plan for and to prevent data loss• Protect database from unintentional or malicious

damage• DBA must open database with exclusive access

prior to setting protection features• Tools– Setting database password– Encrypting database– Hiding database objects

Succeeding in Business with Microsoft Access 2010 47

Using a Password to Protect a Database

• Password– Collection of characters that user types to gain

access to file• Database administrator sets database

password – Users cannot open database file in access – Unless they provide correct password

Succeeding in Business with Microsoft Access 2010 48

Encrypting a Database to Prevent Unauthorized Use

• If someone steals file and tries to open it with program other than Access– Password will not protect file

• Encrypting database – Converts data in database into format readable

only by Access• Click the Encrypt with Password button on the

Database Tools tab on the Info tab of the File tab

Succeeding in Business with Microsoft Access 2010 49

Hiding Database Objects From Users

• Casual users may damage database by– Unintentionally altering object’s design – Deleting object entirely

• Hide objects from being displayed in Navigation Pane– User will not be able to accidentally or intentionally

damage database• Revealing hidden object not difficult• Hide using properties dialog box

Succeeding in Business with Microsoft Access 2010 50

Level 3 Summary

• Compact database• Backup database• Document database• Secure database– Password– Encryption– Hiding objects

Succeeding in Business with Microsoft Access 2010 51

Chapter Summary

• Create tables using one of three methods• Import data from– Other Access tables– Excel

• Create relationships using Relationships Window• Secure database using:– Password– Encryption– Hiding Objects

Succeeding in Business with Microsoft Access 2010 52