You are previewing Access™ 2007 VBA Bible: For Data-Centric Microsoft® Office Applications.
O'Reilly logo
Access™ 2007 VBA Bible: For Data-Centric Microsoft® Office Applications

Book Description

Learn how to tap the full potential of Access 2007 Transfer Access data seamlessly between Microsoft Office applications—and that's just for starters. In this all-new, comprehensive guide by well-known Access expert Helen Feddema, you’ll learn to write Visual Basic code that automates Access database tasks, creates standalone scripts, extracts and merges data, and allows you to put together powerful solutions. Whether you’re a beginner or a power user, this is the book you need to succeed with Access 2007.

Table of Contents

  1. Copyright
  2. About the Author
  3. Credits
  4. Acknowledgments
  5. Introduction
    1. Who Should Read This Book
    2. How This Book Is Organized
      1. Conventions and Features
    3. What's on the Companion Web Site
    4. Minimum Requirements
    5. Where to Go from Here
  6. I. The Office Components and What They Do Best
    1. 1. Storing and Displaying Data in Access
      1. 1.1. A Brief History of Office Data Exchange
      2. 1.2. Storing Data in Access
      3. 1.3. Displaying Data in Access Forms and Reports
        1. 1.3.1. Creating Access Form Letters
        2. 1.3.2. Creating Worksheet-type Reports in Access
          1. 1.3.2.1. Plain Datasheet Reports
          2. 1.3.2.2. PivotTables
          3. 1.3.2.3. PivotCharts
      4. 1.4. Summary
    2. 2. Creating Word Documents from Access
      1. 2.1. Filling Word Documents with Access Data Using the TypeText Method
      2. 2.2. Using Word Templates for Creating Formatted Word Documents
        1. 2.2.1. Bookmarks
        2. 2.2.2. Document Properties
      3. 2.3. Form Field Documents
      4. 2.4. Summary
    3. 3. Analyzing Data with Excel
      1. 3.1. Exporting Access Data to an Unformatted Worksheet
      2. 3.2. Using Excel Templates to Create Formatted Worksheets Filled with Access Data
      3. 3.3. Formatting Excel Worksheets in VBA Code
      4. 3.4. Summary
    4. 4. Organizing and Communicating with Outlook
      1. 4.1. Exporting Appointments and Tasks to Outlook
      2. 4.2. Exporting Journal Information to Outlook
      3. 4.3. Creating Emails from an Access Table
      4. 4.4. Summary
  7. II. Writing VBA Code to Exchange Data between Office Components
    1. 5. Working with Access Data
      1. 5.1. Working with Older Format Databases in Access 2007
        1. 5.1.1. Disambiguating References to Object Model Components
        2. 5.1.2. The DAO Object Model (Old and New)
          1. 5.1.2.1. New Objects in the Access 2007 DAO Object Model
            1. 5.1.2.1.1. ComplexType
            2. 5.1.2.1.2. Field2
            3. 5.1.2.1.3. Recordset2
          2. 5.1.2.2. Hidden Objects in the Access 2007 DAO Object Model
          3. 5.1.2.3. Databases
          4. 5.1.2.4. Recordsets
            1. 5.1.2.4.1. Table
            2. 5.1.2.4.2. Dynaset
            3. 5.1.2.4.3. Snapshot
            4. 5.1.2.4.4. Forward-only
            5. 5.1.2.4.5. Dynamic
          5. 5.1.2.5. QueryDefs
          6. 5.1.2.6. TableDefs and Fields
        3. 5.1.3. The ADO Object Model
          1. 5.1.3.1. Connection
          2. 5.1.3.2. Command
          3. 5.1.3.3. Recordset
            1. 5.1.3.3.1. Dynamic
            2. 5.1.3.3.2. Keyset
            3. 5.1.3.3.3. Static
            4. 5.1.3.3.4. Forward-only
          4. 5.1.3.4. Record
          5. 5.1.3.5. Stream
      2. 5.2. Converting DAO Code to ADO Code
      3. 5.3. Summary
    2. 6. Working with Word Documents and Templates
      1. 6.1. Built-in Word Export in Office 2007
      2. 6.2. Exporting Access Data to Word Using Automation Code
        1. 6.2.1. The Word Object Model
          1. 6.2.1.1. Creating a New, Blank Word Document
          2. 6.2.1.2. Creating a Word Document Based on a Template
          3. 6.2.1.3. Using a Query to Concatenate Data for Export
        2. 6.2.2. Choosing a Method for Merging Access Data to Word
          1. 6.2.2.1. Working with Word Document Properties
            1. 6.2.2.1.1. Sending a Word Letter to a Single Access Contact
            2. 6.2.2.1.2. Sending a Word Letter to Multiple Access Contacts
          2. 6.2.2.2. Word Bookmarks
          3. 6.2.2.3. The TypeText Method
          4. 6.2.2.4. Word Mail Merge
            1. 6.2.2.4.1. Mailing Labels
            2. 6.2.2.4.2. Lists
            3. 6.2.2.4.3. Documents
      3. 6.3. Summary
    3. 7. Working with Excel Worksheets
      1. 7.1. Simply Exporting Access Data to Excel
      2. 7.2. The Excel Object Model
      3. 7.3. Minimally Formatted Worksheets
      4. 7.4. Tabular Worksheets Formatted from Code
      5. 7.5. Timesheets
      6. 7.6. Summary
    4. 8. Working with Outlook Items
      1. 8.1. Exporting Access Data to Outlook Items
        1. 8.1.1. Using the Collect Data Group
        2. 8.1.2. Using the Import Group to Import or Link to Outlook Data
        3. 8.1.3. Using Sample Databases and Forms
      2. 8.2. The Outlook Object Model
        1. 8.2.1. Explorer, Inspector, and other Outlook Objects
        2. 8.2.2. Syntax for Referencing Outlook Objects
        3. 8.2.3. Referencing Outlook Items in VBA Code
      3. 8.3. Working with Outlook Appointments
      4. 8.4. Working with Outlook Tasks
      5. 8.5. Working with Outlook Contacts
      6. 8.6. Summary
    5. 9. Working with Files and Folders
      1. 9.1. Working with Windows Explorer Folders
        1. 9.1.1. The Office FileDialog Object
        2. 9.1.2. The FileSystemObject
      2. 9.2. Backing up Your Database
      3. 9.3. Working with Text Files
        1. 9.3.1. Writing Data to Text Files
          1. 9.3.1.1. ADO
          2. 9.3.1.2. FileSystemObject
          3. 9.3.1.3. VB
        2. 9.3.2. Reading Data from Text Files
          1. 9.3.2.1. ADO
          2. 9.3.2.2. FSO
          3. 9.3.2.3. VB
      4. 9.4. Working with Attachment Fields
        1. 9.4.1. Loading Files into Attachment Fields
        2. 9.4.2. Saving Attachments to Files
      5. 9.5. Summary
    6. 10. Working with External Data
      1. 10.1. Working with Text Files Using the TransferText Method
        1. 10.1.1. Creating an Import or Export Specification
        2. 10.1.2. Importing and Exporting Text File Data in VBA Code
          1. 10.1.2.1. Importing Text Data
          2. 10.1.2.2. Exporting Text Data
      2. 10.2. Working with Legacy Database and Spreadsheet Files
        1. 10.2.1. Importing Database Files
        2. 10.2.2. Importing Spreadsheet Files
        3. 10.2.3. Exporting Database and Spreadsheet Files
      3. 10.3. Working with XML and HTML Files
        1. 10.3.1. Importing HTML and XML Files
        2. 10.3.2. Exporting HTML and XML Files
      4. 10.4. Emailing Exported Text Files
      5. 10.5. Summary
    7. 11. Synchronizing Access and Outlook Contacts
      1. 11.1. Creating a Denormalized Table from a Set of Linked Tables
      2. 11.2. Comparing Outlook and Access Contacts
        1. 11.2.1. Re-creating the Flat-file Tables of Access and Outlook Data
        2. 11.2.2. Copying Contact Data from Access to Outlook (or Vice Versa)
      3. 11.3. Working with Attachments
      4. 11.4. Summary
    8. 12. Going Beyond the Basics
      1. 12.1. Creating Fancy Word Shipping Labels
      2. 12.2. Creating Excel PivotCharts from Access Queries
      3. 12.3. Emailing Shipping and Reordering Reports
      4. 12.4. Summary
  8. III. Adding More Functionality to Office
    1. 13. Creating COM Add-ins with Visual Basic 6
      1. 13.1. Creating a COM Add-in Using Visual Basic 6.0
        1. 13.1.1. Using the COM Add-in Template
        2. 13.1.2. Creating the LNC Control Renaming COM Add-in
          1. 13.1.2.1. The SharedCode Module
          2. 13.1.2.2. The AccessDesigner Module
          3. 13.1.2.3. Creating the DLL
        3. 13.1.3. Installing a COM Add-in
        4. 13.1.4. Troubleshooting a COM Add-in
      2. 13.2. Using a COM Add-in
      3. 13.3. Comparing COM Add-ins with Access Add-ins
      4. 13.4. Summary
    2. 14. Creating Access Add-ins
      1. 14.1. The Purpose of Access Add-ins
      2. 14.2. Add-in Types
      3. 14.3. Creating a Library Database
        1. 14.3.1. Menu Add-ins
        2. 14.3.2. Wizards
        3. 14.3.3. Property Builders
      4. 14.4. Things You Need to Know When Writing Add-ins
        1. 14.4.1. Special Requirements for Add-in Code
        2. 14.4.2. Tips on Add-in Construction
      5. 14.5. The Extras Add-in Code
        1. 14.5.1. Extras Options
        2. 14.5.2. basExtras Module
        3. 14.5.3. Back up Database
        4. 14.5.4. Back up Back End Database
        5. 14.5.5. List Query Fields
        6. 14.5.6. List Table Fields
        7. 14.5.7. Other Procedures
        8. 14.5.8. Finalizing the Add-in
      6. 14.6. Troubleshooting Add-ins
        1. 14.6.1. Interpreting Add-in Error Messages
      7. 14.7. Installing an Add-in
      8. 14.8. Using the Extras 2007 Add-in
        1. 14.8.1. Extras Options
        2. 14.8.2. Back up Database
        3. 14.8.3. Back up Back End Database
        4. 14.8.4. List Query Fields
        5. 14.8.5. List Table Fields
      9. 14.9. Summary
    3. 15. Customizing the Ribbon with XML in Access Databases and Add-ins
      1. 15.1. Useful Tools for Creating and Editing XML Code
        1. 15.1.1. XML Notepad 2007
        2. 15.1.2. VB 2005 XML Editor
        3. 15.1.3. Office 2007 Custom UI Editor
      2. 15.2. Customizing the Ribbon in an Access Database
        1. 15.2.1. Creating the XML Code
          1. 15.2.1.1. Adding a New Tab, Group, and Controls to the Ribbon
          2. 15.2.1.2. Removing a Tab or Group from the Ribbon
        2. 15.2.2. VBA Code
        3. 15.2.3. Form Ribbons
      3. 15.3. Customizing the Ribbon with an Access Add-in
      4. 15.4. Summary
    4. 16. Customizing the Access Ribbon with a Visual Studio 2005 Shared Add-in
      1. 16.1. Preparing to Write a Visual Studio Add-in
        1. 16.1.1. Adding .NET Support to Office
        2. 16.1.2. Modifying the Connect Class Module Code
        3. 16.1.3. Adding Functionality to the Shared Add-in
        4. 16.1.4. Debugging the Add-in
        5. 16.1.5. Building and Installing the Add-in
        6. 16.1.6. Using the Add-in
      2. 16.2. Summary
    5. 17. Creating Standalone Scripts with Windows Script Host
      1. 17.1. Tools for Working with Windows Script Host Scripts
        1. 17.1.1. The Microsoft Script Editor
        2. 17.1.2. The VBScript Help File
      2. 17.2. Differences between VBA and VBScript Code
      3. 17.3. Useful Scripts
        1. 17.3.1. Setup Scripts
        2. 17.3.2. Office Scripts
        3. 17.3.3. Miscellaneous Scripts
      4. 17.4. Scheduling a Backup Script with the Windows Vista Task Scheduler
      5. 17.5. Summary
    6. 18. Working with SQL Server Data
      1. 18.1. Getting SQL Server 2005
      2. 18.2. Preparing an Access Database for Upsizing to SQL Server
      3. 18.3. Configuring SQL Server 2005 for Data Access
        1. 18.3.1. Getting through the Firewall
          1. 18.3.1.1. Windows XP
          2. 18.3.1.2. Windows Vista
        2. 18.3.2. Other Security Roadblocks
      4. 18.4. Using the Upsizing Wizard
        1. 18.4.1. Converting Access Tables to SQL Server Tables
        2. 18.4.2. Creating a Client/Server Application
      5. 18.5. Linking to Data in SQL Server Tables
      6. 18.6. The SQL Server Migration Assistant for Access
      7. 18.7. Summary