with microsoft access 2010© 2011 pearson education, inc. publishing as prentice hall1 powerpoint...

with Microsoft Access 2010 © 2011 Pearson Education, Inc. Publishing as Prentice Hall 1

PowerPoint Presentation to AccompanyGO! with Microsoft® Access 2010

Chapter 10Administering Databases

with Microsoft Access 2010 © 2011 Pearson Education, Inc. Publishing as Prentice Hall 2

Objectives

• Compact and repair a database

• Back up a database

• Convert databases to an earlier version of Access

• Replicate and synchronize a database

with Microsoft Access 2010 © 2011 Pearson Education, Inc. Publishing as Prentice Hall 3

Objectives

• Use Microsoft Access analysis tools

• Add Action Tags

• Modify Access views and behaviors

with Microsoft Access 2010 © 2011 Pearson Education, Inc. Publishing as Prentice Hall 4

Compact and Repair a Database

• Database files should be rebuilt routinely– Ensures optimal performance

• Database file size increases rapidly– Especially as forms, queries, or reports are

modified or deleted– Database file size does not decrease when

objects or records deleted

with Microsoft Access 2010 © 2011 Pearson Education, Inc. Publishing as Prentice Hall 5

Compact and Repair a Database

• Compact & Repair– Process in which an Access file is rewritten– Stores objects and data with less wasted

space

• Access database files commonly measured in bytes

with Microsoft Access 2010 © 2011 Pearson Education, Inc. Publishing as Prentice Hall 6

Compact and Repair a Database

• A byte typically stores a single character– Such as a digit or a letter

• Bytes used to measure– Storage sizes on disks– Computer memory

• A kilobyte (KB) stores 1,024 bytes

with Microsoft Access 2010 © 2011 Pearson Education, Inc. Publishing as Prentice Hall 7

Compact and Repair a Database

• When an Access database is opened– A temporary file is opened

• When an Access database is closed– The temporary file is deleted

with Microsoft Access 2010 © 2011 Pearson Education, Inc. Publishing as Prentice Hall 8

Back Up a Database

• Data loss is the unexpected failure of information

• Data loss caused by such actions as– Hardware malfunction– Accidental deletion of records– Theft– Natural disaster

with Microsoft Access 2010 © 2011 Pearson Education, Inc. Publishing as Prentice Hall 9

Back Up a Database

• Lost data often recovered through copy of database

• Backing up is the process of creating a copy of a file or files

• Organizations typically make backups daily

with Microsoft Access 2010 © 2011 Pearson Education, Inc. Publishing as Prentice Hall 10

Back Up a Database

• Backing up files also done– Before making database design changes– Before performing maintenance tasks

• Collection of backup copies used– To recover data– To provide a historical record of transactions

with Microsoft Access 2010 © 2011 Pearson Education, Inc. Publishing as Prentice Hall 11

Back Up a Database

• Backup is a copy of an original file– Should be stored in an alternative location

with Microsoft Access 2010 © 2011 Pearson Education, Inc. Publishing as Prentice Hall 12

Back Up a Database

• Storing backup copies on same computer:– NOT recommended– Can result in loss of both original and

backup after data loss event

• Store backup copies in different location from original– Can be stored on removable media

with Microsoft Access 2010 © 2011 Pearson Education, Inc. Publishing as Prentice Hall 13

Convert Databases to an Earlier Version of Access

• Microsoft Access 2010 supports different database formats

• Database file formats supported by Access 2010– Prior to 2002-2003 file format– 2002-2003 file format– 2007 file format

with Microsoft Access 2010 © 2011 Pearson Education, Inc. Publishing as Prentice Hall 14

Convert Databases to an Earlier Version of Access

• If version of database file is prior to Access 2002-2003 format– Must be converted to Access 2010 format

to open– Features found only in prior version will not

be supported by Access 2010• Convert to Access 2002-2003 format if earlier

features needed

with Microsoft Access 2010 © 2011 Pearson Education, Inc. Publishing as Prentice Hall 15

Convert Databases to an Earlier Version of Access

• If version of database file is Access 2002-2003– Features supported by Access 2002-2003

added to Ribbon

• If version of database file using features new to Access 2007 or 2010– Database cannot be converted to 2002-

2003 format

with Microsoft Access 2010 © 2011 Pearson Education, Inc. Publishing as Prentice Hall 16

Convert Databases to an Earlier Version of Access

with Microsoft Access 2010 © 2011 Pearson Education, Inc. Publishing as Prentice Hall 17

Convert Databases to an Earlier Version of Access

• Application title bar displays Access file format used

with Microsoft Access 2010 © 2011 Pearson Education, Inc. Publishing as Prentice Hall 18

Replicate and Synchronize a Database

• Replica is a special copy of a database– Any changes made in replica database

sent to original database and any other replicas

• Replication is a process of creating a replica

with Microsoft Access 2010 © 2011 Pearson Education, Inc. Publishing as Prentice Hall 19

Replicate and Synchronize a Database

• Replication enables several users to work with a database without connecting to organization’s network– Can send changes to the master (original)

database when do connect to original database

• Replication supported by 2002-2003– Not supported in Access 2007 or 2010

with Microsoft Access 2010 © 2011 Pearson Education, Inc. Publishing as Prentice Hall 20

Replicate and Synchronize a Database

• Replica set composed of original database and all replicas– Design Master is the original database in

replica set– Design changes made in Design Master

only– Changes made to Design Master passed

on to each replica

with Microsoft Access 2010 © 2011 Pearson Education, Inc. Publishing as Prentice Hall 21

Use Microsoft Access Analysis Tools

• Table Analyzer searches for repeated data in table– Then splits the table into two or more

tables• Tables smaller and related

– Avoids redundancy

• Table Analyzer Wizard used to split tables

with Microsoft Access 2010 © 2011 Pearson Education, Inc. Publishing as Prentice Hall 22

Use Microsoft Access Analysis Tools

• Performance Analyzer used to identify and fix database problems

• Performance Analyzer Wizard– Analyzes database objects– Offers suggestions for object improvement

with Microsoft Access 2010 © 2011 Pearson Education, Inc. Publishing as Prentice Hall 23

Use Microsoft Access Analysis Tools

• Indeterminate relationship between two tables– Does not enforce referential integrity

with Microsoft Access 2010 © 2011 Pearson Education, Inc. Publishing as Prentice Hall 24

Use Microsoft Access Analysis Tools

• Performance Analyzer provides three levels of suggestions– Recommendation– Suggestions– Ideas

with Microsoft Access 2010 © 2011 Pearson Education, Inc. Publishing as Prentice Hall 25

Use Microsoft Access Analysis Tools

• Performance Analyzer dialog box

with Microsoft Access 2010 © 2011 Pearson Education, Inc. Publishing as Prentice Hall 26

Use Microsoft Access Analysis Tools

• Dependencies between objects– Shown in Object Dependency pane

• A dependency is where– An object requires another database object

OR– An object depends on another database

object

with Microsoft Access 2010 © 2011 Pearson Education, Inc. Publishing as Prentice Hall 27

Use Microsoft Access Analysis Tools

• Dependency example– Several queries may depend on a single

table

with Microsoft Access 2010 © 2011 Pearson Education, Inc. Publishing as Prentice Hall 28

Use Microsoft Access Analysis Tools

• Database Documenter builds a report– Contains detailed information about all

database objects

• Data dictionary is the report created– Should be printed periodically– Saved in a safe place– Displays report in Print Preview

with Microsoft Access 2010 © 2011 Pearson Education, Inc. Publishing as Prentice Hall 29

Add Action Tags

• Action tags enable Access integration to another program– Can perform tasks in Access without

opening the other program– The other program used, like Outlook,

must be installed on computer

with Microsoft Access 2010 © 2011 Pearson Education, Inc. Publishing as Prentice Hall 30

Add Action Tags

• Action Tags dialog box

with Microsoft Access 2010 © 2011 Pearson Education, Inc. Publishing as Prentice Hall 31

Modify Access Views and Behaviors

• Access has many options– Affect database views and behaviors

• Options changed through Access Options dialog box– How windows displayed– How text is formatted– Many more options

with Microsoft Access 2010 © 2011 Pearson Education, Inc. Publishing as Prentice Hall 32

Modify Access Views and Behaviors

• Access Options dialog box permits customizing the Quick Access Toolbar

with Microsoft Access 2010 © 2011 Pearson Education, Inc. Publishing as Prentice Hall 33

Modify Access Views and Behaviors

• Current Database group enables creation of the look and feel of a custom application– Example

• Create a custom application title• Application title text displays in Access title bar

with Microsoft Access 2010 © 2011 Pearson Education, Inc. Publishing as Prentice Hall 34

Modify Access Views and Behaviors

• Sample report in Report view with custom application title and icon

with Microsoft Access 2010 © 2011 Pearson Education, Inc. Publishing as Prentice Hall 35

Modify Access Views and Behaviors

• Current Database group also enables customization of the Navigation Pane

with Microsoft Access 2010 © 2011 Pearson Education, Inc. Publishing as Prentice Hall 36

Covered Objectives

• Compact and repair a database

• Back up a database

• Convert databases to an earlier version of Access

• Replicate and synchronize a database

with Microsoft Access 2010 © 2011 Pearson Education, Inc. Publishing as Prentice Hall 37

Covered Objectives

• Use Microsoft Access analysis tools

• Add Action Tags

• Modify Access views and behaviors

with Microsoft Access 2010 © 2011 Pearson Education, Inc. Publishing as Prentice Hall 383838

All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by any means, electronic,

mechanical, photocopying, recording, or otherwise, without the prior written permission of the publisher. Printed in the United States of America.

Copyright © 2011 Pearson Education, Inc.  Copyright © 2011 Pearson Education, Inc.  Publishing as Prentice HallPublishing as Prentice Hall