- Shopping Bag ( 0 items )
Want a NOOK? Explore Now
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
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
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.
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.
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.
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.
Anonymous
Posted Thu Sep 18 00:00:00 EDT 2014
No text was provided for this review.
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.
...