BIS B319 Advanced Business Computing

Assignment 4: SQL in VBA modules

Important note

You must use word processing software (such as Microsoft Word) to prepare the TMAs, and submit the TMAs via the Online Learning Environment (OLE). All assignments must be uploaded to the OLE by the due date.

Failure to upload a TMA in the required format to the OLE may result in the score of the TMA being adjusted to zero.

According to the University’s policy, no extension of the due date will be allowed for the final TMA. This policy will be strictly enforced. Any late submission of the final TMA will result in the score of the TMA being adjusted to zero.

Learning objectives

Before starting the assignment, you must have completed the following topics:

  • Units 5, 6, 8 and 9

In particular, you need to know how to:

  • write SELECT statements in SQL to extract data from a database;
  • write nested SQL statements to cater for more complex queries;
  • write SQL statements to update content of a table in Access;
  • create a query by using embedded SQL statements;
  • pass data returned from an embedded SQL statement to other parts of the VBA module; and
  • test and debug a VBA program.

Case study

With reference to the case study in previous assignments, you now need to write SQL statements to extract information from the database, and embed SQL statements in VBA programs to perform specific tasks.

Assignment requirements

Task 1 (10%)

Use MS Word or Notepad to write two nested SQL statements:

  • An SQL statement for finding the products which were never ordered in 2009. The fields to be shown are Product Code and Product Description.
  • An SQL statement for finding the customers who did not order any product in 2007. The only field to be shown is Customer Name.

Task 2 (10%)

Retrieve the search product form which you created in Task 1 of TMA 3. Rewrite the code associated with the Click method of the Search Product button using an SQL statement embedded in the Open method of recordset in such a way that the original loop structure is eliminated.

Marks will be awarded according to the efficiency of your programs. Note that one way to improve the efficiency of a program is to try to use fewer lines of code to achieve the same result without reducing the readability of the code.

Task 3 (30%)

Retrieve the Order Input Form which you created in TMA 3. Rewrite the code associated with the Click method of the button which calculates the total amount of an order. As in the last task, you are required to use SQL statements embedded in the Open method of recordsets in such a way that you can remove some of the loop structures in your original code. Again, marks will be awarded according to the efficiency of your programs.

Task 4 (50%)

Add a Confirmed (data type: Boolean) field to the Order Header table. On the Order Input Form, rewrite the Click method of the button which checks the current order. Again, your objective is to use SQL statements to replace the loop structures to make your code more efficient. For the orders that are not OK (i.e. either customer is not allowed to order a

product, or a product is out of stock), display exactly the same message as in TMA 3. However, for orders that are OK, do the following:

  • Check if the order is already confirmed (i.e. the Confirmed field is Yes) and if so, display the message: ‘Order: (Order Number) already confirmed.’
  • If the order is not yet confirmed, change the value of the Confirmed field to Yes.

Again, marks will be awarded according to the efficiency of your programs.

What you should submit

  • A text file (in pure text or MS Word format) containing the two SQL statements required in Task 1.
  • A MS Access database containing the programs required in Tasks 2, 3 and 4.