Access Advanced (VBA)

Course Overview

To develop database management applications.

Prerequisites

Excel Intermediate and Windows Relational database notions.

Syllabus

Day 1

Optimization:

• Creating synchronized drop-down lists:
• Creating the first drop-down list;
• Creating the second drop-down list;
• Using synchronized drop-down lists;
• Creating a macro to update the second drop-down list;
• Creating an event procedure to update the second drop-down list;
• Using the “Column” property;
• Using the Nz function;
• Using and inserting an Excel workbook in a form;
• Advanced use of sub-forms;
• Creating the query;
• Creating the sub-form;
• Creating the calculation.

Introduction to SQL queries:

• Defining the SQL language;
• Understanding the use of SQL language;
• Accessing the SQL language;

Creating simple SQL expressions:

• Using the SELECT instruction;
• Using the FROM, WHERE, GROUP BY, HAVING and ORDER BY clauses;
• Using the ALL, DISTINCT, DISTINCTROW and TOP predicates;
• Creating more complex SQL expressions;
• Using the UNION operation;
• Using the SELECT… INTO, UPDATE, DELETE and INSERT INTO instructions.

Day 2

Introduction to programming:

• Using modules;
• Creating a module;
• Differentiating types of procedures;
• Creating custom functions;
• Understanding the notion of variables and constants;
• Enabling the Explicit option by default;
• Creating and using variables;
• What is a constant?;
• Creating and using constants;
• Managing objects, their methods and properties;
• Defining properties and methods;
• Understanding the notion of objects;
• Using the object Explorer;
• Using the DoCmd object;
• Controlling the execution of your programs;
• Working with the If instruction formats;
• Mastering the Select Case instruction.

Advanced programming:

• Using the ActiveX Calendar control;
• Creating the Calendar control;
• Writing the VBA code needed to use the Calendar control;
• Using error management in VBA;Using the On Error instruction;
• Using a list box with multiple selections;
• Creating a list box with multiple selections;
• Creating a list box, an action button and the code needed to display results.