Microsoft Office Access 2007 VBA [NOOK Book]

Overview

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.

...
See more details below
Microsoft Office Access 2007 VBA

Available on NOOK devices and apps  
  • NOOK Devices
  • NOOK HD/HD+ Tablet
  • NOOK
  • NOOK Color
  • NOOK Tablet
  • Tablet/Phone
  • NOOK for Windows 8 Tablet
  • NOOK for iOS
  • NOOK for Android
  • NOOK Kids for iPad
  • PC/Mac
  • NOOK for Windows 8
  • NOOK for PC
  • NOOK for Mac
  • NOOK Study

Want a NOOK? Explore Now

NOOK Book (eBook)
$17.99
BN.com price
(Save 43%)$31.99 List Price

Overview

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

Read More Show Less

Product Details

  • ISBN-13: 9780132714136
  • Publisher: Pearson Education
  • Publication date: 12/10/2003
  • Sold by: Barnes & Noble
  • Format: eBook
  • Edition number: 1
  • Pages: 408
  • Sales rank: 926,588
  • File size: 11 MB
  • Note: This product may take a few minutes to download.

Meet the Author

Scott B. Diamond has been an information technology geek for more than 20 years. He has spent much of that time designing databases on various platforms. He started using Microsoft Access with Office 97 and has mastered all the subsequent versions. Besides developing database applications for the company where he’s employed as an applications administrator, Scott also does freelance work, developing Access applications and consulting. He has always maintained that he’s lucky his vocation is also his avocation, so he spends some of his free time helping people on web-based Q&A boards such as utteraccess.com (the premier support site for Access). He recently received Microsoft’s MVP award for Access in acknowledgment of his contribution to the Access community. Scott, an avid bicyclist, lives on Long Island, New York, with his wife and daughter. You can reach Scott at AccessVBA@diamondassoc.com or visit his website, www.diamondassoc.com.

Brent Spaulding started writing applications about 20 years ago, generally focusing on data and data analysis. He has designed systems that have a wide range of focus: gymnastics class management, product assembly analysis, equipment fault logging, and manufacturing management systems. He has used Microsoft Access since version 2.0 and looks forward to using Access well into the future. In July 2007 Brent, who is employed in the automotive industry, received the Microsoft MVP award for Access, which recognizes his talent and contribution to the Access community. He spends much of his personal time learning and helping others on websites such as utteraccess.com, where he is known as datAdrenaline.

Brent lives in southern Indiana with his wife and children.

Read More Show Less

Table of Contents

Introduction

Part I The Building Blocks

Chapter 1 Advantages of Access and VBA

Understanding Where Access Fits in Office

Understanding Access Programming Choices

Macros

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

Indenting

Documenting

Chapter 3 Using Variables, Constants, and Data Types

Declaring Variables and Constants

Declaring Variables

Using Option Explicit

Naming Variables

Constants

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

Interaction

The MsgBox Function

The InputBox Function

Case Study:Add Work Days

Chapter 5 Building Procedures

Types of Procedures

Subroutines

Functions

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

Overview

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

Subqueries

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

Crosstabs

0789737318 TOC 10/31/2007

Read More Show Less

Preface

Introduction

Introduction

  • What's in the Book
  • This Book's Special Features
  • The Examples Used in the Book

So, you've been using Access for a little while. Now that you have used Access to build databases for yourself, and maybe some friends and/or colleagues, you are ready for the next step: developing automated database applications. If you want quicker, easier, and more accurate data entry; faster searching; better reporting; the ability to manipulate data behind the scenes; and much more, this book is for you.

With Microsoft Office Access 2007 VBA we show you how to unleash the power of Access using Visual Basic for Applications (VBA). VBA is a superset of the Visual Basic programming language that combines Visual Basic command syntax and a rich assortment of functions with the capability to control objects in your application (hence the "A" in "VBA").

This book shows you VBA in action with real-world examples. We introduce you to programming and its use within Access and hold your hand every step of the way. With the information provided in this book you will explore commands, functions, properties, and methods and how to use them to make your applications dance to your tune and jump through hoops.

What's in the Book

This book isn't meant to be read from cover to cover, although you may find that you can't put it down! Instead, most of the chapters are set up as self-contained units that you can dip into and extract whatever nuggets of information you need at will. If you're a relatively new Access user, I suggest starting with the first one or two chapters in each of the book's four main parts to ensure you have a solid foundation in the fundamentals of working with data in Access tables by using queries, forms, and reports.

The book is divided into four main parts. To give you the big picture before diving in, here's a summary of what you find in each part:

  • Part I, "The Building Blocks"—The nine chapters in Part I introduce you to the building blocks you use to build VBA modules. We start by explaining the advantages of using VBA. From there we introduce you to the Visual Basic Editor (VBE). This is where you enter, edit, and test all your code. In Chapter 3, "Using Variables, Constants, and Data Types," we talk about the various ways you assign and identify data. And Chapter 4, "Using Built-in Functions," moves on to a discussion of the many functions that Access and VBA provide. In Chapter 5, "Building Procedures," the topic is procedures in their various forms and modules, the containers for your code. Chapter 6, "Conditional and Looping Statements," gets into the meat of coding as we go over important syntax for branching using conditions and repeating code with looping. We follow that with a chapter on using arrays. And then Chapter 8, "Object and Event-Driven Coding," explains how to launch your programs using object and event-driven coding. Part I closes with a chapter on scope, which covers lifetime and visibility of variables and procedures.

  • Part II, "Working Within the User Interface"—This part shows you how to use VBA to create a great user interface. You learn to work with form and report design and understand their components such as controls and sections. We introduce you to the wide variety of different controls available and show you how to use them. You learn what events are and how they are triggered and discover how to create different menus and use VBA to navigate through your application. Part II ends with a discussion of collections and how to reference Access objects.

  • Part III, "Working with Data"—This part of the book deals with working directly with data. You will learn the two main ways to get at data: Data Access Objects (DAO) and ActiveX Data Objects (ADO). DAO and ADO are presented in a comparative fashion. We cover these object models as they apply to the Access Connectivity Engine (ACE), the database engine under the hood of Access. ACE is the successor to the Joint Engine Technology (JET) database engine, so you will see JET and ACE terminology where appropriate. You will learn not only how to find, add, edit, and remove data, but also how to create and modify the structure of how data is organized (the shema). With DAO, ActiveX Data Objects eXtentsion (ADOX), and Data Definition Language (a subset of SQL), you can modify and create databases, tables, fields, indexes, relationships, and queries. This section also touches on some of the more advanced topics of database analysis, such as retrieving a list of all the relationships in your database or discovering all the computers that are connected to your database.

  • Part IV, "Advanced VBA"—In this final section, you encounter working with other types of data files such as Excel spreadsheets, Word documents, and flat files. You also learn the basics of automation with other Office applications. Finally, we show you how to call on the Windows Application Programming Interface (API).

  • Appendix—This includes a great reference on Structured Query Language (SQL) in its many different flavors and shows how to integrate SQL into your applications.

This Book's Special Features

Microsoft Office Access 2007 VBA is organized to give you a firm foundation for using VBA in a logical manner that builds your knowledge step by step. We have also made the book a functional reference for VBA techniques.

  • Steps—Throughout the book, each Access task is summarized in step-by-step procedures.

  • Code lines—Lines of VBA code, commands, and statements appear in a monospace typeface.

  • Required Text—Any text you need to enter will be boldfaced.

  • Italics—Technical terms being defined appear in italic, such as RecordSet Property.

  • Syntax—Within code statements certain arguments will be italicized to denote where you will need to substitute values relevant to your task. Brackets () are used to indicate optional arguments.

This book also uses the following elements to draw your attention to important (or merely interesting) information.


Note - Notes are used to provide sidebar information about the topic being discussed. They provide extra insights that help you understand the concepts being covered.



Tip - Tips tell you about Access methods that we have found to make coding with Access easier, faster, or more efficient.



Caution - Caution elements warn you about potential pitfalls waiting to trap your code, such as common errors that might occur, and how to avoid or fix them.




Cross-reference elements point you to related material elsewhere in the book.


Case Study - You'll find case studies throughout the book based on an Inventory Tracking application. They are designed to show you how to apply what you've learned.


The Examples Used in the Book

All the tables, objects, and code samples referred to in this book can be found at quepublishing.com. There will be a folder for each chapter. The files build on the examples from the previous chapters. The Introduction file will be pretty bare bones and just have the objects that don't pertain to specific lessons we cover. You can build on the Introduction files or use the files that already have the examples coded for you.


Read More Show Less

Customer Reviews

Be the first to write a review
( 0 )
Rating Distribution

5 Star

(0)

4 Star

(0)

3 Star

(0)

2 Star

(0)

1 Star

(0)

Your Rating:

Your Name: Create a Pen Name or

Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation

Reminder:

  • - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
  • - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

 
Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously

    If you find inappropriate content, please report it to Barnes & Noble
    Why is this product inappropriate?
    Comments (optional)