All Sections – The Access Workbench
C. Create a form that allows users to view and edit project data. The form should
show information about the project, the department that is responsible for the
project, and it should list all employees who are assigned to work on that
project.
All Sections – The Access Workbench
There are many possibilities for the final design of the form. Here is one:
All Sections – The Access Workbench
D. Create a report that displays the project information shown on the form you
created in part C. The report should show this information for all projects,
sorted in ascending order by ProjectID.
All Sections – The Access Workbench
APPENDIX H
CHAPTER OBJECTIVES
• Learn how to create Microsoft Access 2016 Switchboards
CHAPTER ERRATA
There are no known errors at this time. Any errors that are discovered in the future will
be reported and corrected in the online DBC e08 Errata document, which will be
available at http://www.pearsonhighered.com/kroenke.
TEACHING SUGGESTIONS
• This section of The Access Workbench is a continuation of Chapter 4’’s section of
“The Access Workbench”. It should be taught in conjunction with Chapter 4.
• This switchboard is built on the DBC-e08–WP–AW-04.accdb database file as it exists
at the completion of the Chapter 4 Access Workbench Exercises.
ANSWERS TO ACCESS WORKBENCH EXERCISES
In “The Access Workbench” section in Chapter 4, you created some prototype forms and
reports for the Wedgewood Pacific database (WP.accdb).
H-AW–1. Create a switchboard for the Wedgewood Pacific database (WP.accdb).
See the file: DBC-e08-WP–AW-AppH.accb
This is a straightforward exercise, and the results are shown below. This form links
to only some of the forms and reports actually available, and can easily be
expanded. Note the use of a background color for the entire form.
All Sections – The Access Workbench
All Sections – The Access Workbench
SECTION FIVE
CHAPTER OBJECTIVES
• Understand 1:1 relationships in Microsoft Access
• Understand N:M relationships in Microsoft Access
CHAPTER ERRATA
There are no known errors at this time. Any errors that are discovered in the future will
be reported and corrected in the online DBC e08 Errata document, which will be
available at http://www.pearsonhighered.com/kroenke.
TEACHING SUGGESTIONS
• This section of “The Access Workbench” is important because it shows how
Microsoft Access implements 1:1, 1:N and N:M relationships in Access databases.
As illustrated in the text, how to create 1:1 relationships in Access is perhaps not
obvious, but it is not difficult. Be sure your students understand how to create all
three types of relationships.
• Make sure your students actually work through The Access Workbench steps before
attempting the t Access Workbench Exercises.
• We will use the MySQL Workbench to do these drawings, because it handles IE
Crow’s Foot database designs really well. For more information about using the
MySQL Workbench, see Appendix C, “Getting Started with MySQL 5.7 Community
Server.”
All Sections – The Access Workbench
ANSWERS TO ACCESS WORKBENCH EXERCISES
AW.5.1. Using an IE Crow’s Foot E-R diagram, draw a database design for the Wedgewood
Pacific (WP) database as completed at the end of Chapter 3’s section of “The
Access Workbench.”
All Sections – The Access Workbench
© 2018 Pearson Education, Inc. Page 68 of 102
Version Two—With COMPUTER and COMPUTER_ASSIGNMENT
See file: DBC-e08-WP-Database-Design-with-COMPUTER.mwb.
AW.5.2. This chapter’s section of “The Access Workbench” describes how to create 1:1
relationships in Microsoft Access. In particular, we added the business rule that
each salesperson at Wallingford Motors can have one and only one vehicle as a
demo car. Suppose that the rule has been changed so that each salesperson can
have one or more cars as demo vehicles.
A. Using an IE Crow’s Foot E-R diagram, redraw the database design in Figure
AW-5-1 to show the new relationship between VEHICLE and
SALESPERSON. Which table(s) is (are) the parent(s) in the relationship?
Which table(s) is (are) the child(ren)? In which table(s) do you place a foreign
key?
All Sections – The Access Workbench
Relationship
Referential Integrity
Constraint
Cascading Behavior
PARENT
CHILD
ON UPDATE
ON DELETE
SALESPERSON
VEHICLE
NickName in VEHICLE
must exist in NickName
in SALESPERSON
YES
YES
B. Start with the Wallingford Motors database that you’ve created so far
(WMCRM.accdb) as it exists after working through all the steps in this
chapter’s section of “The Access Workbench.” (If you haven’t completed
those actions, do so now). Copy the WMCRM.accdb database and rename
the copy as WMCRM-AW05-v02.accdb. Modify the WMCRM-AW05-
v02.accdb to implement the new relationship between VEHICLE and
SALESPERSON. (Note: Copying a Microsoft Access database is discussed
in Chapter 4’s section of “The Access Workbench”).
To do this, we have to:
All Sections – The Access Workbench
• Create the relationship between the tables (including cascading updates and
deletes) and
• Populate the VEHICLE.NickName column to reestablish the VEHICLE usage
data shown in Figure AW-5-5.
All Sections – The Access Workbench
© 2018 Pearson Education, Inc. Page 71 of 102
AW.5.3. This chapter’s section of “The Access Workbench” describes how to create 1:1
relationships in Microsoft Access. In particular, we added the business rule that
each salesperson at Wallingford Motors can have one and only one vehicle as a
demo car. Suppose that the rule has been changed so that (1) each salesperson
can have one or more cars as demo vehicles, and (2) each demo vehicle can be
shared by two or more salespersons.
A. Using an IE Crow’s Foot E-R diagram, redraw the database design in Figure
AW-5-1 to show the new relationship between VEHICLE and
SALESPERSON. Which table(s) is (are) the parent(s) in the relationship?
Which table(s) is (are) the child(ren)? In which table(s) do you place a foreign
key?
All Sections – The Access Workbench
Relationship
Referential
Integrity
Constraint
Cascading Behavior
PARENT
CHILD
ON UPDATE
ON DELETE
SALESPERSON
SALESPERSON_VEHICLE_INT
NickName in
SALESPERSON_
VEHICLE_INT must
exist in NickName in
SALESPERSON
YES
YES
VEHICLE
SALESPERSON_VEHICLE_INT
InventoryID in
SALESPERSON_
VEHICLE_INT must
exist in InventoryID
in VEHICLE
NO
YES
B. Start with the Wallingford Motors database that you’ve created so far
(WMCRM.accdb) as it exists after working through all the steps in this
chapter’s section of “The Access Workbench.” (If you haven’t completed
those actions, do so now). Copy the WMCRM.accdb database and rename
the copy as WMCRM-AW05-v03.accdb. Modify the WMCRM-AW05-
v03.aaccdb to implement the new relationship between VEHICLE and
SALESPERSON. (NOTE: Copying a Microsoft Access database is discussed
in Chapter 4’s section of “The Access Workbench”).
All Sections – The Access Workbench
To do this, we have to:
• Drop the relationship between SALESPERSON and VEHICLE,
All Sections – The Access Workbench
SECTION SIX
CHAPTER OBJECTIVES
• Understand database security in Microsoft Access
CHAPTER ERRATA
There are no known errors at this time. Any errors that are discovered in the future will
be reported and corrected in the online DBC e08 Errata document, which will be
available at http://www.pearsonhighered.com/kroenke.
TEACHING SUGGESTIONS
• This section of “The Access Workbench” covers database security in Microsoft
Access. If Microsoft Access is going to be used for serious database work in
organizations, then security must be provided for the use of the Microsoft Access
application.
• The security in Microsoft Access 2007 and 2016 is a major change from the user-
based security in Microsoft Access 2003. Compare Microsoft Access 2003 security
and Microsoft Access 2016 carefully.
• Make sure your students actually work through The Access Workbench steps before
attempting The Access Workbench Exercises.
All Sections – The Access Workbench
ANSWERS TO ACCESS WORKBENCH EXERCISES
AW.6.1. Use the Wedgewood Pacific (WP) database developed in previous sections of “The
Access Workbench,” to answer the following questions.
A. Analyze the data in the WP database tables (particularly DEPARTMENT and
EMPLOYEE) and create a database security plan, using Figure 6-16 as an
example.
DATABASE RIGHTS GRANTED
Table
Administrative
Assistants
Management
System Administrator
EMPLOYEE
Read, Change
Read, Insert,
Change, Delete
Grant Rights, Modify
Structure
DEPARTMENT
Read
Read, Insert,
Change, Delete
Grant Rights, Modify
Structure
PROJECT
Read, Insert, Change.
Delete
Read, Insert,
Change, Delete
Grant Rights, Modify
Structure
ASSIGNEMENT
Read, Insert, Change
Read, Insert,
Change, Delete
Grant Rights, Modify
Structure
B. If you have not already created a My-Trusted-Location folder, follow the steps
in this chapter’s section of “The Access Workbench,” do so now.
This is self-explanatory.
C. Make a copy of the WP.accdb file in the My-Trusted-Location folder and
name it WP-AW06-01.accdb. Open the WP-AW06-01.accdb database to
confirm that it opens without displaying the Security Warning bar, and then
close the database.
This is self-explanatory.
D. Make a copy of the WP.accdb file in the My-Trusted-Location folder and
name it WP-AW06-02.accdb. Encrypt the WP-AW06-02.accdb database with
the password AW06EX+password. Close the WP-AW06-02.accdb database
and then reopen it to confirm that it opens properly using the password. Close
E. If you have not already created the Digital-Certificate-AW06-01 digital
certificate, follow the steps in this chapter’s section of “The Access
Workbench” to do so now.
This is self-explanatory.
All Sections – The Access Workbench
F. Make a copy of the WP.accdb file in the My-Trusted-Location folder and
name it WP-AW06-03.accdb. Create an AACDE version of the WP-AW06–
03.accdb database. Create a signed package using the WP-AW06-03.accde
database and the Digital-Certificate-AW06-001 digital certificate.
G. If you have not already created a My-Distributed- Databases folder, follow the
steps in this chapter’s section of “The Access Workbench,” do so now.
This is self-explanatory.
H. Make a copy of the WP-AW06-03.accdc file in the My-Distributed-Databases
folder. Extract the WP-AW06-03.accde file into the folder and then open it to
confirm that the database opens properly. Close the database.
All Sections – The Access Workbench
SECTION SEVEN
CHAPTER OBJECTIVES
• Build Web home pages
• Create an ODBC data source for Microsoft Access databases
• Build a Web page to display data from a database
CHAPTER ERRATA
There are no known errors at this time. Any errors that are discovered in the future will
be reported and corrected in the online DBC e08 Errata document, which will be
available at http://www.pearsonhighered.com/kroenke.
TEACHING SUGGESTIONS
• This section of The Access Workbench shows your students how to use Microsoft
Access as a DBMS to support Internet Web page applications. Today, Web sites use
database data on Web pages a lot, so this is an important tool for your students to
have in their toolbox.
• Have your students read Online Appendix, “Getting Started with Web Servers, PHP
and the NetBeans IDE,” and then review it in class. This document describes the
entire set up of the IIS Web Server, PHP and the NetBeans IDE, as needed for this
chapter. Using this appendix, your students should be able to install and set up
everything necessary to complete the work in the chapter on their own computer.
• Illustrate setting up Microsoft‘s IIS Web server in class.
• Illustrate setting up PHP in class
• Illustrate creating an OBDC System Driver in class.
• Illustrate using the NetBeans IDE in class.
• The HTML and PHP script has to be correct or the pages won’t work. Cover the
details of how the code works. If you students aren’t familiar with basic HTML, use
the resources at www.w3c.org referenced in the IM chapter for Chapter 7 to help
them get started.
• Make sure your students actually work through The Access Workbench steps before
attempting the Access Workbench Exercises.
All Sections – The Access Workbench
• The Access Workbench Exercises below are extensions of the work done in The
Access Workbench Section 7. If you want to extend the Web work your students do,
use the WP database—applying the unsecured version of WP–AW-06.accdb
renamed as WP.accdb as the starting point—and have your students create a Web
site for WP from scratch. Have them connect it to the DBC Web Page as described
in exercises AW.7.1 and AW.7.2 below.
ANSWERS TO ACCESS WORKBENCH EXERCISES
AW.7.1. If you haven’t completed Exercise 7.51, do it now.
AW.7.2. Link the WM Web page to the DBC Web page.
See the solution to Exercise 7.51 in Chapter 7 of the IM. The completed pages are
shown below—both the DBC Web page and the WM Web page with all the links
required for these exercises:
All Sections – The Access Workbench
AW.7.3. Using the WMCRM database, code a PHP Web page to display the data in
SALESPERSON. Add a hyperlink on the WM Web page to access the page. Using
your database, demonstrate that your page works.
All Sections – The Access Workbench
<!DOCTYPE html>
<html>
</head>
<body>
<?php
// Get connection
$Conn = odbc_connect(‘WM’, ”, ”);
// Test connection
if (!$Conn)
}
?>
<!– Page Headers –>
<h1>
The Wallingford Motors CRM SALESPERSON Table
</h1>
<hr />
<h2>
SALESPERSON
</h2>
<?php