Access™ 2007 VBA Programmer's Reference

Access™ 2007 VBA Programmer's Reference

by Teresa Hennig, Rob Cooper, Geoffrey Griffith, Armen Stein
Released May 2007
Publisher(s): Wrox
ISBN: 9780470047033

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

This resource provides the tools you need to leverage Access 2007's built-in functionality and VBA in order to build more dynamic applications. The author team explores all aspects of VBA programming for Access, describes the latest innovations, and explains how to take advantage of key new objects, enhanced macros, and the Office Ribbon. Plus, you'll find several commonly used techniques and sample code to help you get started, as well as expert tips to make your code easier to maintain.

Table of contents

  1. Copyright
  2. Dedication
  3. About the Authors
  4. Credits
  5. Acknowledgments
  6. Foreword
  7. Introduction
  8. Introduction to Microsoft Access 2007
    1. A Brief History of Access
    2. Is Access the Only Database System?
    3. Developing Databases Without VBA Code
    4. Summary
  9. Access, VBA, and Macros
    1. VBA in Access
    2. VBA versus Macros in Access
    3. Summary
  10. New Features in Access 2007
    1. Who Benefits
    2. New Look
    3. Development Environment
    4. Forms
    5. Reports
    6. Embedded Macros
    7. Access Data Engine
    8. Integration with SharePoint
    9. External Data Sources
    10. Security
    11. Convert with Confidence
    12. ADE and Creating Runtime Files
    13. What's Gone or Deprecated
    14. Summary
  11. Using the VBA Editor
    1. Anatomy of the VBA Editor
    2. Using the Object Browser
    3. Testing and Debugging VBA Code
    4. Summary
  12. VBA Basics
    1. VBA Objects
    2. Variables and VBA Syntax
    3. Other VBA Structures
    4. Summary
  13. Using DAO to Access Data
    1. Data Access Objects
    2. Why Use DAO?
    3. New Features in DAO
    4. Referring to DAO Objects
    5. The DBEngine Object
    6. The Databases Collection
    7. DAO Object Properties
    8. Creating Schema Objects with DAO
    9. Database Encryption with DAO
    10. Managing Access (JET) Security with DAO
    11. Data Access with DAO
    12. Append Only Fields
    13. Summary
  14. Using ADO to Access Data
    1. Ambiguous References
    2. Referring to ADO Objects
    3. Connecting to a Data Source
    4. Rolling Your Own Connection String
    5. Data Access with ADO
    6. Creating ADO Recordsets
    7. Creating Schema Recordsets
    8. Using ADO Events
    9. Testing the State Property
    10. Creating Schema Objects with ADOX
    11. Managing Security with ADO
    12. Summary
  15. Executing VBA
    1. When Events Fire
    2. VBA Procedures
    3. Evaluating Expressions in VBA
    4. Using Recordsets
    5. Using Multiple Recordsets
    6. Using VBA in Forms and Reports
    7. Debugging VBA
    8. Investigating Variables
    9. Common VBA Techniques
    10. String Concatenation Techniques
    11. Summary
  16. VBA Error Handling
    1. Why Use Error Handling?
    2. Two Kinds of Errors: Unexpected and Expected
    3. Basic Error Handling
    4. Cleaning Up After an Error
    5. More on Absorbing Expected Errors
    6. Issues in Error Handling
    7. Summary
  17. Using VBA to Enhance Forms
    1. VBA Basics
    2. Creating Forms the 2007 Way
    3. Summary
  18. Enhancing Reports with VBA
    1. Introduction to Reports
    2. Creating a Report
    3. Working with VBA in Reports
    4. Important Report Events and Properties
    5. Report Properties
    6. Working with Charts
    7. Common Report Requests
    8. Layout View
    9. Report View
    10. Summary
  19. Customizing the Ribbon
    1. Ribbon Overview
    2. Custom Menu Bars and Toolbars
    3. Ribbon Customization
    4. Saving a Custom Ribbon
    5. Specifying the Custom Ribbon
    6. Creating an Integrated Ribbon
    7. Creating a Ribbon from Scratch
    8. Customizing the Office Menu
    9. Customizing the Quick Access Toolbar
    10. More Ribbon Tips
    11. Summary
  20. Creating Classes in VBA
    1. A Touch of Class
    2. Why Use Classes?
    3. Creating a Class Module
    4. Naming Objects
    5. Using Class Events
    6. Forms as Objects
    7. Variable Scope and Lifetime
    8. The Me Property
    9. Creating a Clone Method
    10. Creating and Using Collection Classes
    11. The Three Pillars
    12. Summary
  21. Extending VBA with APIs
    1. Introducing the Win32 API
    2. Introducing Linking
    3. Linking Libraries in Access 2007
    4. Understanding C Parameters
    5. Err.LastDLLError
    6. Distributing Applications That Reference Type Libraries and Custom DLLs
    7. Summary
  22. SQL and VBA
    1. Working with SQL Strings in VBA
    2. Using SQL When Opening Forms and Reports
    3. Using SQL to Enhance Forms
    4. The ReplaceOrderByClause and ReplaceWhereClause Functions
    5. Summary
  23. Working with Office Applications
    1. Sharing Information Is a Two-Way Street
    2. Working with Outlook
    3. Sending Information from Access to Excel
    4. Exchanging Data with Microsoft Word
    5. Sending Data to PowerPoint
    6. Pulling Data from Access
    7. Summary
  24. Working with SharePoint
    1. Overview
    2. Access Features on SharePoint
    3. SharePoint Features in Access
    4. Summary
  25. Database Security
    1. Security for the ACCDB File Format
    2. Security for the MDB File Format
    3. Summary
  26. Understanding Client-Server Development with VBA
    1. Client-Server Applications
    2. Choosing the Correct File Format
    3. Controlling the Logon Process
    4. Binding ADODB Recordsets
    5. Using Unbound Forms
    6. Summary
  27. Working with the Win32 Registry
    1. About the Registry
    2. Using the Built-In VBA Registry Functions
    3. Using the Win32 Registry APIs
    4. Summary
  28. Using the ADE Tools
    1. The Redistributable Access Runtime
    2. The Package Solution Wizard
    3. Save as Template
    4. Source Code Control Support
    5. Summary
  29. Protecting Yourself with Access 2007 Security
    1. The Office Trust Center
    2. Disabled Mode
    3. Digital Signatures and Certificates
    4. Access Database Engine Expression Service
    5. Summary
  30. Upgrading to Access 2007
    1. To Convert or To Enable
    2. Installing Multiple Versions of Access on One PC
    3. Changing File Formats
    4. Converting a Secured Database
    5. Converting a Replicated Database
    6. Enabling a Database
  31. References for Projects
    1. Types of References
    2. Adding References to Your Projects
    3. Building Code Libraries
    4. Resources
  32. Calling Managed Code
    1. A Quick Overview of Managed Code
    2. Writing Managed Code
    3. Using Managed Code in Access
  33. DAO Object Method and Property Descriptions
    1. DAO-Supported Objects
    2. Undocumented Tools and Resources
    3. DAO Field Types
    4. OpenRecordset Constants
  34. ADO Object Model Reference
    1. Connection Object
    2. Errors Collection and Error Object
    3. Properties Collection and Property Object
    4. Command Object
    5. Parameters Collection
    6. Recordset Object
    7. Fields Collection
    8. Field Object
    9. Record Object
    10. Stream Object
  35. ADO Object Argument Enumeration Information
    1. Connection.Execute Method Options
    2. Recordset.Open Method Options
    3. Connection.OpenSchema Method Options
    4. Group or User SetPermissions Method Options
  36. The Access Object Model
    1. The Application Object
    2. AllObjects Collection
    3. The CurrentData Object
    4. The CurrentProject Object
    5. AccessObject
    6. The DoCmd Object
    7. The Form Object
    8. The Control Object
    9. The ImportExportSpecification Object
    10. The Module Object
    11. The Printer Object
    12. The References Collection and Reference Object
    13. The Reports Collection and Report Object
    14. The Screen Object
    15. The Section Object
    16. The SmartTag Object
    17. The SubForm Object
    18. The SubReport Object
    19. Other Helpful Information
  37. Windows API Reference Information
    1. API Viewer
    2. Websites
    3. Books
    4. Some Useful API Functions
  38. Windows Registry Information
    1. Windows Registry Data Types
    2. Registry Root Key Hives
    3. Registry Function Declarations
    4. Registry API Constant and User-Defined Type Declarations
  39. Access Wizards, Builders, and Managers
    1. Access Wizards
    2. Access Builders
    3. Access Managers
  40. Reserved Words and Special Characters
    1. What Are the Sources of Reserved Words?
    2. Reserved Words with Error Messages
    3. Reserved Word List
    4. What Are Special Characters?
  41. Naming Conventions
    1. Why Implement Naming Conventions?
    2. A Brief History
    3. The Fundamentals of the Hungarian Convention
    4. Rules for Creating Names — Adding the Personal Touch
    5. More Do's and Don'ts
    6. Tables of Tags
    7. Standards
  42. Tips and Tricks
    1. Visual Interface Standards
    2. Daisy Chain Your Forms
    3. Find Records
    4. Split Your Application
    5. Display Informative Form Captions
    6. Preload Records
    7. Use a Splash Screen
    8. Pop-Up Memo Workspace Form with Spell Check
    9. Determine the User Name
  43. Index

Product information

  • Title: Access™ 2007 VBA Programmer's Reference
  • Author(s): Teresa Hennig, Rob Cooper, Geoffrey Griffith, Armen Stein
  • Release date: May 2007
  • Publisher(s): Wrox
  • ISBN: 9780470047033