Transcript Chapter Objectives - Eastern Michigan University

1
Chapter 1
Tour of Access
1
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
1
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
1
Introduction to the MU-DSci
Application
• Database management system (DBMS)
 Vital component of an organization’s 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
1
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
1
Preparing MU-DSci for Use
in Access
• Refreshing linked tables
 Required whenever you move an Access
application to a different directory
Figure 1-1
The Linked
Table
Manager
1
Preparing MU-DSci for Use
in Access
Figure 1-2
MU-DSci
database
window
1
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
1
Exploring the MU-DSci
Application as an End User
Figure 1-3 MU-DSci switchboard
1
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
1
Exploring the Database
Window
Figure 1-7
Input,
process,
output, and
storage
objects
1
Exploring the Database
Window
Table 1-1
Commands
in the
database
menu
1
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 application’s title bar
Figure 1-8
Startup
window
1
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
1
Exploring Tables
Figure 1-9
Datasheet
view of the
tblProspects
table
1
Exploring Tables
 Design view
 Allows you to modify the table’s structure
Figure 1-10
Design view of
the tblProspects
table
1
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
1
Exploring Queries
Figure 1-11
Relationships
among
Access, the
ODBC
drivers, and
the Jet
database
engine
1
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
table’s data
• Action queries
 Insert, delete, or update data in the database
1
Exploring Queries
• Datasheet view of a query
 Displays the information that satisfies the
query’s specification
• Design view of a query
 Specifies the columns and rows that should be
retrieved
1
Exploring Queries
Figure 1-12 Design view of the qryAdmittedProspects select query
1
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
1
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
1
Exploring Forms
• Controls
 A form’s 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
1
Exploring Forms
Figure 1-13
Design view
of
frmProspects
and the
txtFirstName
property
sheet
1
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
1
Exploring Forms
Figure 1-14 Form property sheet for frmProspects
1
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
1
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
1
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
1
Exploring Data Access Pages
Figure 1-15 pagProspects data access page
1
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
1
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 report’s Record Source
• Record Source
 Indicates the query or table that provides the needed
data
1
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
1
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 report’s Record Source
 Layout Preview
 Displays only a sample of the data
1
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
1
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
1
Exploring Macros
Figure 1-18 mcrProspect Macro window
1
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
1
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
1
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
1
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
1
Exploring Modules
Figure 1-20 Module window
1
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 objectbased and event-driven
1
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
1
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
1
Application Development
Help
Figure 1-22
Microsoft
Access
Help
window
1
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
1
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
1
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
1
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 object’s display
1
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 user’s current needs