Microsoft Access Course

1
Microsoft Access Course

• 1. Introduction to the user interface

2
Course Objectives

• Introduction to main features of Access
• Elements of Access IDE
• Simple design of tables, queries, forms and
  reports
• Development of small application

3
Main features of Access

• Designed for
• advanced end-user
• small system database applications
• rapid prototyping
• front-end client server applications

4
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

5
Overview of Access IDE

• Main database window
• Shows available tools for database design
• Each element has optionsOpen (or
  Run)DesignNew

6
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

7
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

8
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

9
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

10
Table Design 1

• Design and datasheet modes
• Each table column defined by name and type
• Optional formatting, validation, indexing

11
Access Table Design Dialogue
Datasheet view
Table design screen
12
Table Design 2

• Field names
• Use letters and numbers and underscores
• Spaces allowed but creates more work later
• Capitalised format popular CustOrderNo

13
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

14
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

15
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

16
Query Design - dialogue
17
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

18
Query Design interface
Design view
Datasheet (execute) view
SQL view
19
Form Design
20
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.

21
Form display formats
Tabular, continuous form
Columnar, single form
controlled by form Default View property
22
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.
23
Report Design
24
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.

25
Report Design
Access only produces printed reports. The preview
screen shows only one page of the output.
Preview
Design View