Access 2007 VBA Bible: For Data-Centric Microsoft Office Applications(467 Bible)

Table of Contents:
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 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 Recordsets99 QueryDefs 104 TableDefs and Fields 106 The ADO Object Model 110 Connection110 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 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 File Dialog Object 264 The FileSystem Object 271 Backing up Your Database 277 Working with Text Files 282 Writing Data to Text Files 282 ADO..291 FileSystem Object 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 Transfer Text 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 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 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 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

About the Author :
Helen Feddema is one of the most highly respected experts on Microsoft Access. She is the author of several leading books on Microsoft Access, including the bestselling Microsoft Access Version 2002 Inside Out and Expert One-on-One Microsoft Access Application Development. She is the editor of Access Watch, for which she writes the Access Archon column. She is also an independent Access consultant. Helen Feddema is one of the most highly respected experts on Microsoft Access. She is the author of several leading books on Microsoft Access, including the bestselling Microsoft Access Version 2002 Inside Out and Expert One-on-One Microsoft Access Application Development. She is the editor of Access Watch, for which she writes the Access Archon column. She is also an independent Access consultant.