Access: VBA Programming (4 day)

Scheduled Dates (sort by: location | date)

Course Overview

With the advanced capabilities of VBA, you can fully exploit the power of Microsoft Access. Using rapid application development techniques, you build applications that increase productivity.

In this hands-on course, you gain practical experience programming with VBA. You learn to write and debug code that incorporates business rules with Access objects.

Audience

This course benefits those planning to create business solutions with Microsoft Access 2003 or a prior version of Access. Course 307, " Access 2003 Introduction ", or similar experience is assumed. Programming experience is useful, but not required.

Skills Gained

• Develop applications with Microsoft Access 2003 using Visual Basic for Applications (VBA)
• Identify and populate event properties to satisfy design specifications
• Modify object properties and invoke object methods to customise applications
• Create VBA statements using variables and built-in functions
• Build loops and decision logic
• Apply Data Access Objects (DAO) to incorporate business rules
• Integrate Access with external applications through automation

Course Outline

Access Macros and VBA

The power of macros

• Creating decision-making macros
• Advantages and disadvantages of macros
• Converting macros to VBA

Visual Basic Editor

• Navigating the Project Explorer
• Editing code with the Code Window

Event-Driven Programming

Building event procedures

• Binding macros to event properties
• Building events with wizards

Improving code readability

• Applying IntelliSense tools
• Exploiting coding techniques

Event logic

• Identifying the order of events
• Assigning requirements to events

Manipulating Objects

Objects and Object models

• Methods
• Properties
• Events
• Accessing and using individual objects
• Applying the Access Object Model

Programming Access objects

• Application
• Forms collection
• Form and report object
• Controls collection
• Bang (!) vs. Period (.)
• Exploiting the ComboBox, ListBox, Option Group and TextBox
• Writing code with the Me keyword

Object Browser

• Finding objects
• Object libraries
• Locating methods, properties and events
• Gateway into the Help system

Language Concepts

Creating variables

• Naming rules and conventions
• Declaring objects
• Implicit and explicit conversions
• The scope and life of variables

Implementing built-in functions

• Date, string and file system functions
• Integrating symbolic and intrinsic constants

Total Control with VBA

Controlling execution sequence

• Creating decision-making logic
• Building iteration statements
• Queries and the Immediate If (IIf)
• Populating fixed and dynamic arrays

Sub and function procedures

• Arguments
• Calling
• MsgBox
• Returning values

Operators and expressions

• Defining comparative statements
• Constructing relational expressions

Data Programming

Data Access Objects (DAO)

• Manipulating data using Recordsets
• Building and modifying QueryDefs
• Populating parameter queries

Verifying Form data

• Bound and unbound forms and controls
• Changing data sources within code
• Executing SQL statements

Advanced Functionality

Communicating with SQL Server

• Utilising pass-through queries
• Binding ADO Recordsets to forms

Automation with Access

• Early vs. late binding
• Controlling external applications

Trapping Errors and Debugging

Develop error handling

• Classifying errors
• Working with the Err object
• Managing runtime errors

Exploiting debugging tools

• Testing and validating code
• Troubleshooting logic issues

Access: VBA Programming (4 day) / MAC-V4