www.it-ebooks.info Access ™ 2007 VBA Bible For Data-Centric Microsoft ® Office Applications Helen Feddema 01_047026 ffirs.qxp 4/2/07 9:39 PM Page iii www.it-ebooks.info 01_047026 ffirs.qxp 4/2/07 9:39 PM Page ii www.it-ebooks.info Access ™ 2007 VBA Bible 01_047026 ffirs.qxp 4/2/07 9:39 PM Page i www.it-ebooks.info 01_047026 ffirs.qxp 4/2/07 9:39 PM Page ii www.it-ebooks.info Access ™ 2007 VBA Bible For Data-Centric Microsoft ® Office Applications Helen Feddema 01_047026 ffirs.qxp 4/2/07 9:39 PM Page iii www.it-ebooks.info Access™ 2007 VBA Bible: For Data-Centric Microsoft® Office Applications Published by Wiley Publishing, Inc. 10475 Crosspoint Boulevard Indianapolis, IN 46256 www.wiley.com Copyright © 2007 by Wiley Publishing, Inc., Indianapolis, Indiana Published by Wiley Publishing, Inc., Indianapolis, Indiana Published simultaneously in Canada ISBN: 978-0-470-04702-6 Manufactured in the United States of America 10 9 8 7 6 5 4 3 2 1 No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher for permission should be addressed to the Legal Department, Wiley Publishing, Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, (317) 572-3447, fax (317) 572-4355, or online at http://www.wiley.com/go/permissions. Limit of Liability/Disclaimer of Warranty: The publisher and the author make no representations or warranties with respect to the accuracy or completeness of the contents of this work and specifically disclaim all warranties, including without limitation warranties of fitness for a particular purpose. No warranty may be created or extended by sales or promotional materials. The advice and strategies contained herein may not be suitable for every situation. This work is sold with the understanding that the publisher is not engaged in rendering legal, accounting, or other professional services. If professional assistance is required, the services of a competent professional person should be sought. Neither the publisher nor the author shall be liable for damages arising herefrom. The fact that an organization or Website is referred to in this work as a citation and/or a potential source of further information does not mean that the author or the publisher endorses the information the organization or Website may provide or recommendations it may make. Further, readers should be aware that Internet Websites listed in this work may have changed or disappeared between when this work was written and when it is read. For general information on our other products and services or to obtain technical support, please contact our Customer Care Department within the U.S. at (800) 762-2974, outside the U.S. at (317) 572-3993 or fax (317) 572-4002. Library of Congress Cataloging-in-Publication Data: Feddema, Helen Bell. Access 2007 VBA Bible: For Data-Centric Microsoft Office Applications / Helen Feddema. p. cm. Includes index. ISBN 978-0-470-04702-6 (paper/website) 1. Microsoft Access. 2. Database management. 3. Microsoft Visual Basic for applications. I. Title. QA76.9.D3F435 2007 005.75’65--dc22 2007007061 Trademarks: Wiley, the Wiley logo, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United States and other countries, and may not be used without written permission. Microsoft and Access are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. All other trademarks are the property of their respective owners. Wiley Publishing, Inc., is not associated with any product or vendor mentioned in this book. Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic books. 01_047026 ffirs.qxp 4/2/07 9:39 PM Page iv www.it-ebooks.info About the Author Helen Feddema is an independent developer specializing in Microsoft Office applications, concen- trating on Access, Word, and (since 1996) Outlook. She has written or co-authored many books on Access and other Office applications, including Inside Microsoft Access (New Riders, 1992), Power Forms for Microsoft Access and Power Reports for Microsoft Access (Pinnacle, 1994), Access How- Tos (Waite Group Press, 1995), MCSD: Access 95 Study Guide (Sybex, 1998), DAO Object Model: The Definitive Guide (O’Reilly, January 2000), and Access 2002 Inside-Out (Microsoft Press, 2001), which was judged #1 in the Desktop and Office Applications book category at the Waterside 2004 confer- ence. Her most recent book is Expert One-on-One Microsoft Access Application Development (Wiley/Wrox, 2003). She has also contributed chapters to a number of multi-author Office books, including Special Edition: Using Microsoft Outlook 97 (Que, 1997), Office Annoyances (O’Reilly, 1997), Outlook Annoyances (O’Reilly, 1998), Special Edition: Using Microsoft Project 98 (Que, 1997), Teach Yourself Project (Sams, 1998), and Special Edition: Outlook 2000 (Que, 2000). Helen has been a regular con- tributor to Pinnacle’s Smart Access and Office Developer journals, Informant’s Microsoft Office and VBA Programming (now Office Solutions), and Woody’s Underground Office newsletter, and she is cur- rently the editor of the Access Watch ezine (formerly Woody’s Access Watch), for which she writes the Access Archon column. 01_047026 ffirs.qxp 4/2/07 9:39 PM Page v www.it-ebooks.info Credits Executive Editor Bob Elliott Development Editor Kelly Talbot Technical Editor Mary Hardy Production Editor Eric Charbonneau Copy Editor Kim Cofer Editorial Manager Mary Beth Wakefield Production Manager Tim Tate Vice President and Executive Group Publisher Richard Swadley Vice President and Executive Publisher Joseph B. Wikert Project Coordinator Adrienne Martinez Graphics and Production Specialists Sean Decker Jennifer Mayberry Heather Pope Amanda Spagnuolo Quality Control Technicians Melanie Hoffman Robert Springer Brian Walls Proofreading and Indexing Aptara Anniversary Logo Design Richard Pacifico To Kerry and Jean King, good neighbors and good friends 01_047026 ffirs.qxp 4/2/07 9:39 PM Page vi www.it-ebooks.info About the Author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . v Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv Part I: The Office Components and What They Do Best 1 Chapter 1: Storing and Displaying Data in Access . . . . . . . . . . . . . . . 3 A Brief History of Office Data Exchange ................................................................................3 Storing Data in Access ..........................................................................................................4 Displaying Data in Access Forms and Reports ......................................................................5 Creating Access Form Letters ......................................................................................8 Creating Worksheet-type Reports in Access ..............................................................11 Plain Datasheet Reports ..................................................................................11 PivotTables ......................................................................................................19 PivotCharts......................................................................................................22 Summary ............................................................................................................................25 Chapter 2: Creating Word Documents from Access . . . . . . . . . . . . . . 27 Filling Word Documents with Access Data Using the TypeText Method ..............................27 Using Word Templates for Creating Formatted Word Documents........................................30 Bookmarks ................................................................................................................31 Document Properties ................................................................................................39 Form Field Documents........................................................................................................45 Summary ............................................................................................................................48 Chapter 3: Analyzing Data with Excel . . . . . . . . . . . . . . . . . . . . . 49 Exporting Access Data to an Unformatted Worksheet..........................................................50 Using Excel Templates to Create Formatted Worksheets Filled with Access Data ................52 Formatting Excel Worksheets in VBA Code ........................................................................62 Summary ............................................................................................................................69 Chapter 4: Organizing and Communicating with Outlook . . . . . . . . . . 71 Exporting Appointments and Tasks to Outlook ..................................................................72 Exporting Journal Information to Outlook ..........................................................................79 Creating Emails from an Access Table..................................................................................81 Summary ............................................................................................................................85 vii 02_047026 ftoc.qxp 4/2/07 9:40 PM Page vii www.it-ebooks.info Part II: Writing VBA Code to Exchange Data between Office Components 87 Chapter 5: Working with Access Data . . . . . . . . . . . . . . . . . . . . . 89 Working with Older Format Databases in Access 2007 ......................................................91 Disambiguating References to Object Model Components ........................................91 The DAO Object Model (Old and New) ....................................................................96 New Objects in the Access 2007 DAO Object Model ......................................97 Hidden Objects in the Access 2007 DAO Object Model ..................................98 Databases ........................................................................................................99 Recordsets........................................................................................................99 QueryDefs ....................................................................................................104 TableDefs and Fields......................................................................................106 The ADO Object Model ..........................................................................................110 Connection....................................................................................................110 Command......................................................................................................113 Recordset ......................................................................................................116 Record ..........................................................................................................129 Stream ..........................................................................................................129 Converting DAO Code to ADO Code................................................................................129 Summary ..........................................................................................................................131 Chapter 6: Working with Word Documents and Templates . . . . . . . . . 133 Built-in Word Export in Office 2007 ................................................................................135 Exporting Access Data to Word Using Automation Code ..................................................138 The Word Object Model ..........................................................................................139 Creating a New, Blank Word Document ........................................................140 Creating a Word Document Based on a Template ..........................................141 Using a Query to Concatenate Data for Export ..............................................143 Choosing a Method for Merging Access Data to Word ............................................145 Working with Word Document Properties ....................................................146 Word Bookmarks ..........................................................................................165 The TypeText Method ....................................................................................170 Word Mail Merge ..........................................................................................175 Summary ..........................................................................................................................182 Chapter 7: Working with Excel Worksheets. . . . . . . . . . . . . . . . . . 183 Simply Exporting Access Data to Excel..............................................................................184 The Excel Object Model ....................................................................................................187 Minimally Formatted Worksheets ....................................................................................191 Tabular Worksheets Formatted from Code ........................................................................195 Timesheets ........................................................................................................................202 Summary ..........................................................................................................................218 viii Contents 02_047026 ftoc.qxp 4/2/07 9:40 PM Page viii www.it-ebooks.info Chapter 8: Working with Outlook Items . . . . . . . . . . . . . . . . . . . 219 Exporting Access Data to Outlook Items ..........................................................................220 Using the Collect Data Group..................................................................................220 Using the Import Group to Import or Link to Outlook Data....................................221 Using Sample Databases and Forms ........................................................................230 The Outlook Object Model ..............................................................................................234 Explorer, Inspector, and other Outlook Objects ......................................................234 Syntax for Referencing Outlook Objects ..................................................................235 Referencing Outlook Items in VBA Code ................................................................240 Working with Outlook Appointments ..............................................................................241 Working with Outlook Tasks ............................................................................................247 Working with Outlook Contacts........................................................................................254 Summary ..........................................................................................................................262 Chapter 9: Working with Files and Folders . . . . . . . . . . . . . . . . . . 263 Working with Windows Explorer Folders ........................................................................264 The Office FileDialog Object....................................................................................264 The FileSystemObject..............................................................................................271 Backing up Your Database ................................................................................................277 Working with Text Files ....................................................................................................282 Writing Data to Text Files ........................................................................................282 ADO ..............................................................................................................291 FileSystemObject ..........................................................................................292 VB ................................................................................................................292 Reading Data from Text Files ..................................................................................292 ADO ..............................................................................................................296 FSO ..............................................................................................................296 VB ................................................................................................................296 Working with Attachment Fields ......................................................................................297 Loading Files into Attachment Fields ......................................................................299 Saving Attachments to Files ....................................................................................302 Summary ..........................................................................................................................304 Chapter 10: Working with External Data . . . . . . . . . . . . . . . . . . . 305 Working with Text Files Using the TransferText Method....................................................306 Creating an Import or Export Specification..............................................................306 Importing and Exporting Text File Data in VBA Code..............................................312 Importing Text Data ......................................................................................312 Exporting Text Data ......................................................................................320 Working with Legacy Database and Spreadsheet Files ......................................................324 Importing Database Files ........................................................................................324 Importing Spreadsheet Files ....................................................................................328 Exporting Database and Spreadsheet Files ..............................................................336 ix Contents 02_047026 ftoc.qxp 4/2/07 9:40 PM Page ix www.it-ebooks.info Working with XML and HTML Files ................................................................................338 Importing HTML and XML Files ............................................................................338 Exporting HTML and XML Files..............................................................................341 Emailing Exported Text Files ............................................................................................348 Summary ..........................................................................................................................350 Chapter 11: Synchronizing Access and Outlook Contacts . . . . . . . . . . 351 Creating a Denormalized Table from a Set of Linked Tables ..............................................352 Comparing Outlook and Access Contacts..........................................................................359 Re-creating the Flat-file Tables of Access and Outlook Data ....................................361 Copying Contact Data from Access to Outlook (or Vice Versa) ................................374 Working with Attachments................................................................................................391 Summary ..........................................................................................................................396 Chapter 12: Going Beyond the Basics. . . . . . . . . . . . . . . . . . . . . 397 Creating Fancy Word Shipping Labels ..............................................................................397 Creating Excel PivotCharts from Access Queries ..............................................................415 Emailing Shipping and Reordering Reports ......................................................................426 Summary ..........................................................................................................................432 Part III: Adding More Functionality to Office 427 Chapter 13: Creating COM Add-ins with Visual Basic 6 . . . . . . . . . . . 435 Creating a COM Add-in Using Visual Basic 6.0 ................................................................436 Using the COM Add-in Template ............................................................................436 Creating the LNC Control Renaming COM Add-in..................................................440 The SharedCode Module ..............................................................................440 The AccessDesigner Module ..........................................................................442 Creating the DLL ..........................................................................................463 Installing a COM Add-in ........................................................................................463 Troubleshooting a COM Add-in ..............................................................................466 Using a COM Add-in ........................................................................................................467 Comparing COM Add-ins with Access Add-ins ................................................................469 Summary ..........................................................................................................................469 Chapter 14: Creating Access Add-ins . . . . . . . . . . . . . . . . . . . . . 471 The Purpose of Access Add-ins..........................................................................................472 Add-in Types ....................................................................................................................472 Creating a Library Database ..............................................................................................473 Menu Add-ins ........................................................................................................478 Wizards ..................................................................................................................479 Property Builders ....................................................................................................480 Things You Need to Know When Writing Add-ins ............................................................481 Special Requirements for Add-in Code ....................................................................481 Tips on Add-in Construction ..................................................................................482 x Contents 02_047026 ftoc.qxp 4/2/07 9:40 PM Page x www.it-ebooks.info The Extras Add-in Code....................................................................................................483 Extras Options ........................................................................................................483 basExtras Module ....................................................................................................486 Back up Database ....................................................................................................489 Back up Back End Database ....................................................................................491 List Query Fields ....................................................................................................495 List Table Fields ......................................................................................................497 Other Procedures ....................................................................................................499 Finalizing the Add-in ..............................................................................................502 Troubleshooting Add-ins ..................................................................................................503 Interpreting Add-in Error Messages ........................................................................506 Installing an Add-in ..........................................................................................................507 Using the Extras 2007 Add-in ..........................................................................................510 Extras Options ........................................................................................................511 Back up Database ....................................................................................................512 Back up Database Back End ....................................................................................512 List Query Fields ....................................................................................................513 List Table Fields ......................................................................................................514 Summary ..........................................................................................................................514 Chapter 15: Customizing the Ribbon with XML in Access Databases and Add-ins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 515 Useful Tools for Creating and Editing XML Code ..............................................................517 XML Notepad 2007 ................................................................................................517 VB 2005 XML Editor ..............................................................................................518 Office 2007 Custom UI Editor ................................................................................518 Customizing the Ribbon in an Access Database ................................................................519 Creating the XML Code ..........................................................................................522 Adding a New Tab, Group, and Controls to the Ribbon ................................527 Removing a Tab or Group from the Ribbon ..................................................528 VBA Code................................................................................................................542 Form Ribbons..........................................................................................................547 Customizing the Ribbon with an Access Add-in................................................................549 Summary ..........................................................................................................................558 Chapter 16: Customizing the Access Ribbon with a Visual Studio 2005 Shared Add-in. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 559 Preparing to Write a Visual Studio Add-in ........................................................................559 Adding .NET Support to Office ........................................................................................559 Modifying the Connect Class Module Code ............................................................573 Adding Functionality to the Shared Add-in ............................................................575 Debugging the Add-in ............................................................................................582 Building and Installing the Add-in ..........................................................................583 Using the Add-in ....................................................................................................588 Summary ..........................................................................................................................589 xi Contents 02_047026 ftoc.qxp 4/2/07 9:40 PM Page xi www.it-ebooks.info Chapter 17: Creating Standalone Scripts with Windows Script Host . . . . 591 Tools for Working with Windows Script Host Scripts........................................................591 The Microsoft Script Editor ....................................................................................592 The VBScript Help File............................................................................................597 Differences between VBA and VBScript Code ....................................................................599 Useful Scripts ....................................................................................................................603 Setup Scripts ..........................................................................................................603 Office Scripts ..........................................................................................................611 Miscellaneous Scripts ..............................................................................................616 Scheduling a Backup Script with the Windows Vista Task Scheduler ................................619 Summary ..........................................................................................................................626 Chapter 18: Working with SQL Server Data . . . . . . . . . . . . . . . . . 627 Getting SQL Server 2005 ..................................................................................................628 Preparing an Access Database for Upsizing to SQL Server ................................................629 Configuring SQL Server 2005 for Data Access ..................................................................631 Getting through the Firewall....................................................................................640 Windows XP..................................................................................................640 Windows Vista ..............................................................................................643 Other Security Roadblocks ......................................................................................647 Using the Upsizing Wizard................................................................................................648 Converting Access Tables to SQL Server Tables ......................................................649 Creating a Client/Server Application........................................................................657 Linking to Data in SQL Server Tables ................................................................................662 The SQL Server Migration Assistant for Access..................................................................671 Summary ..........................................................................................................................672 Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 673 xii Contents 02_047026 ftoc.qxp 4/2/07 9:40 PM Page xii www.it-ebooks.info