CP212 Lab 9 : Importing Data from a Database

You might also want to read more about using Listboxes to store data: Using Listboxes.

---

Database Relationships for Task

You can refer to this diagram during the task. The database is the Sales Order.mdb database.

---

Practice

You may create a form like this and save it for later use. When you click on the Browse button, open a dialog box that will allow the user to select a file and place its name inside the textbox under the label File. (the file box is TextBox, the output box is a ListBox)

---

Instructions

• Save the spreadsheet file as a Macro Enabled Workbook (.xlsm) file before you write more code. Name your spreadsheet with your network login followed by _l09.xlsm. For example, if your network login was barn4520, then your lab file should be named barn4520_l09.xlsm. Save often.

• All tasks are to be completed on your own.

• Show the Developer Tab in Excel. Click File / Options Customize Ribbon and check the option for Developer Tab.

• Put all code into a Module. Use a different Module for each task and name them appropriately as Task1 or Task2 etc.

• Copy and paste this header into your code modules and fill it out:

  Option Explicit
  
  ' ==== CP212 Windows Application Programming ===============+
  ' Name: Your Name
  ' Student ID:
  ' Date:
  ' Program title:
  ' Description:
  '===========================================================+
  

• Make sure you test your tasks before submitting your lab to the Dropbox to be marked.

• When finished upload your file to the Lab 9 Task Dropbox in MyLearingSpace to be marked

---

Tasks

• Create a form that allows the user to select a database (or any file, you do not need to add a filter for file types but you can), and places the name and path of the selected database in the textbox. Also include a listbox on your form and appropriate labels and buttons as shown below:

  

  Tip: to have the dialog box start in the current folder, use:

  fd.InitialFileName = ThisWorkbook.Path
  

  This makes it easier to find the file and faster for the IAs to mark. Look back to your textbook or the lecture slides if you have problems. Export this form for later use in other projects.

  (the file box is a TextBox, the output box is a ListBox)

• If you haven't already done so, download the database of sales orders Complete_Database.mdb described in the Topics/Tutorial section above.

• Based on the Complete database, write SQL statements to implement the following query. Attach code to the "Run" button so that when the button is clicked, the results of the query are placed in the listbox.

  Find the CustCity for all the Customers with a CustID less than 15. Be sure that the cities appered in the list are not duplicated

  Your output may look like:

  

  For help in creating the queries, look at the Sales Orders: Sample Queries document.

  You may want to export this form for later use in future projects.

Note

You have to use Button(s) on worksheet to run the tasks in this lab and all next labs and assignments. Marks will be deducted if you do not use Buttons

You can use only the covered material in the course till now. You can't use uncovered topics. You can't use topics out of the context of this course.