Transcript Document

Access 2000:
Introduction to Application
Development
© 2001 ComputerPREP, Inc. All rights reserved.
Lesson 1:
Designing Applications
© 2001 ComputerPREP, Inc. All rights reserved.
Objectives
• Examine the application development process
• Preview an existing application
• Decide where to automate your application
About Access Applications
• Access application – an Access database that
contains all the objects necessary for users to
work with data, as well as specific property
settings and macros that automate data entry
tasks
• Access applications are event-driven; they
respond when a user performs an action
• Event – any action a user performs, such as
entering text, opening a form or clicking on a
button
Examining the Application
Development Process
• Three major phases of application development:
– Determine user requirements
– Design the application interface
• Involve users to identify and resolve
potential problems before building them into
the interface
• Limit the scope of each screen to a specific
task
• Limit user actions to necessary tasks
– Define the database configuration
Deciding Where to Automate
Your Application
• Create event-driven applications by writing
macros and event procedures for events to which
you want your application to respond
• Three categories of events in applications:
– Keyboard event – occurs when the user presses
a keyboard key
– Mouse event – occurs when the user moves or
drags the mouse, or clicks a mouse button
– Program event – occurs when the application
loads, opens or closes a form or report
Lesson 2:
Using Advanced Tools and
Controls
© 2001 ComputerPREP, Inc. All rights reserved.
Objectives
• Use the Performance Analyzer
• Use a combo box to find records
• Calculate values from subform controls
Using the Performance
Analyzer
• The Performance Analyzer evaluates and optimizes the
performance of objects in a database
• The Performance Analyzer lists three kinds of analysis
results:
– Recommendations
– Suggestions
– Ideas for optimization
• Access can automatically perform recommendations
and suggestions
• You must perform idea optimizations on your own
Using a Combo Box to Find
Records
• Using the Combo Box Wizard:
– Use the Combo Box Wizard to add an unbound
combo box control to a form
• Modifying the Combo Box Drop-Down List:
– Modify what displays in a drop-down list by
changing its RowSource property
– Modify the SQL statement to reflect the desired
drop-down list display
Calculating Values from
Subform Controls
• To display calculated values in a main form based
on values in a subform:
– Create a calculated control in the footer of a
subform
– Refer to the subform calculated control from the
unbound text box control in the main form
– Hide the form footer if the subform is a
continuous form by setting its Visible property
to No
Lesson 3:
Creating Dialog Boxes
© 2001 ComputerPREP, Inc. All rights reserved.
Objectives
• Set dialog box form properties
• Add dialog box controls
• Enhance dialog boxes
Setting Dialog Box Form
Properties
• Dialog box – a specialized form that displays on
top of other forms, stays open until a user closes
it, and prevents a user from accessing other
database objects while it is open
• To create a dialog box:
– Open a new, unbound form
– Specify format properties to make the unbound
form display and function as a dialog box
Creating Option Groups
• Option group – a type of control that contains a set
of related buttons that provide a limited set of
choices for the user
• An option group consists of:
– An option group frame
– Option buttons inside the option group frame
• Option buttons created outside the option group
frame and then dragged into the frame will not
become part of the option group
Creating Command Buttons
• Command buttons allow the user to execute or cancel
the current task
• Specify a default and a cancel button for a dialog box:
– Default button – a button that is selected
automatically when the user presses ENTER
– Cancel button – a button that is selected
automatically when the user presses ESC
• The default button should be the one most often
chosen by users
• The cancel button exits a dialog box without
performing any other tasks
Enhancing Dialog Boxes
• Enhance a dialog box by:
– Adding labels
– Setting a default value (default option button)
– Setting the Pop Up and Modal properties
• The default option button should be the one most often
chosen by users
• Pop Up property – specifies that the dialog box will
display on top of onscreen objects
• Modal property – specifies that the dialog box must
close before the user can access any other database
object
Lesson 4:
Using Macros
© 2001 ComputerPREP, Inc. All rights reserved.
Objectives
•
•
•
•
•
Create macros
Attach macros to events on forms
Use the Macro Builder
Create conditional macros
Create macro groups
Creating Macros
• Macro – a set of one or more actions that you can use to
automate tasks
– There are 53 pre-defined macro actions you can use to
create macros
• When you run a macro, the macro actions execute in the
order in which they appear in the Macro window
• Most macros require action arguments
– Action argument – an expression, operator, object name
or combination of objects that provide additional
information needed by the macro to perform its actions
• For example: you must specify the name of a form to
open or a report to print
Attaching Macros to Events on
Forms
• For a macro to execute automatically, it must be attached to
an event in your application
• To attach a macro to an event on a form:
– Open the form in Design view and display the Event
properties for the object to which you want to attach the
macro
– Display the drop-down list for the appropriate event
property, then click on the macro you want to attach to
the object
• For example: Command buttons have macros
attached to them that govern their actions when a user
clicks on them
Using the Macro Builder
• Use the Macro Builder to create a macro and
attach it to an event in one step
• To use the Macro Builder:
– Open the form in Design view and display the
Event properties for the object to which you
want to attach the macro
– Click on the Build button for the desired
property
– Enter the macro actions, comments and action
arguments, as required
Creating Conditional Macros
• Conditional macro – a macro that executes only
when certain conditions are met
• To create a conditional macro:
– Display the Condition column in the Macro
window and enter an expression in it that can
be evaluated as either True or False
• If the condition is True, the action associated
with the condition will execute
• If the condition is False, the action
associated with the condition will not
execute
Creating Macro Groups
• You can group two or more related macros in one macro
group
• To create a macro group:
– Display the Macro Name column in the Macro window and
specify a name for each individual macro
– When you save the Macro window, the name you specify
becomes the macro group name
• You reference the individual macros by using the macro
group name, period (.), then the individual macro name
– For example: ReportsDialog.Preview refers to the Preview
macro in the ReportsDialog macro group
Lesson 5:
Controlling Data Entry
© 2001 ComputerPREP, Inc. All rights reserved.
Objectives
•
•
•
•
•
•
Restrict user access to fields
Set form properties
Set the open mode
Close forms automatically
Specify default values for fields
Conditionally set the Visible property
Restricting User Access to
Fields
• Locked property – specifies whether data in a control can be
edited
• Enabled property – specifies whether a control can receive
user input
• Tab Stop property – specifies whether a user can access a
control by pressing the TAB key
– If Enabled = No, a user cannot access the control and it
appears dimmed
– If Locked = Yes, a user can access the control but not edit
the data
– If Enabled = No and Locked = Yes, a user cannot access
the control but it displays normally
– If Tab Stop = No, a user cannot tab to the control
Setting Form Properties
• AllowAdditions property – specifies whether a
user can add records using a form
• AllowDeletions property – specifies whether a user
can delete records using a form
• AllowEdits property – specifies whether a user can
edit existing records using a form
• DataEntry property – specifies whether a form
opens to allow data entry and whether existing
records display
Setting the Open Mode
• You can set three open modes for a form:
– Add mode – specifies that a user can add new
records using a form
– Edit mode – specifies that a user can add new
records and edit existing records using a form
– Read Only mode – specifies that a user cannot
add or edit records using a form
• The open mode of a form overrides its property
settings
Closing Forms Automatically
• To close a form automatically after a user adds a
record:
– Create a macro that includes a Close action
– Specify the name of the form you want to close
– Attach the macro to the form’s AfterInsert or
AfterUpdate event
• AfterInsert event – an event that occurs when
a user adds a new record to a table
• AfterUpdate event – an event that occurs
when a user adds a new record or edits and
saves changes to an existing record
Specifying Default Values for
Fields
• Specify default values for fields to increase data
entry speed and accuracy
• To specify a default value for a field:
– Open the form and display the Data properties
for the appropriate control
– In the Default Value text box, type an expression
that yields the default value; or use the
Expression Builder to build an expression that
yields the default value
Conditionally Setting the
Visible Property
• Conditionally set the Visible property to hide a
control in situations when it would not be
appropriate for a user to access the control
• To conditionally set the Visible property for a
control:
– Create a conditional macro and add a SetValue
action that will execute if the condition is True
– Specify the appropriate action arguments to set
the Visible property to No
– Attach the macro to an appropriate event
Lesson 6:
Using Dialog Boxes to Get
Report Criteria
© 2001 ComputerPREP, Inc. All rights reserved.
Objectives
•
•
•
•
Create a dialog box for a report
Modify underlying queries
Modify reports
Test and refine dialog boxes
Creating a Dialog Box for a
Report
• The first step in giving users the ability to filter
records in a report is to create a dialog box that
will accept user input for use as query criteria
• To create a dialog box that will accept user input:
– Create a dialog box and add unbound text box
controls for each user-supplied criteria
– Set the Name and Format properties for the
unbound text box controls
– Create and attach macros for the dialog box, as
appropriate
Modifying Underlying Queries
• The second step in giving users the ability to filter
records in a report is to keep the dialog box open so
you can reference its user-supplied value as you
specify the criteria of a query based on that value
• To keep a dialog box open:
– Set the Visible property to No
• To reference a value a user types in the dialog box:
– Open the dialog box
– Create an expression in the appropriate Criteria cell
in the query design grid that references the control
in the dialog box
Modifying Reports
• The third step in giving users the ability to filter
records in a report is to modify appropriate report
properties to display and close needed dialog
boxes as the macro executes
• To display a dialog box when a user previews or
prints a report:
– Create a macro that opens the dialog box form
and attach it to the report’s OnOpen event
• To close a dialog box when a user closes a report:
– Create a macro that closes the dialog box form
and attach it to the report’s OnClose event
Testing and Refining Dialog
Boxes
• Ensure that you test all features included in the
dialog box and test it in relation to other forms in
the application
• To test a dialog box:
– Open the dialog box as many times as
necessary to test each feature
– Record and repair any errors or problems
Testing and Refining Dialog
Boxes (cont’d)
• If, while testing the dialog box, you encounter
results that could confuse an inexperienced user,
create message boxes to tell the user what to do
next
• To create a message box:
– Create a macro and include the MsgBox action
– Type the message you want the message box to
display in the Message action argument
– Specify settings for the remaining action
arguments as appropriate
Lesson 7:
Creating Custom Switchboards
© 2001 ComputerPREP, Inc. All rights reserved.
Objectives
• Create switchboard forms
• Create macros for switchboard controls
• Assign macros to switchboard controls
Creating Switchboard Forms
• Switchboard forms are forms that contain option
or command buttons that users can click on to
execute application tasks
• Switchboards serve as menus for an application
• Switchboard options should logically group
related tasks and forms in the application
• You can create as many switchboards as
necessary
• Switchboards can display other switchboards
Creating and Assigning
Macros to Switchboard
Controls
• Switchboard controls open forms or display other
switchboards
• As with controls on any form, you create macros
or event procedures to perform application tasks
associated with the controls
• After you create the macros or event procedures,
assign them to the appropriate controls in the
switchboard
Lesson 8:
Enhancing Applications
© 2001 ComputerPREP, Inc. All rights reserved.
Objectives
•
•
•
•
Create keyboard shortcut macros
Create control tips and status bar messages
Set startup properties
Adjust objects and view enhancements
Creating Keyboard Shortcut
Macros
• Create keyboard shortcuts by creating a macro
group named AutoKeys, in which you assign
macro actions to key combinations
• The actions you assign to shortcut key
combinations within a database will override the
default Access key assignments
• Enter symbols in the Macro Name column of the
AutoKeys macro group to specify particular key
combinations
Creating Control Tips and
Status Bar Messages
• Control tips and status bar messages provide
helpful information and instructional text about
controls in a form
– Control tip – text that displays in a pop-up
window when a user positions the mouse
pointer over a control
– Status bar message – text that displays in the
status bar when a control is activated
Setting Startup Properties
• Startup properties determine the way an application appears
when opened
• Use the Startup dialog box to set startup properties, which
can include:
– Specifying the text that will display in the application title
bar
– Specifying the form to display automatically when the
application opens, such as the switchboard
– Specifying to hide the Database window when the
application opens
• Press and hold the SHIFT key when opening a database to
bypass the startup settings
Adjusting Objects and Viewing
Enhancements
• After you enhance an application, you might need
to adjust macros or forms to accommodate the
changes you made
• In the Trailer Park application, you will:
– Write a macro action to redisplay the hidden
Database window from within a macro
– Write a macro action to exit Access from within
the macro
– Test the application enhancements
Lesson 9:
Using Database Utilities
© 2001 ComputerPREP, Inc. All rights reserved.
Objectives
•
•
•
•
•
Use the Database Splitter
Use the Linked Table Manager
Compact and repair a database
Convert a database to a previous version
Back up and restore a database
Using the Database Splitter
• If your application is located on a network, you can
create a front-end/back-end application using the
Database Splitter wizard
– Back-end database – a database that contains the
tables used by the front-end database
– Front-end database – a database that contains the
database objects and links to the tables in the backend database
• Typically, the back-end database is located on a
network server and copies of the front-end database
are located on individual users’ computers
Using the Linked Table
Manager
• After you have split a database, use the Linked
Table Manager utility to view or refresh links, or to
change the file name and path for linked tables
• If the location or structure of a linked table has
changed since you split the database, you will not
be able to access the table data by attempting to
open a query, form or report until you refresh the
links
Compacting and Repairing a
Database
• If you delete data or objects in your application,
the database can become fragmented and use disk
space inefficiently
• If a database behaves unpredictably, it may be
damaged
• Use the Compact and Repair utility to compact and
repair a database simultaneously
• Compacting a database:
– Rearranges how the file is stored on disk
– Resets AutoNumber values
Converting a Database to a
Previous Version
• You can use the Convert Database utility to convert an
Access 2000 database to an Access 97 database
• To convert a database to a previous version:
– Open the database you want to convert
– Click on Tools, Database Utilities, Convert Database,
To Prior Access Database Version…
– Name the new database, then click on Save
• If the Access 2000 application contains event
procedures, you may need to fix missing references in
the converted version
Backing Up and Restoring a
Database
• To back up a database:
– Make a copy of the database using Windows
Explorer, My Computer or the MS-DOS Copy
command, or
– Use a backup/restore utility software package (if
the database will not fit on one floppy disk)
• To restore a database:
– Copy the backup database back to your hard
disk, or
– Use the restore feature of the backup/restore
utility software package
Lesson 10:
Protecting and Replicating
Databases
© 2001 ComputerPREP, Inc. All rights reserved.
Objectives
•
•
•
•
•
Set record locking
Open databases in exclusive mode
Use database passwords
Encrypt and decrypt databases
Replicate and synchronize databases
Setting Record Locking
• Record Locking – a setting that gives one user
exclusive access to a record or table and prevents
other users from editing a given record at the
same time
• Record locking settings:
– No locks – turns off record locking for the
database
– All records – locks all records in the form or
datasheet while you edit them
– Edited record – locks only the record you are
editing
Opening Databases in
Exclusive Mode
• Open mode – more than one user can open a
database at a given time
• Shared mode (the default mode) – other users can
access a database that you have opened
• Exclusive mode – no other users can access a
database that you have opened
– Some database operations require that a
database be opened in exclusive mode. For
example: compacting a database, or setting or
unsetting a database password
Using Database Passwords
• Set database passwords to prevent unauthorized
access to your application
• Database passwords are stored with the database
• After you set a password, only a user who enters
the correct password will be able to open the
database
• If you lose or forget the password, it cannot be
recovered and you will be unable to open the
database
Encrypting and Decrypting
Databases
• Encrypt – compacts a database and makes it
indecipherable by a utility program or a word
processor
• Decrypt – reverses the encryption process
• Encrypt a database:
– When sending a database as an e-mail
attachment, or
– Sending it to a remote user over the network
Encrypting and Decrypting
Databases (cont’d)
• Before you can encrypt a database:
– You must close it
– Enough space must be available on your hard
disk for the original database and the temporary
copy Access creates during the encryption
process
• Before you can decrypt a database:
– Enough space must be available on your hard
disk for the original database and the temporary
copy Access creates during the decryption
process
Replicating and Synchronizing
Databases
• Replicate a database for use by mobile users and
synchronize the replicas with the original database
to update the data between them
– Replicate – copies a database to create replicas
– Synchronize – exchanges updated data
between the original database and the replicas
• When you replicate databases, the original
becomes the design master
• The design master and replicas, collectively, are
know as a replica set
Replicating and Synchronizing
Databases (cont’d)
• Users may update design master or any of its
replicas may be updated
• To change the structure of a database, you must
make changes to the design master
• Different replicas in the replica set can be
designated the design master; only one design
master can be designated in each replica set
• You cannot replicate or synchronize a database
that is password-protected, nor can you attach a
password to a replica