Access 2007: VBA Programming (4 day)

Scheduled Dates (sort by: location | date)

Course Overview

The database solutions of today require a seamless integration of specific user requirements with Access objects. Utilising macros, VBA, SQL and embedded expressions, you can create fully distributable applications within Access.

In this hands-on course, you gain the skills necessary to create intelligent forms by manipulating properties, executing methods, and incorporating business logic to create a complete solution.

Audience

This course benefits those planning to create business solutions with Microsoft Access. Experience with Access at the level of Course 970, " Access 2007 Introduction ", is assumed. Programming experience is recommended, but not required.

Skills Gained

• Develop distributable applications with Microsoft Access 2007
• Incorporate user specifications to enhance application functionality
• Customise applications by dynamically setting properties and executing methods
• Assemble expressions into VBA statements using variables and intrinsic functions
• Control program flow with loops and decision-making logic
• Apply Data Access Objects (DAO) to incorporate business rules
• Centralise the error handling process

Course Outline

Automating Access with Macros

Designing complex macros

• Creating decision-making macros
• Implementing organisational techniques for macros
• Trapping macro errors

Maximising macro functionality

• Mastering event driven programming
• Binding macros to event properties

Transitioning to VBA

Beyond the macro object

• Identifying limitations of macros
• Converting macros to VBA

Exploring the Visual Basic Editor

• Traversing the Project Explorer
• Writing code with IntelliSense
• Troubleshooting code with debugging techniques

Programming Access Objects

Navigating the Object Model

• Methods
• Properties
• Events
• Communicating with individual objects

Interfacing with 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

Applying event logic

• Identifying the order of events
• Assigning requirements to events

Demystifying the Object Browser

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

Constructing VBA Expressions

Creating variables

• Naming rules and conventions
• Declaring objects
• Implicit vs. explicit data type conversions
• Specifying the scope and life of variables

Implementing built-in functions

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

Controlling Program Execution

Structuring code sequences

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

Logical and comparison operators

• Developing comparative statements
• Designing relational expressions

Optimising code for scalability and reuse

• Defining procedures with arguments
• Returning values with functions

Manipulating and Managing Data

Data Access Objects (DAO)

• Executing action SQL statements
• Retrieving data into Recordsets
• Building and modifying parametised queries
• Accessing multi-valued fields using the ComplexType object

Designing intelligent forms

• Interacting with unbound forms and controls
• Changing data sources within code

Processing Runtime Errors

• Classifying errors
• Working with the Err object
• Logging and error notification
• Creating error handling strategies

Getting Ready for Distribution

• Customising the ribbon
• Configuring start-up options
• Securing your Access database using SharePoint services
• Locking down the database

Access 2007: VBA Programming (4 day) / MAC07-BA