Tour of Access

1
Chapter 1

• Tour of Access

2
Chapter Objectives

• Start and exit Microsoft Access
• Open and run an Access application
• Identify the major elements of the Access
  programming environment
• Explore the menus and the Object Bar associated
  with the Database window

3
Chapter Objectives

• Explore the Design view and other views of forms,
  pages, reports, tables, queries, macros, and
  modules
• Define key Access concepts such as events,
  procedures, methods, modules, and properties
• Invoke Access Help
• Learn the different ways in which Access
  databases can be incorporated into applications

4
Introduction to the MU-DSci Application

• Database management system (DBMS)
• Vital component of an organizations software
  collection
• From the perspective of an end user, it
  simplifies the storage, retrieval, and updating
  of data
• Supports the day-to-day operations of a business
  and provides important information needed for
  decision making

5
Preparing MU-DSci for Use in Access

• Table
• Fundamental structure of a relational database
  management system (RDBMS)
• Stores data in records and fields
• Records (rows)
• Contains all information related to one entry
• Fields (columns)
• Contains a specific item of information

6
Preparing MU-DSci for Usein Access

• Refreshing linked tables
• Required whenever you move an Access application
  to a different directory

Figure 1-1 The Linked Table Manager
7
Preparing MU-DSci for Use in Access
Figure 1-2 MU-DSci database window
8
Exploring the MU-DSci Application as an End User

• In general, windows that display information on
  the monitor are called forms
• An opening form often contains command buttons
  that, when clicked, display other forms
• Switchboards
• Forms that open other forms

9
Exploring the MU-DSci Application as an End User
Figure 1-3 MU-DSci switchboard
10
Exploring the Database Window

• Database window
• Organizes the objects used in the application
  into seven sections that, generally, correspond
  to the input, output, storage, and processing
  framework
• Central area that gathers the objects making up
  an Access application

11
Exploring the Database Window
Figure 1-7 Input, process, output, and storage
objects
12
Exploring the Database Window
Table 1-1 Commands in the database menu
13
Exploring the Database Window

• Startup window
• Used to specify a default application menu, the
  status of the Database window, the icon
  associated with the application, and the wording
  of applications title bar

Figure 1-8 Startup window
14
Exploring Tables

• Tables can be displayed in two views
• Datasheet view
• Represents a standard way to view the rows in a
  table
• Fields in the table represent attributes that
  describe the item being represented

15
Exploring Tables
Figure 1-9 Datasheet view of the tblProspects
table
16
Exploring Tables

• Design view
• Allows you to modify the tables structure

Figure 1-10 Design view of the tblProspects table
17
The Jet Database Engine

• Jet database engine
• Component of Access that is responsible for
  storing and retrieving data
• Open Data Basic Connectivity (ODBC)
• Standard communications protocol that allows an
  application to retrieve and store information in
  different DBMSs
• Most DBMSs supply their on ODBC-compliant drivers

18
Exploring Queries
Figure 1-11 Relationships among Access, the ODBC
drivers, and the Jet database engine
19
Exploring Queries

• Queries
• Statements that retrieve or update data in the
  database tables
• Select queries
• Queries that retrieve data that can select
  particular rows and fields from a table, combine
  data from multiple tables, and perform
  calculations that summarize a tables data
• Action queries
• Insert, delete, or update data in the database

20
Exploring Queries

• Datasheet view of a query
• Displays the information that satisfies the
  querys specification
• Design view of a query
• Specifies the columns and rows that should be
  retrieved

21
Exploring Queries
Figure 1-12 Design view of the
qryAdmittedProspects select query
22
Exploring Queries

• Structured Query Language (SQL)
• Standard query language supported by relational
  databases
• SQL view of a query
• Displays the SQL statement that implements the
  query

23
Exploring Forms

• Forms are divided into three sections
• Form Header
• Contains controls that remain on the top of the
  form
• Detail section
• Contains the data shown on the form
• Form Footer
• Displays information that stays at the bottom of
  a form

24
Exploring Forms

• Controls
• A forms contents including buttons, list boxes,
  text boxes, labels
• Access allows you to draw them directly on the
  form
• Chosen from the toolbar to add instances of them
  to the form

25
Exploring Forms
Figure 1-13 Design view of frmProspects and the
txtFirstName property sheet
26
Exploring Forms

• Text box
• Displays the current value of some piece of
  information
• Property sheets
• Allow you to modify many of the characteristics
  of a control
• Form selector
• Box where the rulers meet in the Design view

27
Exploring Forms
Figure 1-14 Form property sheet for frmProspects
28
Adding Controls to Forms

• You can create a command button to add to the
  copy of the switchboard on your own, or you can
  have Microsoft Access create it for you by using
  a wizard
• Wizard
• Speeds up the process of creating a command
  button because it handles all of the basic work
  automatically

29
Exploring Data Access Pages

• Data access pages
• New in Access 2000
• Allows you to manipulate data via the Internet or
  an intranet
• An intranet is a network of networks that can be
  accessed only by certain people

30
Exploring Data Access Pages

• On pages, a Body object is divided into two
  sections
• Page Header section
• Page Navigation section
• HeadingText object
• Static text object where a label can be created
  for the page
• BeforeBodyText object
• Smaller static text object that can hold
  additional information

31
Exploring Data Access Pages
Figure 1-15 pagProspects data access page
32
Adding Text Box Controls to Pages

• Access provides a Field list for pages in the
  same manner that it does for forms
• Field list for pages has two tabs
• Database tab
• Displays a tree structure of the tables in the
  database
• Page tab
• Displays a tree structure of the pages in the
  database

33
Design View of a Report

• Report Header and Report Footer
• Display information at beginning and end of the
  report
• Page Header and Page Footer
• Appear at beginning and end of each page,
  respectively
• Detail section
• Contains the data supplied by reports Record
  Source
• Record Source
• Indicates the query or table that provides the
  needed data

34
Exploring Reports

• Sorting and Grouping
• Allows you to specify groups into which to
  classify data
• Enables you to add, delete, and change the fields
  or expressions on which groups are based, the
  sort order, and the group properties such as
  headers and footers

35
Exploring Reports

• Report selector
• Allows you to select all objects on the report at
  one time
• Reports can be displayed as
• Print Preview
• Displays all data in the reports Record Source
• Layout Preview
• Displays only a sample of the data

36
Exploring Macros

• Macro
• Named set of actions that manipulates the objects
  and properties of the Access application
• OpenForm action
• Displays a form
• SetValue action
• Changes the value of some property

37
Macro Window

• Action column
• Identifies the action to be performed
• Comment column
• Used for documentation
• Conditions column
• Contains an expression that can be evaluated as
  True or False
• Macro Names column
• Used to write several macros in same Macro window

38
Exploring Macros
Figure 1-18 mcrProspect Macro window
39
Exploring Macros

• Most actions require values to be placed in the
  Action Arguments section of the Macro window
• Object Type argument
• Indicates that the move occurs on a form
• Object Name argument
• Identifies the specific form involved

40
Using Macros to Open a Different Form at Startup

• Modules are different from macros in that they
  are written in the Visual Basic for Applications
  (VBA) programming language
• More capable language, so modules can accomplish
  more complex tasks within Access

41
Exploring Modules

• Module
• Collection of VBA declarations and procedures
  stored together as a unit
• Procedures
• Use and manipulate the objects of an Access
  application
• Declarations
• Name a variable, constant, or procedure
• Specify a data type for use in an Access
  application

42
Exploring Modules

• Standard modules
• Contain subroutines and functions and can be run
  at any time, as long as the application is open
• Class modules
• Independent class modules
• Invoked at any time while the application is open
• Form and report class modules
• Opened or invoked only when the form or report is
  open

43
Exploring Modules
Figure 1-20 Module window
44
Exploring Modules

• Procedure list box
• Displays all of the procedures within a module
  that are associated with a certain object
• Object list box
• Names the object
• Objects have properties that can be manipulated
  through macros and modules, which are themselves
  triggered by events that occur in the application
• For this reason, the Access environment is called
  object-based and event-driven

45
Application Development Help

• Expression Builder
• Supports the development of various types of
  Access expressions that can be placed in the
  control source on forms, in macros, and in
  procedures

Figure 1-21 Expression Builder
46
Application Development Help

• Access Help menu
• Similar to the Help menu associated with any
  other Windows application
• Provides steps to accomplish a particular goal,
  advice, object and property descriptions, legal
  syntax, and examples

47
Application Development Help
Figure 1-22 Microsoft Access Help window
48
Starting a New Database

• Active Server Page (ASP) approach
• Active Server Pages are files that contain a
  combination of HTML and VBscript code
• Derivative of Visual Basic that supports
  Web-based application development
• Microsoft Access project
• New Access file type that has been specially
  designed to facilitate access to Microsoft SQL
  Server databases

49
Starting a New Database

• Access projects take advantage of a new Microsoft
  database engine called the Microsoft Database
  Engine (MSDE)
• Provides local data storage that is compatible
  with SQL Server 7.0
• Intended to be a client/server data engine
  alternative to the Jet database engine used in
  Access
• Designed and optimized for use on smaller
  computer systems

50
Chapter Summary

• DBMS applications
• Store and retrieve the vital day-to-day
  transactions that occur in a business
• Retrieve and update information that supports
  critical decisions
• Microsoft Access
• Software tool that supports the development of
  DBMS applications
• Consists of many different objects

51
Chapter Summary

• Database objects such as tables and queries are
  managed by the Jet database engine or by some
  other ODBC-compliant DBMS
• Design view facilitates the development and
  modification of an object
• Property sheet values specify the data used by
  the object or change the objects display

52
Chapter Summary

• Macros and modules are needed to
• Create applications that automatically open forms
  in response to a user action
• Fill in portions of a form automatically
• Change the contents of a form or report in
  response to the users current needs