You are previewing Microsoft® Access® 2010 VBA Programming Inside Out.
O'Reilly logo
Microsoft® Access® 2010 VBA Programming Inside Out

Book Description

You're beyond the basics, so dive right in and customize, automate, and extend Access—using Visual Basic for Applications (VBA). This supremely organized reference is packed with hundreds of time-saving solutions, troubleshooting tips, and workarounds. It's all muscle and no fluff. Discover how the experts use VBA to exploit the power of Access—and challenge yourself to new levels of mastery!

  • Enhance your application with VBA built-in functions and SQL code

  • Use the Access Object Model to work with data in forms and reports

  • Manipulate data using SQL, queries, and recordsets with Data Access Objects (DAO)

  • Create classes for handling form and control events

  • Connect your Access database to different sources of data

  • Effectively plan how to upsize an existing Access database to Microsoft SQL Server

  • Dynamically update Microsoft Excel spreadsheets from the database

  • Migrate your Access database directly to the cloud using SQL Azure

  • Table of Contents

    1. Microsoft® Access® 2010 VBA Programming Inside Out
    2. Dedication
    3. A Note Regarding Supplemental Files
    4. Introduction
      1. Who This Book Is For
      2. Assumptions About You
      3. How This Book Is Organized
    5. Features and Conventions Used in This Book
      1. Text Conventions
      2. Design Conventions
    6. About the Companion Content
      1. Access Versions
      2. Your Companion eBook
    7. Acknowledgments
    8. Support and Feedback
      1. Errata & Support
      2. We Want to Hear from You
      3. Stay in Touch
    9. I. VBA Environment and Language
      1. 1. Using the VBA Editor and Debugging Code
        1. Debugging Code on a Form
          1. Entering the VBA Editor
          2. The Application and VBA Code Windows
        2. Creating Modules and Procedures
          1. Creating a Module
          2. Creating a Procedure
          3. Executing a Subroutine
          4. Executing a Function
          5. Viewing and Searching Code
          6. Split Window
          7. Searching Code
        3. Debugging Code in a Module
          1. Debug Commands
        4. Breakpointing Code
          1. Set Next Command
          2. Breakpoint Step and Run Commands
          3. Displaying Variables in the Locals Window
          4. Tracing Procedures with the Call Stack
          5. Watching Variables and Expressions
          6. Adding Conditional Watch Expressions
          7. Working with the Immediate Window
          8. Changing Code On-the-Fly
        5. Using the Object Browser and Help System
          1. Configuring the Help System
          2. Working with the Object Browser
        6. Summary
          1. Mixed Versions of Access
          2. Expression Builder
          3. Object Browser
          4. Debugging Modal Forms
      2. 2. Understanding the VBA Language Structure
        1. VBA Language Settings
          1. Comments
          2. Setting Option Explicit
          3. Selecting Option Compare
          4. Compiling Code
          5. Conditional Compilation
          6. References
        2. Working with Constants and Variables
          1. Improving Code Quality with Constants
          2. The Enum Keyword
          3. Variables and Database Field Types
          4. Handling NULL Values, IsNull and Nz
          5. Using Static Variables
          6. Using Global Variables
          7. Variable Scope and Lifetime
          8. Working with Arrays
            1. Dynamic Arrays
            2. Multi-Dimensional Arrays
            3. Option Base
          9. Type Structures
        3. Functions and Procedures
          1. Managing Code with Subroutines
          2. Defining ByRef and ByValue Parameters
          3. Private and Public Procedures
          4. Optional and Named Parameters
          5. The ParamArray Qualifier
          6. Organizing Code in Modules and Class Modules
        4. Control Statements and Program Flow
          1. IF... Then... Else... Statements
          2. IIF Statements
          3. Choose Statements
          4. Select Case Statements
          5. TypeOf Statements
          6. For and For Each Loops
          7. Do While and Do Until Loops
          8. Exit Statements
          9. The With Statement
          10. GoTo and GoSub
          11. Line Continuation
          12. Splitting SQL Over Multiple Lines
        5. Summary
      3. 3. Understanding the VBA Language Features
        1. Using Built-In Functions
          1. Date and Time Functions
          2. String Functions
            1. The Format Function
            2. The ASC Function
            3. The Mid Function
        2. Domain Functions
          1. Constructing Where Clauses
        3. SQL and Embedded Quotes
          1. Using VBA Functions in Queries
          2. The Eval Function
          3. Shell and Sendkeys
          4. The DoEvents Command
        4. Objects and Collections
          1. Object Variables
          2. Is Nothing, IsEmpty, IsObject
        5. Creating Maintainable Code
          1. Naming Access Document Objects
          2. Naming Database Fields
          3. Naming Unbound Controls
          4. Naming Variables in Code
          5. Indenting Code
          6. Other Variable Naming Conventions
        6. VBA and Macros
          1. Access Basic
          2. Converting Macros to VBA
        7. Error Handling
          1. On Error Resume Next
          2. Err Object
          3. On Error GoTo
          4. Developing a General Purpose Error Handler
          5. OpenArgs and Dialog Forms
          6. Err.Raise
        8. Summary
    10. II. Access Object Model and Data Access Objects (DAO)
      1. 4. Applying the Access Object Model
        1. The Application Object Methods and Properties
          1. The Run Method
          2. The RunCommand Method
          3. Simplifying Filtering by Using BuildCriteria
          4. The ColumnHistory and Append Only Memo Fields
          5. Examining TempVars
          6. Invoking the Expression Builder
        2. The CurrentProject and CurrentData Objects
          1. Retrieving Version Information
          2. Changing Form Datasheet View Properties
          3. Object Dependencies
        3. The DoCmd Object
          1. Controlling the Environment
          2. Controlling Size and Position
          3. Application Navigation
          4. Data Exchange
        4. Manipulating the Forms and Reports Collections
          1. Using the Expression Builder
          2. Referencing Controls on a Subform
          3. Creating Access Objects in Code
        5. Using the Screen Object
          1. Changing the Mouse Pointer Shape
          2. Working with the ActiveForm and ActiveControl
        6. Enhancing the User Interface
          1. Setting and Getting Options
          2. Locking Down Access
          3. Monitoring Progress with SysCmd
          4. Custom Progress Bars
          5. Selecting Files with the Office FileDialog
        7. Summary
      2. 5. Understanding the Data Access Object Model
        1. The DAO Model
          1. DAO, ADO, and References
        2. Working with Databases
          1. The DBEngine Object
          2. The Workspace Object
          3. Transactions
          4. The Errors Collection
          5. The Database Object
          6. CurrentDB, DBEngine, and CodeDB
            1. CodeDB
          7. The TableDefs Collection and Indexes
            1. The Data Definition Language
          8. Managing Datasheet Properties
          9. Relationships
        3. Manipulating Data with Recordsets
          1. Searching
          2. Bookmarks
          3. Field Syntax
          4. Filter and Sort Properties
          5. Adding, Editing, and Updating Records
          6. Multiple-Values Lookup Fields
          7. Attachment Fields
            1. Displaying Information
            2. Delete
            3. SaveToFile
            4. LoadFromFile
            5. Copying Attachments
          8. The OLE Object Data Type
            1. Using Binary Transfer
            2. Inserted Documents
          9. Calculated Fields
          10. Cloning and Copying Recordsets
          11. Reading Records into an Array
        4. Working with Queries in Code
          1. Temporary QueryDefs
          2. QueryDefs and Recordsets
          3. Creating QueryDefs
          4. QueryDef Parameters
        5. Investigating and Documenting Objects
          1. Containers and Documents
          2. Object Properties
        6. Sample Applications
          1. Documenting a Database by Using the DAO
          2. Finding Objects in a Database by Using the DAO
        7. Summary
    11. III. Working with Forms and Reports
      1. 6. Using Forms and Events
        1. Displaying Records
          1. Bound and Unbound Forms
          2. Modal and Pop-Up Forms
          3. Open and Load Events
        2. Filtering by Using Controls
          1. Filtering by Using the Filter Property
          2. Filtering by Using Another Form
          3. The ApplyFilter Event
          4. Unload and Close Events
          5. Working with the RecordsetClone
          6. Refresh, Repaint, Recalc, and Requery Commands
          7. Calling Procedures Across Forms
        3. Interacting with Records on a Form
          1. The Current Event
          2. Deactivate and Activate Events
          3. Setting the Timer Interval Property of the Timer Event
            1. Periodic Execution
            2. Monitoring
          4. The Mouse Events
        4. Editing and Undo on a Record
          1. BeforeUpdate and AfterUpdate Events
          2. Locking and Unlocking Controls
          3. BeforeInsert and AfterInsert Events
          4. The Delete Event
          5. KeyPreview and Key Events
          6. The Error Event
          7. Saving Records
        5. Summary
      2. 7. Using Form Controls and Events
        1. Control Events
          1. The Click and DblClick Events
          2. The BeforeUpdate Event
          3. The AfterUpdate Event
          4. The GotFocus and LostFocus Events
        2. Combo Boxes
          1. Synchronizing Data in Controls
          2. Combo Box RowSource Type
          3. Combo Box Columns
          4. Value List Editing
          5. Table/Query Editing
        3. List Boxes
          1. Multiple Selections
          2. Multiple Selections with Two List Boxes
          3. Using the List Box as a Subform
        4. The TreeView Control
          1. Adding the TreeView Control
          2. Populating the Tree
          3. Adding Graphics
          4. Expanding and Collapsing Nodes
          5. Drag-and-Drop
          6. Deleting a Node with Recursion
          7. Adding Nodes
        5. The Tab Control
          1. Refreshing Between Tabs and Controls
          2. The OnChange Event
          3. Dynamically Loading Tabs
            1. Loading Pages
            2. Dynamic Loading of a Related Page
            3. Unloading a Page
        6. Summary
      3. 8. Creating Reports and Events
        1. Report Event Sequences
          1. Creating Drill-Down Reports and Current Event
          2. Creating a Boxed Grid with the Print Event
          3. Layout Control and the Format Event
        2. Report Layout Control
          1. Driving Reports from a Form
          2. Reducing Joins with a Combo Box
          3. Programming a Report Grouping
          4. Packing Address Information with a ParamArray
          5. Control of Printers
        3. Summary
    12. IV. Advanced Programming with VBA Classes
      1. 9. Adding Functionality with Classes
        1. Improving the Dynamic Tab Control
          1. Creating a Class Module
          2. The Let and Get Object Properties
          3. Creating an Object with New and Set
          4. Collection of Objects
          5. Creating Collection Classes
            1. Exporting and Re-importing the Class
          6. Using Classes with the Dynamic Tab
          7. Simplifying the Application with Classes
        2. Creating a Hierarchy of Classes
          1. Creating a Base Class
          2. Derived Classes
        3. Summary
      2. 10. Using Classes and Events
        1. WithEvents Processing
          1. Handling Form Events
          2. Handling Control Events
          3. Asynchronous Event Processing and RaiseEvent
            1. Stored Procedures
            2. The ADO Asynchronous Execution Class
            3. Batch Processing Form
        2. Abstract and Implementation Classes
          1. Abstract Classes
          2. Implementation Classes
          3. Implementing an Abstract Class
          4. Hybrid Abstract and Non-Abstract Classes
        3. Friend Methods
        4. Summary
      3. 11. Using Classes and Forms
        1. Opening Multiple Instances of a Form
        2. Classes and Binding Forms
          1. Binding a Form to a Data Access Object Recordset
          2. Binding a Form to an Active Data Object Recordset
        3. ActiveX Controls and Events
          1. Adding a Slider Control
          2. The UpDown or Spin Control
        4. Summary
    13. V. External Data and Office Integration
      1. 12. Linking Access Tables
        1. Linking Access to Access
          1. Using the Database Splitter
          2. Linked Table Manager
          3. Automating Relinking
        2. Linking to Excel and Text Files
          1. Linking to Excel
          2. Linking to Text Files
        3. Linking to SQL Server
          1. Setting up the Sample Database
          2. Creating a DSN
          3. Connecting to SQL Server Tables
          4. Refreshing SQL Server Linked Tables
          5. Connecting to a View in SQL Server
          6. Refreshing SQL Server Views
        4. Linking to SQL Azure
          1. SQL Azure DSN
          2. Connecting to SQL Azure
        5. Linking to SharePoint Lists
          1. Relinking SharePoint Lists
        6. Linking Access Web Databases
          1. Relinking to an Access Web Database
        7. Summary
      2. 13. Integrating Microsoft Office
        1. Working with Objects and Object Models
          1. Early vs. Late Binding and CreateObject vs. New
          2. The GetObject Keyword
          3. Opening Existing Files
        2. Connecting Access to Word
          1. Generating Documents from a Placeholder Document
          2. Opening a Placeholder Document
          3. Merging Data with Bookmarks
        3. Connecting Access to Excel
          1. Writing Data to a Spreadsheet
            1. Opening Excel
            2. Writing the Data
          2. Reading Data from a Spreadsheet
          3. Reporting with Excel Linked to Access
          4. Using MS Query and Data Sources
        4. Connecting Access to Outlook
          1. Extracting Information from Outlook
          2. Creating Objects in Outlook
          3. Writing to Access from Outlook
        5. Summary
    14. VI. SQL Server and SQL Azure
      1. 14. Using SQL Server
        1. Introducing SQL Server
          1. Programs vs. Services
          2. Client-Server Performance
          3. SQL Server Versions
          4. SQL Express and SQL Server Products
          5. Database File Locations
          6. Log Files and Recovery Models
          7. Instances
          8. Windows Services
          9. System Databases
          10. System Tables
        2. Getting Started with the SQL Server Management Studio
          1. Running the Demo Database Script
          2. Creating a New Database
        3. Creating Tables and Relationships
          1. Database Diagrams
          2. Tables, Relationships, and Script Files
          3. Changing the Design of a Table
            1. Changing a Table Name
            2. Changing a Column Name
            3. Changing a Column Data Type
            4. Adding a Column
            5. Deleting a Column
            6. Adding a Column with a Default
          4. Using the Identity Property
        4. Working with Views
          1. Graphical Interface
          2. Views and Script Files
          3. CROSSTAB Queries
        5. Working with Stored Procedures
          1. The DELETE Query
          2. The UPDATE Query
          3. The INSERT and INSERT INTO Queries
        6. Introducing T-SQL
          1. Defining Variables
          2. Using CAST and CONVERT
          3. Built-In Functions
          4. System Variables
            1. @@Version
            2. @@IDENTITY, SCOPE_IDENTITY() and IDENT_CURRENT
          5. Controlling Program Flow
          6. Error Handling
        7. Working with Triggers
        8. Working with Transactions
          1. Transaction Isolation Levels
          2. Nesting Transactions
        9. User-Defined Functions
        10. Getting Started with SQL Server Security
          1. Surface Area Configuration
          2. SQL Server Authentication
          3. Windows Authentication
        11. Summary
      2. 15. Upsizing Access to SQL Server
        1. Planning for Upsizing
          1. Text Data Types and UNICODE
          2. Date and Time Data
          3. Boolean Data
          4. Integer Numbers
          5. Real Numbers, Decimals, and Floating-Point Numbers
          6. Hyperlinks
          7. IMAGE, VARBINARY(Max), and OLE Data
          8. Memo Data
          9. Currency
          10. Attachments and Multi-Value Data
          11. Required Fields
          12. Cycles and Multiple Cascade Paths
          13. Mismatched Fields in Relationships
          14. Replicated Databases and Random Autonumbers
          15. Unique Index and Ignore Nulls
          16. Timestamps and Row Versioning
          17. Schemas and Synonyms
        2. The Upsizing Wizard and the SQL Server Migration Assistant
          1. The Upsizing Wizard
          2. Upsizing to Use an Access Data Project
            1. Query Conversion
          3. SSMA
            1. The Migration Wizard
            2. Mapping Data Types
            3. Using Schemas
            4. Comparing Table Conversion in the Upsizing Wizard and SSMA
            5. Comparing Query Conversion in the Upsizing Wizard and SSMA
        3. Developing with Access and SQL Server
          1. The dbSeeChanges Constant
          2. Pass-Through Queries
          3. Stored Procedures and Temporary Tables
          4. Handling Complex Queries
          5. Performance and Execution Plans
          6. SQL Server Profiler
            1. The MSysConf Table
        4. Summary
      3. 16. Using SQL Azure
        1. Introducing SQL Azure
          1. Creating Databases
          2. Firewall Settings
          3. Using Management Studio
          4. Developing with the Browser Interface
        2. Migrating SQL Databases
          1. Creating a Set of Tables
          2. Transferring Data with the SQL Server Import and Export Wizard
          3. Backing up and Copying a Database
        3. The Data Sync Feature
          1. The Data Sync Agent
          2. Sync Groups and Sync Logs
          3. Changing Data and Database Structure
          4. Conflict Resolution in Data
          5. Changes to Table Structure
        4. Planning and Managing Security
        5. Building Multi-Tenanted Applications
          1. User Tables and Views
          2. Application Tables and Views
          3. Managing Security
        6. SQL Server Migration Assistant and Access to Azure
        7. Summary
    15. VII. Application Design
      1. 17. Building Applications
        1. Developing Applications
          1. Application Navigation
            1. Push Buttons on a Form
            2. The Switchboard Manager Manager
            3. The Navigation Control
            4. The TreeView Control
            5. The Tab Control
            6. The Ribbon
            7. Opening Multiple Copies of a Form
            8. Navigating with Combo and List Boxes
            9. The Maximize, Popup, Modal, and MoveSize Properties
          2. Ribbon Design
            1. The USysRibbons Table
            2. The OnLoad Callback
            3. The OnAction Callback
            4. The GetEnabled Callback
            5. Setting a Default Ribbon for the Application
            6. Images
            7. Dynamically Changing Tab Visibility and Focus
            8. The Backstage View
            9. Ribbons for Forms and Reports
          3. 32-Bit and 64-Bit Environments
          4. Working with the Windows Registry
          5. Using the Windows API
        2. Completing an Application
          1. Splash Screens
          2. Progress Bars
          3. Error Handling
          4. Locking Down an Application
        3. Deploying Applications
          1. Protecting Your Design with ACCDE Files
          2. Runtime Deployment
          3. Single and Multiple Application Files
          4. DSNs and Relinking Applications
          5. Depending on References
          6. Updating Applications
        4. Summary
      2. 18. Using ADO and ADOX
        1. ActiveX Data Objects
          1. Cursors
          2. Asynchronous Operations
          3. Forms and ADO Recordsets
        2. Working with SQL Server
          1. Connection Strings
          2. Connecting to SQL Server
          3. Command Object
          4. Stored Procedures
          5. Multiple Active Result Sets and Performance
          6. MARS and Connections
        3. ADOX
        4. Summary
    16. A. About the Author
    17. Index
    18. About the Author
    19. Copyright