Office 2013 – myitlab:grader – Instructions YO Access WS 13 H1
Premium Electronics Database
Project Description:
Premium Electronics is a local electronics store that offers discounts on top brand electronics to members of their buyers’ club. Members sign up online through an outsourced CRM service, and then spreadsheet files are sent on a biweekly basis to be imported into the database. You have been asked to create an import procedure that will allow the user to select multiple files, if necessary, to import into the database. After the importing process is finished a message box should appear informing the user that the process has been completed. You also have been asked to increase the security of the database to protect the structure and content of the database.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step / Instructions / Points Possible /1 / Start Access. Open the downloaded database named a07ws13_grader_h1_Electronics.accdb. Save the file with the name a07ws13_grader_h1_Electronics_LastFirst, replacing LastFirst with your name. / 0
2 / Add a button in the frmImportMembers form in the top-left corner of the Detail section using the name cmdImportMembers. / 8
3 / Change the button caption to read Import New Members. Change the width of the button to 1.7" and the height to 0.3". / 8
4 / Use the Code Builder to create a procedure that will run when the button is clicked. Below the Private Sub statement, declare a variable named ImportTable. On the following line, set the ImportTable variable to display the Open dialog box. / 16
5 / In a new blank line immediately below the Private Sub statement, add the comment This procedure imports new members from multiple xlsx files. to the procedure. / 4
6 / In a new blank line immediately below the comment, begin to create the error handling process. If an error occurs during the execution of this procedure, indicate that Access should immediately move to the portion of the code that starts on the line that contains Import_Err.
Immediately above the End Function statement, indicate that if no errors occur, the procedure should end on the next line and press ENTER.
On the current line, indicate that if an error occurs, the code should move to this line and continue running the procedure. Press ENTER.
Indicate that if an error occurs, the error should be displayed to the user in a message box. / 16
7 / In a blank line immediately below the Set statement, use a With statement to modify the ImportTable object.
On the next blank line, set Import Member File(s) as the title of the file dialog box.
On the next blank line, use the .Show method of the FileDialog object to determine if one or more files have been selected.
On the next line, store the name of the file in a variable named Filename. Press ENTER.
On the next line, create a loop that will continue until there are no additional files names in the .SelectedItems method. Type For Each Filename In .SelectedItems and press ENTER.
On the next line, allow for multiple Excel spreadsheets to be imported into the tblMembers table using the code DoCmd.TransferSpreadsheet acImport, ac SpreadsheetTypeExcel112Xnl, "tblMembers", Filename, True, "" and press ENTER. / 24
8 / In the blank line immediately below the import process, indicate that once the file have been imported a message box should display the message The files(s) have been imported before exiting. Press ENTER and then type Next. / 5
9 / On the next line, type Else and press ENTER. On the current line, create the False portion of the If statement by indicating if the import is cancelled the message The import has been cancelled should be displayed. Type End If and press ENTER. Type End With and press ENTER. / 9
10 / Delete any blank lines within the code. Delete any blank spaces at the beginning of a line.
Compile the database before exiting the Visual Basic Editor. If an error occurs during the compile process, fix the error, and compile again. Two Excel spreadsheets of members have been provided to you for importing. Test the import process by importing the a07ws13July.xlsx and a07ws13August.xlsx Excel files. Import these files only one time each. / 10
11 / Close the database and submit the database as directed. / 0
Total Points / 100
Updated: 08/01/2014 1 A_WS13_YOV2_H1_Instructions.docx