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

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