Microsoft Access Project
iwatbo
Access_2016_ACP_5.pdf
Access 2016: In Practice Page 1 of 5 Access Application Capstone Project 5
ACCESS 2016: IN PRACTICE—APPLICATION CAPSTONE PROJECT
Access 2016: Application Capstone Project 5 (Chapters 8-10)
For this project, you enhance the functionality of a database for Magnificent Meals. You append records
from an Excel file to the Customers table; export to XML the data and structure from a query; use mail
merge to generate a feedback letter to send to select customers; and create a custom Navigation pane
to enhance the functionality of the database. Finally, you encrypt the database with a password and
create a PDF file of the database table documentation. You use OneDrive and Word Online to customize
and share a Word file.
[Student Learning Outcomes: 8.2, 8.5, 8.6, 9.1, 9.2, 9.5, 10.3, 10.4, 10.5]
Note to Students and Instructor:
Students: For this project, you share a OneDrive file with your instructor.
Instructor: In order to complete this project, your students need your Microsoft email address.
Files Needed: MagnificentMeals‐05.accdb, CustomerData.xlsx, MMFeedbackLetter.docx, and NewMealsMessage.docx Completed Project File Names: [your initials] Access ACP 5.accdb, [your initials] OrderDetail.xml, [your initials] OrderDetail.xsd, [your initials] FeedbackLetterMain.docx, [your initials] FeedbackLetterMerge.docx, and [your initials] NewMealsMessage‐edited.docx
Skills Covered in this Project
Prepare an Excel file for importing. Append data from an Excel file to an existing table. Review a table after importing data. View and edit relationship properties. Export a query to XML. Use mail merge to create a form letter. Filter recipients in a mail merge. Save the settings in a mail merge document. Create a custom Navigation pane. Create a custom category and custom groups. Assign objects to groups.
Modify shortcut properties. Hide objects and groups. Encrypt a database with a password. Use the Database Documenter tool. Log in to OneDrive. Create a OneDrive folder. Upload a file to a OneDrive folder and share that
file.
Add a comment to an Office Online application. Download a OneDrive file to your computer.
1. Open the MagnificentMeals‐05.accdb database file from your student data files and save it as [your initials] Access ACP 5.
2. If prompted, enable content in the database. 3. Close the MainMenu form that opened automatically with the database. 4. Prepare to import the Excel file.
a. Open the Customers table in Design view. b. Click through each field to note the Field Name and the Required property setting of the field. c. Open the CustomerData.xlsx Excel file. Note that the first row contains field names and that
there are no missing values in the data rows.
d. Close the file. e. Close the Customers table.
5. Append data from Excel to an existing table. a. Click the Excel button to launch the Get External Data – Excel Spreadsheet dialog box. b. Locate, select, and open the CustomerData.xlsx file.
Access 2016: In Practice Page 2 of 5 Access Application Capstone Project 5
c. Choose to append a copy of the records to the Customers table and click OK to launch the import process.
d. Confirm that the data records display in the wizard window and advance to the next page of the wizard.
e. Confirm the name of the table in which to append the records and finish the wizard. f. Don’t save the import steps. g. Access appends the records to the table.
6. Open the Customers table in Datasheet view to verify that nine records shown in Figure 1 were appended to the table.
a. Click the plus sign to the left of CustID 1000 to expand the related records. Note that the related order from June 1, 2018 displays.
b. Close the Customers table. 7. Enforce referential integrity between the Customers and Orders tables.
a. Open the Relationships window. Note that the existing relationship between the Customers and Orders tables does not have any symbols, indicating that referential integrity is not enforced.
b. Edit the relationship between Customers and Orders to Enforce Referential Integrity and Cascade Updates.
c. Save the changes and close the Relationships window. 8. Export the OrderDetailExport query to XML.
a. Start the export by selecting the OrderDetailExport query and selecting XML File from the Export options. Use [your initials] OrderDetail.xml as the file name.
b. Start the export process. c. Choose to export both the data and the schema and click the More Options button in the Export
XML dialog box.
d. Click the Schema tab and save the schema file in the same location as the XML file. Change the schema document name to [your initials] OrderDetail.xsd. Click OK to export the file.
e. Close the wizard without saving the export steps.
Figure 1 Access ACP 5, Datasheet view of the Customers table after appending records from Excel
Access 2016: In Practice Page 3 of 5 Access Application Capstone Project 5
9. Open the [your initials] OrderDetail.xml file in Notepad to verify that it was created
successfully. It should be similar to Figure 2.
Close the file.
10. Perform a mail merge using an existing letter.
a. Select the CustomerOrderMailMerge query and click the Word Merge button.
b. Select the Link your data to an existing Microsoft Word document and click OK.
c. Locate, select, and open the MMFeedbackLetter.docx Word file. In
the Word document, note that this
document contains placeholders of
VVVV, XXXX, YYYY and ZZZZ so you know
where to position the merge fields.
d. Click the Next: Write your letter link. e. In the Word document, replace the VVVV
placeholder with the <<AddressBlock>>
merge field. Accept the default selections
on the Insert Address Block dialog box.
f. Replace the remaining placeholders by clicking the More items link to open the
Insert Merge Field dialog box and insert
the corresponding field. Replace XXXX
with FirstName, YYYY with OrderDate, and
ZZZZ with MealName.
g. Click the Next: Preview your letters link. The document updates to show how the
merged data appears (Figure 3).
h. Click the Edit recipient list link to limit the records that display. The Mail Merge
Recipients dialog box opens.
i. Click the Filter link to open the Filter and Sort dialog box.
j. Select MealName from the Field box drop‐down list and enter Chocolate Decadence Cake in the Compare to box.
k. Click OK. The Mail Merge Recipients box updates to display the 3 records where the order contained this cake.
l. Click OK to close the Mail Merge Recipients box. m. Click the Next: Complete the merge link. n. Click the Edit individual letters link and merge all the records. o. Save the completed merged document as [your initials] FeedbackLetterMerge.docx and close
the document.
Figure 2 Access ACP 5, XML file after exporting the OrderDetailExport query
Figure 3 Access ACP 5, Preview of merged feedback letter
Access 2016: In Practice Page 4 of 5 Access Application Capstone Project 5
11. Save a copy of the main document as [your initials] FeedbackLetterMain.docx to preserve the merge settings and close the document.
12. Create a category and group for a custom Navigation pane. a. Open the Navigation Options dialog box. b. Add a new category named Magnificent Meals Main Menu. c. Add a new group in this category named Order Processing. d. Add a new group in this category named Mail Merge and Exports. e. If selected, uncheck the Show Hidden Objects box. f. Close the Navigation Options dialog box.
13. Assign objects and modify the Navigation pane for the Magnificent Meals Main Menu category. a. Select the Magnificent Meals Main Menu category. b. Drag the MainMenu form into the Order Processing group. c. Drag the CustomerOrderMailMerge and OrderDetailExport queries into the Mail Merge and
Exports group.
d. Select the Disable Design View shortcuts check box for all of the shortcuts in the Order Processing and Mail Merge and Exports groups.
e. Hide the Unassigned Objects heading. f. Drag the divider bar to widen the Navigation pane. The completed
Navigation pane for the Magnificent Meals Main Menu should look
similar to Figure 4.
g. Close the database, but leave Access open. 14. Password protect the database.
a. Open the [your initials] Access ACP 5.accdb using the Open Exclusive option. b. Encrypt the database with a password. Use ButterI$b3TTer as the password. c. Click OK. If you get a message about row level locking, click OK. d. Close the database.
15. Test the password‐protected database. a. Open the [your initials] Access ACP 5.accdb database file. b. Enter the password ButterI$b3TTer when prompted.
16. Create database documentation for the database tables. a. Click the Database Documenter button to open the
Documenter dialog box.
b. Select all of the tables on the Tables tab. c. Click the Options button. Verify that the options in the
Print Table Definition dialog box match those shown in
Figure 5.
d. Preview the Database Documenter report. e. Export the documentation as a PDF file. Locate the
folder where you want to save the file, enter [your initials] Access ACP 5_Documentation.pdf as the file name, and choose to Open file after publishing. The PDF file
opens in your PDF viewer.
f. Navigate through the pages in the PDF file to ensure it
Figure 4 Access ACP 5, Completed Navigation pane
Figure 5 Access ACP 5, Print Table Definition dialog box
Access 2016: In Practice Page 5 of 5 Access Application Capstone Project 5
was created successfully. The report contains approximately 21 pages.
g. Close the PDF file. h. Don’t save the export steps. i. Click Close Print Preview.
17. Close the database and close Access. 18. Create a OneDrive folder and upload files to the OneDrive folder.
a. Open an Internet browser page and log in to your OneDrive account (http://www.onedrive.com).
b. Create a new folder named MagMeals in the Files area. c. Upload the NewMealsMessage.docx file from your student data files to the MagMeals folder.
19. Add a comment to a document in Word Web App. a. Open the NewMealsMessage document in read‐only mode in Word Online. b. Select the first line in the document. c. Add and post the following comment to the selected line in the document: Please review
this email message draft and let me know if you have any suggested changes.
d. Return to your MagMeals folder in OneDrive. 20. Share the NewMealsMessage document with your instructor, include a brief message, and allow
him or her to edit the document.
21. Download a file from OneDrive to your computer. a. Select the NewMealsMessage document and download the file. In the dialog box that opens at
the bottom of the window, click the Save as option (Figure 6) to open the Save As dialog box.
Browse to the desired location and save the file as [your initials] NewMealsMessage‐
edited.docx. If you are using a different browser or Windows version, the save interface may be
different.
b. Uncheck the NewMealsMessage file. 22. Sign out of OneDrive.
Figure 6 Access ACP 5, Save as option when downloading a file from OneDrive
