Introduction to Microsoft Access

1
Introduction to Microsoft Access

• Overview

2
Introduction

• What is Access?
• A relational database management system
• What is a Relational Database?
• A relational database system creates separate
  containers of logically related data, and stores
  each container in a separate table.
• Tables are the key objects of a relational
  database
• Tables form the basis for information storage and
  retrieval
• A collection of tables that are related to one
  another form a database

3
Front-end Back-end

• Database can be split into Back-end and Front-end
  database
• Back-end contains the data tables
• Front-end contains forms, reports, data access
  pages and other components
• Splitting allows interface components to be
  replaced or modified without disturbing data in
  tables
• Application development independence

4
Access Objects

• Objects are the structures you create and methods
  you employ to store, manipulate and retrieve data
• Objects include
• tables
• queries
• forms
• Reports
• Pages
• Macros
• modules

5
Tables

• Data is entered into Tables
• Tables hold all stored data
• none of the other Access objects hold data.
• Two dimensional
• rows (records)
• columns (fields/attributes of interest)
• Row ordering is unimportant,
• rows can be sorted and rearranged and not change
  the fundamental table information
• Column ordering is unimportant,
• A table column may be placed in any particular
  position

6
each column holds a different characteristic
(attribute) about the row (record) it describes
the primary key Attribute(s) that makes each
row(record) unique
7
Queries

• Data stored in tables can be filtered and sorted
  in queries
• Data filtered and sorted by query can be
  displayed
• Data filtered and sorted by query can be printed
  in reports

8
Query Types

• Selection (projection) queries.
• Select queries are the most common type.
• They pose questions of the database and return
  answers in a dynaset(a virtual table)
• Subsets of rows(records) are returned when
  selection criteria are specified to filter the
  data
• All attributes for each record are also returned
• Projection returns a subset of records and a
  subset of attributes for each record

9
Product Category table
10
Selection Query
Selection Criteria
11
Resulting Dynaset
12
Projection Query
13
Resulting Dynaset
14
Action Queries

• Alters(modifies) data in a table
• Add data records to existing table from another
  table
• Delete records in a table
• Update records in a table
• Creates new table using data from an existing
  table(s)

15
Forms

• Provide a way to view table data one row at a
  time
• Easier data entry
• Easier data editing and modifications
• Easier validation checks on entered data
• Display data from tables or queries but do not
  actually hold data

16
Datasheet view of Customer Table
17
Form View for Data entry
18
Reports

• Used to preview and print data for distribution
• Provide formatted, hard-copy output
• Displays database information that can be
  supplied by
• tables,
• queries, or
• both
• Can not be used to modify data

19
Example report
20
Example report
21
Macros

• Collection of actions that enable user to perform
  simple database tasks using
• Keyboard or
• mouse
• Macros for most part have been replaced by VBA
  code
• Macros useful for creating
• Database start-up routines
• Hot keys

22
Modules

• Used to perform database actions
• More powerful than macros
• Create sophisticated database applications

23
Example Startup Module
24
Data Access Page

• Used to work with live data in the database via
  the
• Internet or
• Intranet

25
Access 2k3 database Window
comments about each table
tables, queries, and other database objects on
the Objects bar
list of tables in the database
26
Access 2k7 database Window
Click on a specific database object to get a list
of items within the objects
27
Access 2k7
List of all tables in the database
28
Creating Access Database Objects(tables,
queries, forms , reports)

• Design View
• Create object from scratch
• Wizards
• Simplify initial creation of object
• In many cases it is easier to use wizard to
  create the object, and make any modifications to
  the object created by the wizard in design view
• Table wizards have been replaced in 2K7 with
  table templates
• Templates are not as good as the wizards

29
Naming Conventions

• Hungarian Notation(or derivation such as LNC) is
  a common naming Convention for Access databases
• Prefix(es)
• Tag
• BaseName
• SuffixQualifier
• Using naming convention for database objects
  makes database self-documenting

30
Naming Conventions Examples

• Object

• Tag, BaseName, Qualifier

• Form
• Query(any type)
• Query update
• Report
• Table

• frmCustomers
• qryCustomerByState
• qupdCustomer
• rptCustomerByState
• tblCustomers