Microsoft® Access® 2010 VBA Programming Inside Out by Couch, Andrew -- Read -- Imperial Library of Trantor

Index

Microsoft® Access® 2010 VBA Programming Inside Out

A Note Regarding Supplemental Files Introduction

Who This Book Is For Assumptions About You How This Book Is Organized

Features and Conventions Used in This Book

Text Conventions Design Conventions

About the Companion Content

Access Versions

Acknowledgments Support and Feedback

Errata & Support We Want to Hear from You Stay in Touch

How to Access Your Online Edition Hosted by Safari How to Download the Online Edition to Your Computer I. VBA Environment and Language

1. Using the VBA Editor and Debugging Code

Debugging Code on a Form

Entering the VBA Editor The Application and VBA Code Windows

Creating Modules and Procedures

Creating a Module Creating a Procedure Executing a Subroutine Executing a Function Viewing and Searching Code Split Window Searching Code

Debugging Code in a Module

Debug Commands

Breakpointing Code

Set Next Command Breakpoint Step and Run Commands Displaying Variables in the Locals Window Tracing Procedures with the Call Stack Watching Variables and Expressions Adding Conditional Watch Expressions Working with the Immediate Window Changing Code On-the-Fly

Using the Object Browser and Help System

Configuring the Help System Working with the Object Browser

Summary

Mixed Versions of Access Expression Builder Object Browser Debugging Modal Forms

2. Understanding the VBA Language Structure

VBA Language Settings

Comments Setting Option Explicit Selecting Option Compare Compiling Code Conditional Compilation References

Working with Constants and Variables

Improving Code Quality with Constants The Enum Keyword Variables and Database Field Types Handling NULL Values, IsNull and Nz Using Static Variables Using Global Variables Variable Scope and Lifetime Working with Arrays

Dynamic Arrays Multi-Dimensional Arrays Option Base

Type Structures

Functions and Procedures

Managing Code with Subroutines Defining ByRef and ByValue Parameters Private and Public Procedures Optional and Named Parameters The ParamArray Qualifier Organizing Code in Modules and Class Modules

Control Statements and Program Flow

IF... Then... Else... Statements IIF Statements Choose Statements Select Case Statements TypeOf Statements For and For Each Loops Do While and Do Until Loops Exit Statements The With Statement GoTo and GoSub Line Continuation Splitting SQL Over Multiple Lines

Summary

3. Understanding the VBA Language Features

Using Built-In Functions

Date and Time Functions String Functions

The Format Function The ASC Function The Mid Function

Domain Functions

Constructing Where Clauses

SQL and Embedded Quotes

Using VBA Functions in Queries The Eval Function Shell and Sendkeys The DoEvents Command

Objects and Collections

Object Variables Is Nothing, IsEmpty, IsObject

Creating Maintainable Code

Naming Access Document Objects Naming Database Fields Naming Unbound Controls Naming Variables in Code Indenting Code Other Variable Naming Conventions

VBA and Macros

Access Basic Converting Macros to VBA

Error Handling

On Error Resume Next Err Object On Error GoTo Developing a General Purpose Error Handler OpenArgs and Dialog Forms Err.Raise

Summary

II. Access Object Model and Data Access Objects (DAO)

4. Applying the Access Object Model

The Application Object Methods and Properties

The Run Method The RunCommand Method Simplifying Filtering by Using BuildCriteria The ColumnHistory and Append Only Memo Fields Examining TempVars Invoking the Expression Builder

The CurrentProject and CurrentData Objects

Retrieving Version Information Changing Form Datasheet View Properties Object Dependencies

The DoCmd Object

Controlling the Environment Controlling Size and Position Application Navigation Data Exchange

Manipulating the Forms and Reports Collections

Using the Expression Builder Referencing Controls on a Subform Creating Access Objects in Code

Using the Screen Object

Changing the Mouse Pointer Shape Working with the ActiveForm and ActiveControl

Enhancing the User Interface

Setting and Getting Options Locking Down Access Monitoring Progress with SysCmd Custom Progress Bars Selecting Files with the Office FileDialog

Summary

5. Understanding the Data Access Object Model

The DAO Model

DAO, ADO, and References

Working with Databases

The DBEngine Object The Workspace Object Transactions The Errors Collection The Database Object CurrentDB, DBEngine, and CodeDB

CodeDB

The TableDefs Collection and Indexes

The Data Definition Language

Managing Datasheet Properties Relationships

Manipulating Data with Recordsets

Searching Bookmarks Field Syntax Filter and Sort Properties Adding, Editing, and Updating Records Multiple-Values Lookup Fields Attachment Fields

Displaying Information Delete SaveToFile LoadFromFile Copying Attachments

The OLE Object Data Type

Using Binary Transfer Inserted Documents

Calculated Fields Cloning and Copying Recordsets Reading Records into an Array

Working with Queries in Code

Temporary QueryDefs QueryDefs and Recordsets Creating QueryDefs QueryDef Parameters

Investigating and Documenting Objects

Containers and Documents Object Properties

Sample Applications

Documenting a Database by Using the DAO Finding Objects in a Database by Using the DAO

Summary

III. Working with Forms and Reports

6. Using Forms and Events

Displaying Records

Bound and Unbound Forms Modal and Pop-Up Forms Open and Load Events

Filtering by Using Controls

Filtering by Using the Filter Property Filtering by Using Another Form The ApplyFilter Event Unload and Close Events Working with the RecordsetClone Refresh, Repaint, Recalc, and Requery Commands Calling Procedures Across Forms

Interacting with Records on a Form

The Current Event Deactivate and Activate Events Setting the Timer Interval Property of the Timer Event

Periodic Execution Monitoring

The Mouse Events

Editing and Undo on a Record

BeforeUpdate and AfterUpdate Events Locking and Unlocking Controls BeforeInsert and AfterInsert Events The Delete Event KeyPreview and Key Events The Error Event Saving Records

Summary

7. Using Form Controls and Events

Control Events

The Click and DblClick Events The BeforeUpdate Event The AfterUpdate Event The GotFocus and LostFocus Events

Combo Boxes

Synchronizing Data in Controls Combo Box RowSource Type Combo Box Columns Value List Editing Table/Query Editing

List Boxes

Multiple Selections Multiple Selections with Two List Boxes Using the List Box as a Subform

The TreeView Control

Adding the TreeView Control Populating the Tree Adding Graphics Expanding and Collapsing Nodes Drag-and-Drop Deleting a Node with Recursion Adding Nodes

The Tab Control

Refreshing Between Tabs and Controls The OnChange Event Dynamically Loading Tabs

Loading Pages Dynamic Loading of a Related Page Unloading a Page

Summary

8. Creating Reports and Events

Report Event Sequences

Creating Drill-Down Reports and Current Event Creating a Boxed Grid with the Print Event Layout Control and the Format Event

Report Layout Control

Driving Reports from a Form Reducing Joins with a Combo Box Programming a Report Grouping Packing Address Information with a ParamArray Control of Printers

Summary

IV. Advanced Programming with VBA Classes

9. Adding Functionality with Classes

Improving the Dynamic Tab Control

Creating a Class Module The Let and Get Object Properties Creating an Object with New and Set Collection of Objects Creating Collection Classes

Exporting and Re-importing the Class

Using Classes with the Dynamic Tab Simplifying the Application with Classes

Creating a Hierarchy of Classes

Creating a Base Class Derived Classes

Summary

10. Using Classes and Events

WithEvents Processing

Handling Form Events Handling Control Events Asynchronous Event Processing and RaiseEvent

Stored Procedures The ADO Asynchronous Execution Class Batch Processing Form

Abstract and Implementation Classes

Abstract Classes Implementation Classes Implementing an Abstract Class Hybrid Abstract and Non-Abstract Classes

Friend Methods Summary

11. Using Classes and Forms

Opening Multiple Instances of a Form Classes and Binding Forms

Binding a Form to a Data Access Object Recordset Binding a Form to an Active Data Object Recordset

ActiveX Controls and Events

Adding a Slider Control The UpDown or Spin Control

Summary

V. External Data and Office Integration

12. Linking Access Tables

Linking Access to Access

Using the Database Splitter Linked Table Manager Automating Relinking

Linking to Excel and Text Files

Linking to Excel Linking to Text Files

Linking to SQL Server

Setting up the Sample Database Creating a DSN Connecting to SQL Server Tables Refreshing SQL Server Linked Tables Connecting to a View in SQL Server Refreshing SQL Server Views

Linking to SQL Azure

SQL Azure DSN Connecting to SQL Azure

Linking to SharePoint Lists

Relinking SharePoint Lists

Linking Access Web Databases

Relinking to an Access Web Database

Summary

13. Integrating Microsoft Office

Working with Objects and Object Models

Early vs. Late Binding and CreateObject vs. New The GetObject Keyword Opening Existing Files

Connecting Access to Word

Generating Documents from a Placeholder Document Opening a Placeholder Document Merging Data with Bookmarks

Connecting Access to Excel

Writing Data to a Spreadsheet

Opening Excel Writing the Data

Reading Data from a Spreadsheet Reporting with Excel Linked to Access Using MS Query and Data Sources

Connecting Access to Outlook

Extracting Information from Outlook Creating Objects in Outlook Writing to Access from Outlook

Summary

VI. SQL Server and SQL Azure

14. Using SQL Server

Introducing SQL Server

Programs vs. Services Client-Server Performance SQL Server Versions SQL Express and SQL Server Products Database File Locations Log Files and Recovery Models Instances Windows Services System Databases System Tables

Getting Started with the SQL Server Management Studio

Running the Demo Database Script Creating a New Database

Creating Tables and Relationships

Database Diagrams Tables, Relationships, and Script Files Changing the Design of a Table

Changing a Table Name Changing a Column Name Changing a Column Data Type Adding a Column Deleting a Column Adding a Column with a Default

Using the Identity Property

Working with Views

Graphical Interface Views and Script Files CROSSTAB Queries

Working with Stored Procedures

The DELETE Query The UPDATE Query The INSERT and INSERT INTO Queries

Introducing T-SQL

Defining Variables Using CAST and CONVERT Built-In Functions System Variables

@@Version @@IDENTITY, SCOPE_IDENTITY() and IDENT_CURRENT

Controlling Program Flow Error Handling

Working with Triggers Working with Transactions

Transaction Isolation Levels Nesting Transactions

User-Defined Functions Getting Started with SQL Server Security

Surface Area Configuration SQL Server Authentication Windows Authentication

Summary

15. Upsizing Access to SQL Server

Planning for Upsizing

Text Data Types and UNICODE Date and Time Data Boolean Data Integer Numbers Real Numbers, Decimals, and Floating-Point Numbers Hyperlinks IMAGE, VARBINARY(Max), and OLE Data Memo Data Currency Attachments and Multi-Value Data Required Fields Cycles and Multiple Cascade Paths Mismatched Fields in Relationships Replicated Databases and Random Autonumbers Unique Index and Ignore Nulls Timestamps and Row Versioning Schemas and Synonyms

The Upsizing Wizard and the SQL Server Migration Assistant

The Upsizing Wizard Upsizing to Use an Access Data Project

Query Conversion

SSMA

The Migration Wizard Mapping Data Types Using Schemas Comparing Table Conversion in the Upsizing Wizard and SSMA Comparing Query Conversion in the Upsizing Wizard and SSMA

Developing with Access and SQL Server

The dbSeeChanges Constant Pass-Through Queries Stored Procedures and Temporary Tables Handling Complex Queries Performance and Execution Plans SQL Server Profiler

The MSysConf Table

Summary

16. Using SQL Azure

Introducing SQL Azure

Creating Databases Firewall Settings Using Management Studio Developing with the Browser Interface

Migrating SQL Databases

Creating a Set of Tables Transferring Data with the SQL Server Import and Export Wizard Backing up and Copying a Database

The Data Sync Feature

The Data Sync Agent Sync Groups and Sync Logs Changing Data and Database Structure Conflict Resolution in Data Changes to Table Structure

Planning and Managing Security Building Multi-Tenanted Applications

User Tables and Views Application Tables and Views Managing Security

SQL Server Migration Assistant and Access to Azure Summary

VII. Application Design

17. Building Applications

Developing Applications

Application Navigation

Push Buttons on a Form The Switchboard Manager Manager The Navigation Control The TreeView Control The Tab Control The Ribbon Opening Multiple Copies of a Form Navigating with Combo and List Boxes The Maximize, Popup, Modal, and MoveSize Properties

Ribbon Design

The USysRibbons Table The OnLoad Callback The OnAction Callback The GetEnabled Callback Setting a Default Ribbon for the Application Images Dynamically Changing Tab Visibility and Focus The Backstage View Ribbons for Forms and Reports

32-Bit and 64-Bit Environments Working with the Windows Registry Using the Windows API

Completing an Application

Splash Screens Progress Bars Error Handling Locking Down an Application

Deploying Applications

Protecting Your Design with ACCDE Files Runtime Deployment Single and Multiple Application Files DSNs and Relinking Applications Depending on References Updating Applications

Summary

18. Using ADO and ADOX

ActiveX Data Objects

Cursors Asynchronous Operations Forms and ADO Recordsets

Working with SQL Server

Connection Strings Connecting to SQL Server Command Object Stored Procedures Multiple Active Result Sets and Performance MARS and Connections

ADOX Summary

A. About the Author Index About the Author

← Prev
Back
Next →

← Prev
Back
Next →