Transcript Microsoft Access Course

Microsoft Access Course
1. Introduction to the user interface
Course Objectives
Introduction to main features of Access
Elements of Access IDE
Simple design of tables, queries,
forms and reports
Development of small application
Main features of Access
Designed for

advanced ‘end-user’

small system database applications

rapid prototyping

front-end client server applications
Main features of Access (cont)
Features:





extensive point and click facilities
object-based construction
fully-featured DB engine technology suitable for
multi-user applications
Underlying VBA language for advanced capability
ODBC, DAO and ADO support for inter-system
communication
Overview of Access IDE
 Main database window


Shows available tools for
database design
Each element has options–
Open (or Run)
Design
New
Overview of Access IDE (cont)
Table designer

Defines database schema

Defines attributes, data types, type qualifiers,
validation, indexes
 Query designer




QBE grid: columns, conditions
SQL view: QBE equivalence
Query execution
Query storage - relational ‘view’
Overview of Access IDE (cont)
 Forms





Used to develop the users’ interface to application
Enables viewing of data from tables or queries
Form built from screen objects (text boxes, labels,
buttons) called ‘controls’
Extensive presentation facilities
Main focus of application development
Overview of Access IDE (cont)
 Reports


Used for printed reports only
Extensive facilities for grouping sub-totaling etc.
Macros



Facility for script-like commands
Principally intended for non-programming users
Not essential - macro actions can be implemented in
VBA
Overview of Access IDE (cont)
 Modules

Repository for ‘global’ modules

Most modules are part of form or report - called
‘class modules’ - scope local to form/report

Global modules accessible by any form/report
Table Design 1
 Design and datasheet modes
 Each table column defined by name and type
 Optional formatting, validation, indexing
Access Table Design Dialogue
Table design screen
Datasheet view
Table Design 2
 Field names:

Use letters and numbers and underscores

Spaces allowed but creates more work later

Capitalised format popular: CustOrderNo
Table Design 3
 Main Data types:






Text - for character information
Number - various formats - see next slide
Date - various formats
Currency
Autonumber - generates unique sequence number
in long integer format
Yes/No - boolean
Table Design 4
 Number types - set by Field Size

Byte - 8 bit positive

Integer - 16 bit

Long Integer - 32 bit

Single - 4 byte floating point

Double - 8 byte floating point
Table Design 5
 Date data-type


Permits various display formats
Best choice: Medium date - 12-Nov-01
Currency


Stored as double float
Displayed as fixed point with 2 decimals
Query Design - dialogue
Query Design - dialogue
 Design Choices:



Design view: ‘hand knit’ your query
Simple Query Wizard: allows selection of tables and
columns but no more.
Others are special cases and rarely required.
 Saved queries


Working queries can be saved
Saves queries can be used as source of forms and
reports
Query Design interface
Design view
Datasheet (execute) view
SQL view
Form Design
Form Design - new form choices
 Form design options





Design View: ‘hand knit’ your form. Needed for menu forms
and other special purposes.
Form Wizard: select input tables/queries and columns.
Necessary join queries automatically created.
Autoform Columnar: creates form with all columns arranged
vertically, one row per page.
Autoform Tabular: creates form with all columns arranged
horizontally, multiple rows per page.
Autoform Datasheet: creates a form using basic table display
format.
Form display formats
Tabular, continuous form *
Columnar, single form *
* controlled by form Default View property
Forms: Design and Form Views
Form view shows execution
of form
Design view - note ‘City’ selected for
amendment.
Note header and footer sections, unused in this
example.
Report Design
Report Design - choices
 Report design options

Design view: ‘hand knit’ your own layout - definitely not
recommended.

Report Wizard: powerful tool allows selection of source
tables/queries and fields and constructs complex structured
reports.

AutoReport: produces basic reports using all fields of source
tables/queries.

Label Wizard: facilitates generation of label printout.
Report Design
Access only produces printed reports.
The preview screen shows only one
page of the output.
Preview
Design View