Database Development using Microsoft Access CMPCP0015

1
Database Development using Microsoft
AccessCMPCP0015

• Lecture 10
• Switchboard, and Macro in Access
• Mengjie Yu
• M.Yu_at_2001.ljmu.ac.uk

2
Switchboard

• A Single Point of Entry into the Application
• With a list of custom activities
• For example which form or report to open
• which action to take next
• At least one switchboard for each application
• it might involve with sub-switchboard

3
Switchboard Structure
4
Switchboard Structure
Questions 1. How should we organize the
structure of switchboard? 2. Should we put
all the functions into the swithcboard?
5
Switchboard Structure

• Some Rules
• Keep the main switchboard as concise as possible
• Use the hierarchical structures
• Use sub-switchboards to catalogue functions
  into different groups
• Open sub-switchboards in the separated pages
• Put some short-cuts functions on the main
  switchboard

6
Switchboard Structure

• Use diagram to design the structure

7
Macro

• Provides a quick and easy way to program your
  Access applications
• Specify customized responses to user actions on
  the different controls
• Such as click a button, open a form, select an
  option
• Specify / set the argument (e.g. condition) for
  each Macros
• Offer an easy way to specify user interaction
  rather than Visual Basic programming

8
Macro Usage
Associated with the controls events
9
Macro Tutorials

• Three Exercises
• SetDateValue
• OpenForm
• FilterRecord !!!

10
Coursework

• Entity-Relation-Attribute Diagram (ERAD)
• The purpose of using ERAD
• Identify the potential entities from the problem
  description
• Identify the relationships among entities
• Identify / group the attributes belonging to
  each entity
• it will assist you in the normalization stage

11
Coursework

• Entity Identification
• Not all the nouns will be considered as entities
• Such as department,
• Select entities related to the on-demand
  functionalities
• Such as Staff, suppliers, products
• Entity Attributes Identification
• Group attributes

12
Coursework

• ERAD Diagram
• Entity Name
• Primary Key
• Relations

(Student_no)
m
Student
registers
1
m
Department
takes
m
Course
1
(Depat_no)
offers
m
(Course_no)
13
Coursework

• Normalization
• UF (unormal Form)
• Select the primary key for the UF
• 1NF
• Remove the repeating group
• 2NF
• Remove the partial functional dependence
• 3NF
• Remove attributes depending on non-primary key
  attributes

14
Coursework

• Physical Database Design
• Design Tables in Access
• Entities retrieved from normalization ? Tables in
  Access
• Create / Link the relationships among tables
• Create relationships in the Relationships window
• Design Queries in Access
• Associated with Report or Form Design as the
  record sources
• !!! Note Tables / Queries as record source for
  Report / Form
• Field as control source for each
  individual controls

15
Next Lecture

• Customize menu and toolbar in Access applications
• Introduction to VBA
• Some useful objects and functions
• Coursework
• Discussion on the Report and Form design