Microsoft Access: Visual Basic for Applications (VBA) Programming (2 day)

Course Overview

Microsoft Access is the most powerful desktop database design product to date from the Microsoft Office professional suite. This course teaches experienced Access users about the greater advantages to be gained by combining the built-in features of Access with the programming capabilities of Visual Basic for Applications (VBA).

The course is designed to give delegates a thorough overview of programming within the Access environment, enabling them to improve the functionality of their Access databases, whilst providing a useful introduction to programming, in particular the Visual Basic programming language.

Audience

Database developers and experienced users of Microsoft Access who wish to utilise the power of VBA. This course covers the basic programming structures such as subroutines and functions and no previous knowledge of programming is necessary.

This course is particularly beneficial for those with a sound knowledge of Access 97 or Access 2000 who are familiar with other features available such as tables, queries, forms, reports, macros and relationships.

Skills Gained

By the end of the course the delegate will be able to:

• Plan and design robust systems
• Build multi user applications
• Design advanced queries, data entry forms and reports
• Build secure applications
• When to use VBA instead of macros

Prerequisites

Delegates should be fully familiar with either Access 97 or Access 2000 and be able to create tables, queries, forms, reports, macros and relationships. These concepts are covered in Access 97 Level 3 (A2C), or course Access 2000 Level 3 (A3C). Prior knowledge of programming is not necessary.

Course Outline

Review of Macros

What is Visual Basic for Applications?

Introduction to the programming environment

• Modules & Procedures
• Creating & Opening a Module
• Module Window Toolbar & Keys

Creating Functions & Procedures

• Function & Sub statements
• Event Procedures

Access Objects, Methods & Events

• Applying Methods to & Opening Objects
• Data Handling techniques
• Objects & collections
• Form & Field handling
• Referencing Objects Literal, Syntax, Collection & Intrinsic
• Defining Recordsets, Dynasets & Workspaces
• Form, Report & Control Object Events & Properties
• Application Initiated Events
• Order of Events
• Foreground & Background Processes

Variables

• What is a variable?
• Declaring Public, Private & Static Variables
• Dimensioning Variables
• Scope Statements
• Data Type Conversion Functions

Object Variables

• Data Access Object Model
• DBEngine & Workspace Objects including ODBCDirect
• Creating Database Object Variables including the OpenRecordset Method

Processing data using SQL commands

Testing & Debugging Applications

• Starting, Halting & Tracing Execution
• Resolving Compile and Run-time Errors
• Working with Breakpoints
• Using the Debug Window

Error Handling & Trapping

• On Error Statement & Err Object

Control Structures

• IF...Then...Else statements
• Select Case statements
• For...Next loop statements
• Do...Loop statements
• While...Wend statement
• With statement
• For Each statement

Declaring & Calling DLL Routines

Security

• Passwords
• Wizard
• Using MDE files

Replication overview

Microsoft Access: Visual Basic for Applications (VBA) Programming (2 day) / MAC-V2