Home > Store

Microsoft Office Access 2007 VBA

Register your product to gain access to bonus material or receive a coupon.

Microsoft Office Access 2007 VBA


  • Sorry, this book is no longer in print.
Not for Sale

eBook (Watermarked)

  • Your Price: $30.39
  • List Price: $37.99
  • Includes EPUB and PDF
  • About eBook Formats
  • This eBook includes the following formats, accessible from your Account page after purchase:

    ePub EPUB The open industry format known for its reflowable content and usability on supported mobile devices.

    Adobe Reader PDF The popular standard, used most often with the free Acrobat® Reader® software.

    This eBook requires no passwords or activation to read. We customize your eBook by discreetly watermarking it with your name, making it uniquely yours.





  • Copyright 2008
  • Dimensions: 7" x 9-1/8"
  • Pages: 408
  • Edition: 1st
  • Book
  • ISBN-10: 0-7897-3731-0
  • ISBN-13: 978-0-7897-3731-1

Business Solutions

Microsoft® Office Access 2007 VBA

Develop your Access 2007 VBA expertise instantly with proven techniques

Microsoft Office Access 2007 VBA builds on the skills you’ve already developed in creating database applications and helps you take them to the next level—using Visual Basic for Applications (VBA) to accomplish things you once performed manually. To facilitate this lofty goal, Access includes the VBA programming language. Even if you’ve never programmed, this book will help you learn how to leverage the power of VBA to make your work with Access more efficient than ever before. Microsoft Office Access 2007 VBA is for professionals who use Microsoft Access frequently in their daily work. You have serious work to get done and you can’t spend all day reading a computer book. This book teaches you the essential skills you need to automate your databases as quickly as possible.

Although written for Access 2007, the techniques and concepts covered will work in most versions of Microsoft Access.

Highlights of This Book Include

          •        Navigating within the Visual Basic Editor

          •        Using variables, constants, and data types

          •        Employing built-in functions

          •        Creating procedures

          •        Understanding object-and event-driven coding

          •        Working with arrays

          •        Understanding scope

          •        Working with forms

          •        Using selection controls

          •        Creating reports

          •        Exploring menus, navigation, and ribbons

          •        Using object models

          •        Working with data

          •        Defining database schema

          •        Using the Windows API

          •        Working with XML files

          •        Exploring Access SQL

On the Website

Download database files used in the book at www.quepublishing.com.

Category     Office Applications

Covers         Visual Basic for Applications 

User Level    Intermediate - Advanced

Scott B. Diamond is a seasoned database designer and Microsoft Access 2007 MVP. During the last 20+ years, he has designed databases on a wide range of platforms, including dBASE, FoxPro, SQL/DS, Lotus Approach, Lotus Notes, and, for the past 10 years, Microsoft Access. Scott has worked as a consultant, both in-house and freelance, and as a support professional at firms that are among the leaders in their industries. Scott spends some of his free time answering questions at the premier site for Access support: http://www.utteraccess.com.

Brent Spaulding started writing applications about 20 years ago and has utilized Microsoft Access since version 2.0. He looks forward to using Access well into the future. In July 2007, he received the Microsoft MVP award for Access, which recognizes his talent and contributions to the Access community.

Front cover bullets:

Edit and debug your code

Use looping and conditional statements

Understand the Access object- and event-driven architecture

Automate data entry

Learn how to use variables for dynamic automation

Create user-friendly applications for others

Create custom functions and objects

Customize the user interface

Manipulate data and objects with code



Download the examples

Sample Content

Online Sample Chapter

Microsoft Office Access 2007 VBA: Using Built-In Functions

Downloadable Sample Chapter

Download the chapter

Sample Pages

Download the sample pages (includes Chapter 4 and Index)

Table of Contents



Part I The Building Blocks

Chapter 1 Advantages of Access and VBA

Understanding Where Access Fits in Office

Understanding Access Programming Choices


    Using SQL

    Using VBA

Chapter 2 Using the Visual Basic Editor

First Look at the Visual Basic Editor

Explaining VBA Modules

Entering and Running Code

    Debugging Code

    Saving Code

Getting Help on Code

    Coding Shortcuts

Good Coding Habits

    Using a Naming Convention



Chapter 3 Using Variables, Constants, and Data Types

Declaring Variables and Constants

    Declaring Variables

    Using Option Explicit

    Naming Variables


    Declaring Constants

VBA Data Types

Referencing Syntax

Case Study:Using Form References

Chapter 4 Using Built-In Functions

What Are Functions?

Converting Data Types

    Converting to a Boolean Data Type

    Converting to a Date Data Type

    Converting to an Integer Data Type

    Converting to a String Data Type

    Converting to a Variant Data Type

    Converting Null Values

Working with Date Functions

    Returning the Current Date

    Performing Date Arithmetic

    Determining the Difference Between Two Dates

    Extracting Parts of Dates

    Creating Dates from the Individual Parts

    Creating Dates from String Values

    Extracting a Specific Date or Time Portion

    A Conversion and Date Example

Using Mathematical Functions

    The Abs Function

    The Int Function

    The Rnd Function

    A Mathematical Functions Example

Using Financial Functions

    The Ddb Function

    The FV Function

    The Pmt Function

    The Rate Function

    A Financial Functions Example

Manipulating Text Strings

    The Asc Function

    The Chr Function

    The Case Functions

    The Len Function

    The Left, Right, and Mid Functions

    The Replace Function

    The Split Function

    The Trim Functions

Formatting Values

    Applying User-Defined Formats

Domain Aggregate Functions

    The DLookup Function

    The DCount Function

    The DMax/DMin Functions

Using the Is Functions


    The MsgBox Function

    The InputBox Function

Case Study:Add Work Days

Chapter 5 Building Procedures

Types of Procedures



    Assigning a Data Type to a Function

    Public Versus Private

Passing Arguments

    Using Optional Arguments and Default Values

    Passing Arguments By Reference

    Passing Arguments By Value

Error Handling

    Using On Error Resume Next

    Using On Error Goto

Chapter 6 Conditional and Looping Statements

Introducing Flow of Control Statements

Using If...Then...Else

    A Simple If Statement

    More Complex Conditions

    Including an Else Clause

    Including an ElseIf Clause

Using Select Case

Using For...Next

    Using the Step Clause

    Other Ways to Set the Counter

    Nesting For...Next Loops

    Aborting a For...Next Loop

Using Do Loops

    A Simple Do Loop

    Do Loop Flavors

    Aborting a Do Loop

Using GoTo

Case Study: Calculating Bonuses

Chapter 7 Working with Arrays

Introducing Arrays

Declaring a Fixed-Size Array

Understanding an Array’s Index

    Using Option Base

Working with Array Elements

    Assigning Array Elements

    Using Array Element Values

Arrays with Multiple Dimensions

Expanding to Dynamic Arrays

    About ReDim

    Erase Statement

Chapter 8 Object and Event-Driven Coding

Understanding Objects

    Creating Objects in Code

    Reading and Setting Object Properties

Invoking Methods

Using Collections

Working with an Object Model

    Using the Object Model

    Using References

    The Object Browser

Creating Objects

Working with Events

Chapter 9 Understanding Scope and Lifetime

Scope Explained

    Procedure-Level Variables

    Module-Level Variables and Constants

    Public Variables and Constants

Measuring the Lifetime of a Variable or Constant

    The Lifetime of a Procedure-Level Variable

    The Lifetime of a Module-Level Variable

    The Lifetime of a Public Variable

Using Static Variables

Case Study:Tracking the Current User

Part II Working Within the User Interface

Chapter 10 Working with Forms

Opening and Closing Forms

    Opening a Form

    Passing Arguments Using OpenArgs

    Closing a Form

The Form Module

Form and Control Properties

Form Events

Case Study:Adding to a Combo Box

Chapter 11 More on Event-Driven Coding

Responding to Events

The Event Sequence for Controls

    Focus Events

    Data Events

    Control Specific Events

The Event Sequence for Forms

    Navigation Events

    Data Events

    Behind the Scenes: Data Buffers

The Event Sequence for Reports

Cancelling Events

Case Study:Validating Data

Chapter 12 Working with Selection Controls

Selection Controls

Populating a List Control

    A Filtering List Control

Adding to the List—Or Not

    Updating a Table/Query List

Working with Option Groups

Working with MultiSelect Controls

    Determining What Is and Isn’t Selected

Case Study: Selecting Multiple Items

Chapter 13 Working with Other Controls

Working with Text Boxes

    Key Properties of Text Boxes

    Tracking the Focus

Working with Check Boxes, Radio Buttons, or Toggle Buttons

Working with Subforms

Working with the Tag Property

Case Study: An Audit Trail

Chapter 14 Working with Reports

An introduction to the Report Module and Events

Opening and Closing Reports

    Opening a Report

    Closing a Report

Passing Argument Using OpenArgs

Populating the Report

    Applying a Filter and Sort Order

Handling Report-Level Errors

    What to Do When There Is No Data

Working with Subreports

Case-Study: Product Catalog

Chapter 15 Menus, Navigation, and Ribbons

Introducing Menus

Creating Form-Based Menus

Managing the Navigation Pane

Using Custom Ribbons

Chapter 16 Application Collections

Understanding Application Collections

Retrieving Lists of Objects

Working with Object Properties

Programmatically Determining Dependencies

Case Study:Version Control

Part III Working with Data

Chapter 17 Object Models for Working with Data

What They Are and Why We Need Them

Data Access Objects

ActiveX Data Objects

ActiveX Data Objects Extensions for Data Definition

Object Model Selection

Chapter 18 Creating Schema


Creating Databases

    Using the DAO Object Model

    Using the ADOX Object Model

Creating Tables

    Using the DAO Object Model

    Using the ADOX Object Model

Creating Fields

    Using the DAO Object Model

    Using the ADOX Object Model

Creating Indexes

    Using the DAO Object Model

    Using the ADOX Object Model

Creating Relationships

    Using the DAO Object Model

    Using the ADOX Object Model

Creating Queries

    Using the DAO Object Model

    Using the ADOX Object Model

Case Study: Updating an Existing Database Installation

Chapter 19 Data Manipulation

Connecting to a Data Source

    Using the DAO Object Model

    Using the ADO Object Model

Opening a Recordset

    Using the DAO Object Model

    Using the ADO Object Model

Inserting Data

    DAO’S Execute Method

    ADO’s Execute Method

    DAO’S AddNew Method

    ADO’S AddNew Method

Finding Data

    Limiting Records Retrieved

    DAO’s FindFirst, FindNext, FindLast, and FindPrevious Methods

    DAO’s Seek Method

    Using DAO’s Filter Method

    Using ADO’s Find Method

    Using ADO’s Seek Method

    Using ADO’s Filter Property

Updating Data

Deleting Data

    DAO’S Delete Method for a Recordset Object

    ADO’s Delete Method for a Recordset Object

Case Study: Backing Up Data

Chapter 20 Advanced Data Operations

Creating Linked Tables

Data Definition Language

Schema Recordsets


Part IV Advanced VBA

Chapter 21 Working with Other Data Files

Understanding File I/O

Opening Files

    About mode

    About access

    About locking

    Demonstrating Opening a File

Reading from Files

    Using Input

    Using Line Input #

    Using Input #

Writing to Files

Printing to Files

Case Study:Using .ini Files

Chapter 22 Working with Other Applications

Understanding Automation

Setting Object References

Creating Objects

    Using CreateObject

    Using GetObject

    Using Early Binding

Working with Automation Servers

    Talking To Excel

    Talking to Word

Case Study:Using Excel Charts

Chapter 23 Working with XML Files

Understanding XML

Using ExportXML

    An Example of Exporting

    Exporting a Web-Ready File

    Exporting Related Data

Using ImportXML

    An Import Example

Chapter 24 Using the Windows API

Declaring API Calls

Using API Calls

API Calls You Can Use from Access

    Check Whether an Application Is Loaded

    Capture the Network Login ID

    Retrieving the Name of the Program Associated with a Data File

Knowing When to Use the Windows API

Case Study: Capturing a Filename to Use for Processing

Appendix A Review of Access SQL

Introduction to SQL

SQL Structure and Syntax

The SELECT Statement

    The SQL Predicates

    The SQL FROM Clause

    The SQL WHERE Clause

    The SQL ORDER BY Clause

    The SQL GROUP BY Clause

    The SQL HAVING Clause

The INSERT Statement

The UPDATE Statement

The SELECT INTO Statement

The DELETE Statement


0789737318    TOC    10/31/2007


Download the index


Download the introduction

More Information

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information

To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.


Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.


If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information

Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.


This site is not directed to children under the age of 13.


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information

If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information

Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents

California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure

Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact

Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice

We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020