Programming Microsoft Access 2002

or Microsoft Access 2000

or Microsoft Access 2003

(DataSchenk Note: the focus of this class is on programming, not the version...)

MOC 2657; 3 Day Training Class

This course teaches programming concepts and how to add simple functionality to a Microsoft Access 2002 database by using Microsoft Visual Basic for Applications.

Audience

The audience for this course includes the following:

• Knowledge workers and technologists who are familiar with using Access and other Microsoft Office applications but whose previous programming experience is limited
• Any programmers who require an introduction to Office development using Visual Basic

After completing this course, students will be able to:

• Use the programming capabilities of Microsoft Visual Basic for Applications in Microsoft Access 2002 applications to write custom functions and event-driven procedures.
• Validate data.
• Find and trap errors.
• Gain access to and manipulate data in tables by using Microsoft ActiveX Data Objects (ADO) and the Structured Query Language (SQL).
• Create a custom switchboard for an Access application.

Prerequisites

The course assumes that students have the following skills:

• Ability to design a relational database - we recommend completion of three levels of Access training - version 2000 or later.
• Ability to use Microsoft Access to create a database with multiple tables, forms, and reports, and to create queries

Microsoft Certified Professional Exams

This course is not currently associated with any Microsoft certification exams.

Course Outline

Module 1: Introduction to Programming

The following topics are covered in this module:

• Using Visual Basic Help
• Writing Procedures
• Working with Variables
• Controlling Program Flow
• Calling Procedures

After completing this module, students will be able to:

• Use Visual Basic Help.
• Use the Visual Basic Editor to add code to applications.
• Declare and use variables.
• Use built-in functions to obtain values from users.
• Create and call Sub procedures.
• Use conditional statements to control the flow of programs.

Lab 1.1: Introduction to Programming

Module 2: Responding to User Actions

The following topics are covered in this module:

• Overview of the Class Application
• Adding Event Procedures
• Working with Events
• Using Objects, Properties, and Methods
• Using the DoCmd Object

After completing this module, students will be able to:

• Create an event procedure.
• Add an event procedure to a form.
• Respond to user actions programmatically.
• Perform specific tasks by using the DoCmd object.

Lab 2.1: Responding to User Actions

Module 3: Validating Data

The following topics are covered in this module:

• Overview of Data Validation
• Validating Data Without Using Code
• Validating Data by Using Code

After completing this module, students will be able to:

• Differentiate between data validation at the table level, form level, and code level.
• Use the built-in validation tools in Microsoft Access.

Lab 3.1: Validating Data

Module 4: Using Functions and Understanding Scope

The following topics are covered in this module:

• Using Functions
• Understanding Scope

After completing this module, students will be able to:

• Describe the appropriate use of functions.
• Call built-in and custom functions in an application.
• Create a custom function.
• Describe the different levels of scope available to variables and procedures.
• Define variables at the appropriate level of scope.
• Define procedures at the appropriate level of scope.

Lab 4.1: Using Functions and Understanding Scope

Module 5: Using Debugging Tools

The following topics are covered in this module:

• Stepping Through Code
• Viewing Variables

After completing this module, students will be able to:

• Set breakpoints to stop program execution.
• Run selected portions of code.
• Test data and procedures in the Immediate window.
• Monitor variable values in the Locals window.
• Add and edit watch expressions.

Lab 5.1: Using Debugging Tools

Module 6: Handling Run-Time Errors

The following topics are covered in this module:

• Types of Errors
• Adding Error Handlers

After completing this module, students will be able to:

• Trap run-time errors.
• Create error handlers.
• Use the Err object.

Lab 6.1: Handling Run-Time Errors

Module 7: Looping Through Code

The following topics are covered in this module:

• Overview of Looping Structures
• For Loops
• Do Loops

After completing this module, students will be able to:

• List the different looping structures available in Visual Basic for Applications.
• Write a For...Next loop to perform execution of code statements a fixed number of times.
• Write a Do...While and a Do...Until loop to perform execution of code statements a variable number of times.
• Describe the difference between beginning-of-loop and end-of-loop evaluation methods, and write code statements to accomplish each type of evaluation.

Module 8: Working with Records

The following topic is covered in this module:

• Overview of Recordsets
• Retrieving Data
• Structured Query Language (SQL)
• Manipulating Data

After completing this module, students will be able to:

• Define recordsets.
• Access data in tables by using ADO recordsets.
• Retrieve data by using SQL and ADO recordsets.
• Manipulate data by using SQL and ADO recordsets.

Lab 8.1: Working with Records

Module 9: Working with XML

The following topics are covered in this module:

• About XML
• Importing XML
• Exporting XML
• Transforming XML
• Modifying XML using the DOM

After completing this module, students will be able to:

• Describe XML and related technologies.
• Import XML into Access.
• Export XML from Access.
• Transform XML by using the Extensible Stylesheet Language for Transformations (XSLT).
• Modify XML by using the DOM.

Module 10: Finalizing Your Application

The following topics are covered in this module:

• Reviewing Your Application
• Enhancing the User Interface
• Packaging for Delivery
• After completing this module, students will be able to:
• List four good coding practices and describe why you should use them.
• Describe the testing criteria for an application.
• Customize a switchboard.
• Hide code by creating an MDE file.
• Describe how to use the Compact and Repair utility to maintain your database application.
• Describe why and how to use the Database Splitter utility.
• Describe when and how to use the Packaging Wizard to prepare an application for distribution.

Lab 10.1: Finalizing Your Application