Introduction to Access VBA & Development

2 Days, 9:00-4:00

Course Overview

This 2-day instructor-led course introduces students to the Visual Basic for Applications (VBA) programming environment of Microsoft Access and demonstrates how VBA can be used to develop a Microsoft Access database application. Through lecture and hands-on practice, students will be introduced to programming concepts and complete a variety of projects that use VBA to automate Microsoft Access. At the completion of the course, users should:

Course Objectives

• Be familiar with the basics of developing applications in Access
• Create and convert macros into VBA
• Make Access start up with the form you select
• Create subroutines and understand how to use variables to generate an output
• Understand and create input boxes and message boxes
• Create and use decision-making programs and programming loops
• Understand and use the DoCmd object
• Recognize and understand ADO techniques and use ADO to work with forms and recordsets
• Apply a range of debugging tools
• Replace obscure error messages with meaningful ones

Development Basics

• Understanding The Development Environment
• Understanding Splitting Databases
• Linking To Data Files
• Using Queries For Reports And Forms
• Developing Systems For Other Users

Macros and VBA

• Understanding Macros
• Creating Macros
• Assigning Macros
• Creating More Complex Macros
• Converting Macros To VBA

VBA Essentials

• Understanding The Programming Environment
• Exploring The Object Model
• Navigating With Forms
• Creating A Startup Form
• The VBA Editor Window
• Working With The Project Explorer
• Working With The Properties Window
• Understanding Modules
• Viewing The Code Behind Forms
• Standard Code Modules
• Understanding Naming Conventions

Interacting With Users

• Understanding Input Boxes
• Using The InputBox Function
• Using InputBox Function Arguments
• Understanding Message Boxes
• Creating A Simple Message Box
• Creating A Longer Message
• Breaking A Message Line
• Displaying Buttons And Icons
• Using The Message Box Function
• Responding To Different Buttons

Making Decisions

• Understanding The IF Statement
• Creating A Single-Line If Statement
• Creating A Block If Statement
• Creating Multiple If Statements
• Creating If Statements
• Using If For Multiple Text Conditions
• Understanding The Select Case Statement
• Using Select Case Statements With OR

Looping In VBA

• Understanding Loops
• Creating A While Loop
• Working With Counters
• Creating An Until Loop
• For…Each Loops
• Exiting From Loops
• For...Next Loops
• Creating Loops

The DoCmd Object

• Understanding DoCmd
• Navigating With DoCmd
• Moving Through Records
• Filtering Records With DoCmd
• Opening And Closing A Form With DoCmd
• Opening A Report With DoCmd
• Creating Variable Reports

VBA Subroutines

• Understanding The Development Environment
• Understanding Procedures
• Anatomy Of A Module
• Creating A Subroutine
• Creating A Function
• Calling Procedures
• Passing Parameters 
  
• Returning Values
• Scoping Procedures

Using Variables

• Understanding Variables
• Understanding Data Types
• Creating And Using Variables
• Implicit And Explicit Declarations
• Working With Numbers In VBA
• Dealing With Overflows 
  
• Working With Decimals In VBA
• Default Values
• Understanding The Scope Of Variables
• Procedure Level Scoping
• Module Level Scoping
• Passing Variables By Reference
• Passing Variables By Value

ActiveX Data Objects - Tables

• Understanding ADO
• Setting References Using VBA
• Creating A Table With ADO
• Creating Tables With ADO
• Removing A Table Using ADO
• Removing A Table With ADO 
  
• Populating A Form Using ADO
• Dealing With An Empty Table

ADO Forms And Recordsets

• Navigating Records In A Form
• Finding A Record
• Updating A Record
• Deleting A Record 
  
• Adding a Record

Debugging VBA

• Understanding Debugging
• Setting A Breakpoint
• Setting Additional Breakpoints
• Stepping Through A Procedure 
  
• Viewing The Call Stack
• Using Locals And Immediate Windows
• Adding A Watch

Handling Errors In VBA

• Understanding Error Types 
  
• Common Errors
• Understanding The On Error Statement
• Simple Error Handling
• Using The Err Object
• Using The Resume Statement
• Using Decision Structures In Error Handlers
• Forcing An Error
• Defining Custom Errors
• Working With Errors