Introduction to Relational Databases with Microsoft Access, VBA and SQL

Overview

Microsoft Access is a powerful relational database management system which allows database tables to be linked together through common fields. This eases data sharing and management, and minimises data duplication. This practical course introduces relationships - why they are needed and how they are designed and created - and develops techniques required for relational design. These techniques include creating suitable tables for storing and linking data, and developing suitable user interfaces for entering and accessing data; the latter requires some Access programming. Access contains two programming languages - Visual Basic for Applications (VBA) and Structured Query Language (SQL). SQL's commands query data whilst VBA is used for macro and module programming; both enhance forms (used mainly for data input) and reports (for data output). Combining VBA and SQL is the most efficient way to write Access programs.

During the course we will consider and develop a multi-table application, from design of tables and relationships to design of user interface forms for input; and forms, queries, and reports for data output. This application will be based on a small retail business selling goods to customers. We can achieve good, efficient design by practising and following a few basic “common sense” rules. A more formalised procedure, termed normalisation, which is used to group data into tables and tables into database systems, is mentioned briefly on this course.

Participants are expected to be familiar with the basics of Access - the creation, use, and modification of simple ‘flat’ tables, and simple queries, forms, and reports. No previous experience of either relational techniques or of VBA or SQL programming is assumed or required, as these will all be taught from first principles on this course.

Programme details

Course begins: 17 Jan 2022

The first session will be for course orientation and an Introduction to Teams  and Canvas.

During the course we will develop one main relational database project, which will be a simple multi-table application based on a small retail business selling goods to customers. This course will start with an elementary introduction to relational database systems, starting with the concept of relationships - what they are and why they are needed. This will be followed by a general overview of table objects, table types, and the basic connections required in a relational system. Design rules will be introduced on a ‘common sense’ basis.

On a practical level the course will examine the underlying design techniques and methods by which the relational objects and their contents can be constructed and manipulated in Microsoft Access. This includes using VBA and SQL.

The following list gives some of the topics which this course aims to cover:

  • The basic concepts of a relational database.
  • Defining and designing table objects for a relational database system.
  • Defining relationships - use of key fields.
  • Different types of relationships - one to one; one to many.
  • Design rules, data integrity and validation.
  • Building a practical relational database system with Microsoft Access.
  • The user interface: creating multi-table queries; forms for data input; and 'smart' forms and reports for data output.
  • An introduction to VBA and SQL.

Certification

Students who register for CATS points will receive a Record of CATS points on successful completion of their course assessment.

To earn credit (CATS points) you will need to register and pay an additional £10 fee per course. You can do this by ticking the relevant box at the bottom of the enrolment form or when enrolling online.

Coursework is an integral part of all weekly classes and everyone enrolled will be expected to do coursework in order to benefit fully from the course. Only those who have registered for credit will be awarded CATS points for completing work at the required standard.

Students who do not register for CATS points during the enrolment process can either register for CATS points prior to the start of their course or retrospectively from the January 1st after the current full academic year has been completed. If you are enrolled on the Certificate of Higher Education you need to indicate this on the enrolment form but there is no additional registration fee.

Fees

Description Costs
Course Fee £250.00
Take this course for CATS points £10.00

Tutor

Ms Judith A Harley

Judith Harley, MA, is a physics graduate and freelance computer consultant who advises on, and designs, commercial and private database, spreadsheet, and Visual Basic applications. She has taught computing courses at Oxford University for over 20 years.

Course aims

This is a practical and theoretical course in understanding and designing a simple relational database system using Microsoft Access. The course aim is to introduce the concept of relationships - why they are needed and how they are structured - and to develop the underlying design techniques and methods by which the various relational objects - tables, queries, forms, and reports - and their contents can be manipulated. This includes some programming in a combination of Visual Basic for Applications (VBA) and Structured Query Language (SQL).

Course Objectives

1.  To introduce and develop the concept of relational database systems.

2.  To introduce database design principles within the framework of Microsoft Access, using tables, queries, forms, and reports.

3.  To introduce and develop Visual Basic for Applications (VBA) and Structured Query Language (SQL) programming skills.

Teaching methods

The course will be a mix of approximately 50% formal group teaching online and approximately 50% individual practical work following pre-recorded video topics. During the online teaching sessions students will have the opportunity to ask questions about the pre-recorded session content. Students are encouraged to actively participate and question during the online teaching and to regard this as an opportunity to interact with other participants and the course tutor.

Learning outcomes

By the end of this course students will be expected to see improvements in the following areas of knowledge and skills:

1.  Development of knowledge and understanding of relational database systems;

2.  Development of various skills, including SQL and VBA programming:

  • To analyse - developing the logic to analyse relationships in a relational exercise or situation;
  • To organise - organising and ordering the information given and information required;
  • To develop and design - designing and building a suitable user interface, with appropriate data objects and SQL and VBA programming;
  • To communicate -communicating with the user during design, development and maintenance;
  •  To evaluate -  evaluating requirements; using suitable structures; evaluating the suitability of the result.

Assessment methods

Assessment will take the form of a single worked practical relational database project based on material taught during the course, accompanied by a descriptive written account. Each student is encouraged to complete this.

Students must submit a completed Declaration of Authorship form at the end of term when submitting your final piece of work. CATS points cannot be awarded without the aforementioned form.

Application

We will close for enrolments 7 days prior to the start date to allow us to complete the course set up. We will email you at that time (7 days before the course begins) with further information and joining instructions. As always, students will want to check spam and junk folders during this period to ensure that these emails are received.

To earn credit (CATS points) for your course you will need to register and pay an additional £10 fee per course. You can do this by ticking the relevant box at the bottom of the enrolment form or when enrolling online.

Please use the 'Book' or 'Apply' button on this page. Alternatively, please complete an application form.

Level and demands

Participants are expected to be familiar with the basics of Access - the creation, use, and modification of simple ‘flat’ tables, and simple queries, forms, and reports. No previous experience of either relational techniques or of VBA or SQL programming is assumed or required, as these will all be taught from first principles on this course.

Most of the Department's weekly classes have 10 or 20 CATS points assigned to them. 10 CATS points at FHEQ Level 4 usually consist of ten 2-hour sessions. 20 CATS points at FHEQ Level 4 usually consist of twenty 2-hour sessions. It is expected that, for every 2 hours of tuition you are given, you will engage in eight hours of private study.

Credit Accumulation and Transfer Scheme (CATS)