COURSE NAME: Access/VBA BootCamp (4060)

Unit objectives help students become comfortable with the course, and also provide a means to evaluate learning. Upon completion of this course, students will be able to:

• Construct a substantial application with VBA
• Understand the foundations of Visual Basic for Applications
• Understand the concepts behind classes and objects
• Use Automation to link Office applications
• Create custom objects using the Class Module feature
• Debug your programs and implement robust error handling
• Add support for multiple users
• Publish your Access database on the Internet
• Optimize and add polish to your finished database application

Unit 1: Access 2000 – A Long Journey

• Access Projects
• Data Access Pages
• Sub Datasheets
• Programmability
• JET 4.0
• Unicode
• Data Types
• Locking
• Replication

Unit 2: Designing Applications. What Is An Access Application?

• The Analysis Phase; Requirements Analysis, Technical Analysis, Data Analysis,
• Design and Coding; Designing for Change Coding
• Testing & Documentation
• Macros Or VBA?
• Why and when you Should Use VBA
• Trapping Keystrokes Throughout an Application
• Carrying Out Actions when a Database is Opened – the Autoexec Macro
• Moving to VBA

Unit 3: Introduction to Event Driven Programming

• Examples of Events
• How Windows Handles Events
• Some Definitions
• So Many Event & default events
• Handling Events in Access with VBA
• What are Actions and Methods?
• Compiling Code
• Other Events

The VBA IDE

• Components of the VBA IDE
• Project Explorer
• Code Window
• The (General) Object

Converting Macros to VBA

• Completing the Company Contacts Form

Unit 4: Chapter 4: Creating Code

• Procedures
• Modules
• Subroutines and Functions

Procedure Declaration

• Function Types
• Parameters or Arguments
• Optional Arguments
• Using Optional Arguments to Aid Maintenance
• Default Values
• Named Arguments

Built-In Functions

Variables

• Declaring Variables
• Manipulating Variables
• Variable Types
• Boolean
• Byte
• Currency
• Double and Single
• Integer and Long
• String
• Object
• Hyperlink
• Variant
• Date
• Formatting Variables

Constants

Intrinsic Constants

Variable Scope and Lifetime

• Local Variables
• Static, Global, and Public Variables
• Public vs. Private

Naming Conventions

• Keep Names Meaningful
• Prefix All variables and Constants With Their Type
• Prefix Global Variables with their Scope
• Naming Conventions for Controls, Objects, Constants, and Summaries

Unit 5: Controlling the Program

Programming Structures

• Expressions
• Selection Structures
• If…Then…
• If…Then…Else…
• ElseIf…
• Logical Operations with the If statement
• Select Case
• IIf
• Operator Precedence

Repetition

• Loops
• For…Next
• Do…Loop
• Nested Control Structures
• Exiting a Control Structure

Arrays

• Static Arrays
• Upper and Lower Bounds
• Dynamic Arrays
• Redimensioning Existing Arrays
• The Dangers of ReDim

Unit 6: Using Access Objects

• Object-Oriented Programming
• Objects
• Properties
• Methods
• Classes
• The Advantages of Object-Orientation
• Object Models
• Using Object Models in VBA
• Access 2000 Objects
• The Access Object Object
• Dynamic List Boxes
• Changing the Switchboard
• Dynamic List Boxes – The Return
• Custom List Box Summary
• Forms and Reports
• Control Types
• The Err Object
• Referring to Objects
• Special Objects
• The Me Property
• The Object Browser

Unit 7: Using DAO

DAO vs. ADO

• A Brief History Of DAO
• The Future of DAO
• OLEDB and ADO

The DAO Hierarchy

• The DBEngine Object
• The Workspace Object
• The Database Object
• The TableDefs Collection
• The QueryDefs Collection
• The Recordsets Collection
• Different Types of Recordset
• Building Recordsets Dynamically
• Default Types
• Default Types
• Requerying Data in Recordsets

Working with Recordsets

• Examining Field Values
• Moving Through Recordsets
• Counting Records in a Recordset
• Table-Type Recordsets

Dynasets and Snapshots

Unit 8: Data Management Techniques

The Challenge – Flexibility vs. Manageability

The Requirement – Ad Hoc Query Functionality

• Why Not Use Parameterized Queries?
• Building a Query by Form Interface

Creating and Modifying QueryDefs

• Working with SQL

Two Approaches to Displaying Results

• Modifying the QueryDef
• Using a Matching Records table
• Other Considerations

Building Tables

• The Ten Steps

Unit 9: External Data Techniques

Other Applications

• Databases
• Exporting Foreign Keys
• Spreadsheets

Text Files

• Specifications

Electronic Mail

Using External Data

• The Database Splitter
• Linked Tables
• Differences between Linked and Local Tables

Unit 10: Reports

• Starting Off
• Page Numbers
• IIf
• Dates
• Summarizing
• Expressions
• Events
  • Open
  • Activate
  • Deactivate
  • Close
  • Error
  • Format
  • Cancel and FormatCount
  • Print
  • Retreat
  • NoData
  • Page
  • When To Use the Different Events
  • Format
  • Print
  • Retreat
  • The FormatCount and PrintCount Properties
• Filters

Unit 11: Advanced Programming Techniques

Arrays

• Multi-dimensional Arrays
• Dynamic Multi-dimensional Arrays
• Referencing Elements in a Multi-dimensional Array
• Memory Considerations
• Erasing Arrays
• Parameter Arrays
• The Array Function
• The GetRows Method
• Detecting Arrays
• The IsArray Function
• The VarType Function
• The TypeName Function

Arguments

• Passing Arguments By Value

Dynamic-Link Libraries (DLLs)

• Declaring a DLL in Code
• Enums
• Aliases
• Using the ByVal Keyword
• Passing Strings
• Passing Arrays to a DLL
• Type Conversion
• User-defined Data Types
• Null Pointers
• The Dangers of Using DLLs

Custom DAO Properties

• Database Properties

Unit 12: Error Handling and Debugging

Planning for Errors

• The Design
• User Analysis
• Tables
• Queries
• Forms and Reports
• Modules
• Data Driven Design
• Object Oriented Techniques
• Option Explicit
• Syntax Checking
• Comments

Compiling

Testing

• Functional Testing
• Usability Testing
• Destructive Testing
• Maintenance

Types of Errors

Using External Data

• Syntax Errors
• Compile Errors
• Run-Time Errors
• Semantic Errors
• Locating Errors
• Other Errors

Debugging

• Program Execution
• Breakpoints
• Assertions
• The Stop Statement
• Continuing Execution
• Stopping Execution
• Stepping Through Code
• Rerunning Lines of Code
• Skipping Lines of Code
• Changing Code
• Commenting Out Lines
• Bulk Commenting
• The Call Stack
• The Immediate Window
• The Locals Window
• The Watch Window
• Instant Watches
• Hovering

Error Handling

• The Err Object
• Visual Basic Errors
• Form Errors
• Access and VBA Errors
• Data Access Errors
• Data Access Objects
• Active X Data Objects
• User Defined Errors
• The Error Stack

Debugging you Debugging Code

Unit 13: Using Classes Class

Modules and Custom Objects

• What Are Objects?
• Why Use Objects?
• Abstraction
• Encapsulation
• Polymorphism
• Inheritance
• The MyRectangle Class
• Abstraction
• Encapsulation
• Polymorphism
• Polymorphism through Early Binding
• Inheritance
• The PaymentStats Class
• The Business Need
• The Object Model
• Building The Interface
• Implementing the Logic
• Finishing Touches
• Benefits of an Object-Oriented Approach
• Class Events
• The Class Initialize and Terminate Events

Forms as Class Modules

• Creating Custom Properties for Forms
• Custom Form Methods
• Creating Multiple Instances of Objects

Collections – Creating a Hierarchy

Getting the Most from Class Modules

• Hide Your Data
• Don’t Overdo It
• Avoid Get and Set Methods
• Get the Object Model Right
• Make it Look Easy

Unit 14: Libraries and Add-Ins

Library Databases

• Creating a Library Database
• Considerations

Class Libraries

• An ADO Class Library

Add-Ins

• The Language Converter Add-In
• Creating the Add-In
• The Complete Language Converter Add-In
• The Color Schemes Add-In
• Creating Add-Ins Summary
• Updating your Add-In

Unit 15: Automation

What Is Automation?

• Interfaces and Object Models

Object References

Creating Automation Objects

Microsoft Outlook

Microsoft Word

• Word Summary

Microsoft Excel

• Graph Summary
• Office Assistant

Unit 16: Multi-User

Multi-Developer Issues

Record Locking in Access 2000

Page Locking vs. Record Locking

• Record Level Locking
• Optimistic and Pessimistic Locking
• Choosing a Locking Strategy
• Setting the default Record Locking Mechanism
• Implementing Record Locking on Forms
• Recordsets and Record Locking
• The Type Argument
• The Option Argument
• The LockEdits Argument
• The LockEdits Property
• Handling Record Locking Errors
• Optimistic Locking Errors
• Pessimistic Locking Errors

Security

• Why Security?
• Setting a Database Password from VBA
• Protecting Your Code
• The Access Security Model
• Manipulating Users and Groups
• Enumerating Users and Groups
• Creating a New User
• Creating a New Group
• Adding a User to a Group
• Changing a Password
• Setting Object Permissions with Visual Basic
• Analyzing the Output
• Workspaces

Compiling

• What is Compilation?
• How do we Compile Code?
• When does Code Decompile?
• Using MDE files
• Saving a Database as an MDE file
• Restrictions on MDE Files
• Using MDE’s with Add-Ins and Libraries
• Encrypting

Unit 17: The Internet The Internet

The HyperLink Data Type

How to use Hyperlinks

Hyperlinks in VBA

Custom Hyperlink Form

Unit 18: Optimizing Your Application

Efficiency

Reducing Memory Overhead

• Use the Right Data Type
• Group Procedures into Modules
• Reclaim Memory where Possible
• Don’t Load Unnecessary Libraries
• Save as an MDE
• A Final Recommendation – Buy More Memory!

Increasing Execution Speed

• Timing the Code Samples
• Use Constants
• Don’t be vague!
• Use Specific Object Types (Early Binding)
• Use Variables, Not Properties
• Avoid Slow Structures

• Immediate If (IIf)
• Use Integer Arithmetic Where Possible
• Use In-Line Code
• Use DoEvents Judiciously
• Use the Requery Method, not the
• Requery Action
• Use Me
• Speed Up Database Operations
• Creating Test
• Generating Random Numbers
• Populating the Test Table
• Use Indexes
• Use Appropriate Recordset Types
• Use Bookmarks

Increasing Apparent Speed

• Startup Forms and Splash Screens
• Using a Start-Up Form
• Use Gauges
• Remove Code from Form Modules
• Pre-Load and Hide Forms
• Cache Data Locally

Network Considerations

• Search on Indexed Fields in attached Tables
• Put Non-Table Objects on the Local Machine
• Disable AutoExpand

Finishing Touches

• Compact the Database
• Compile All Modules

Open Databases Exclusively