Access 2007 VBA Programmer′s Reference

Teresa Hennig is president of both the Pacific Northwest Access Developer Group and the Seattle Access Group, and is host for INETA s monthly webcasts. She was the coordinating author for Access 2003 VBA Programmer′s reference, and continues to publish two monthly Access newsletters. In recognition of her expertise and dedication to the Access community, Teresa was awarded Microsoft Access MVP. Rob Cooper is a test lead on the Access team at Microsoft. During the Access 2007 release, he led the security efforts across the test team and worked on several new features including disabled mode, database encryption, Office Trust Center, and sorting, grouping and totals. Rob also led efforts around the Access object model and continues to provide direction around programmability and security in Access. A long–time fan of Access, Rob is a frequent speaker at the Seattle Access Group and PNWADG meetings and has written for the Microsoft Knowledge Base and Access Advisor. Geoffrey Griffith is an avid Access user who contributed to the Access 2007 product as a Software Design Engineer in Test for the Microsoft Office Access team. He enjoys participating in software community events by attending and speaking for local users groups and helping all those who would seek it. Armen Stein is a Microsoft Access MVP and the president of J Street Technology, Inc., a team of database application developers in Redmond, Washington. Armen is President Emeritus of the Pacific Northwest Access Developers Group, and has also spoken at Seattle Access and Portland Access Users Group meetings. He has taught database classes at Bellevue Community College, and also developed and taught one–day training classes on Access and Access/SQL Server development.

Acknowledgments xi
Foreword xiii
Introduction xxxi
Chapter 1: Introduction to Microsoft Access 2007 1
A Brief History of Access 1
Is Access the Only Database System? 2
Developing Databases Without VBA Code 4
Summary 18
Chapter 2: Access, VBA, and Macros 19
VBA in Access 19
VBA versus Macros in Access 22
Summary 31
Chapter 3: New Features in Access 2007 33
Who Benefits 34
New Look 37
Development Environment 39
Forms 43
Reports 47
Embedded Macros 50
Access Data Engine 52
Integration with SharePoint 55
External Data Sources 59
Security 60
Convert with Confidence 63
ADE and Creating Runtime Files 63
What s Gone or Deprecated 65
Summary 66
Chapter 4: Using the VBA Editor 67
Anatomy of the VBA Editor 67
Using the Object Browser 69
Testing and Debugging VBA Code 71
Summary 82
Chapter 5: VBA Basics 83
VBA Objects 83
Variables and VBA Syntax 85
Other VBA Structures 104
Summary 110
Chapter 6: Using DAO to Access Data 111
Data Access Objects 111
Why Use DAO? 112
New Features in DAO 113
Referring to DAO Objects 115
The DBEngine Object 117
The Databases Collection 122
DAO Object Properties 127
Creating Schema Objects with DAO 132
Database Encryption with DAO 143
Managing Access (JET) Security with DAO 147
Data Access with DAO 157
Append Only Fields 187
Summary 189
Chapter 7: Using ADO to Access Data 191
Ambiguous References 192
Referring to ADO Objects 193
Connecting to a Data Source 193
Rolling Your Own Connection String 196
Data Access with ADO 200
Creating ADO Recordsets 204
Creating Schema Recordsets 219
Using ADO Events 221
Testing the State Property 223
Creating Schema Objects with ADOX 223
Managing Security with ADO 231
Summary 231
Chapter 8: Executing VBA 233
When Events Fire 233
VBA Procedures 238
Evaluating Expressions in VBA 247
Using Recordsets 249
Using Multiple Recordsets 252
Using VBA in Forms and Reports 256
Debugging VBA 260
Investigating Variables 261
Common VBA Techniques 267
String Concatenation Techniques 273
Summary 274
Chapter 9: VBA Error Handling 275
Why Use Error Handling? 275
Two Kinds of Errors: Unexpected and Expected 276
Basic Error Handling 278
Cleaning Up After an Error 285
More on Absorbing Expected Errors 286
Issues in Error Handling 289
Summary 290
Chapter 10: Using VBA to Enhance Forms 291
VBA Basics 291
Creating Forms the 2007 Way 295
Summary 346
Chapter 11: Enhancing Reports with VBA 347
Introduction to Reports 347
Creating a Report 351
Working with VBA in Reports 351
Important Report Events and Properties 352
Report Properties 358
Working with Charts 360
Common Report Requests 361
Layout View 369
Report View 370
Summary 373
Chapter 12: Customizing the Ribbon 375
Ribbon Overview 375
Custom Menu Bars and Toolbars 376
Ribbon Customization 377
Saving a Custom Ribbon 377
Specifying the Custom Ribbon 378
Creating an Integrated Ribbon 401
Creating a Ribbon from Scratch 407
Customizing the Office Menu 418
Customizing the Quick Access Toolbar 420
More Ribbon Tips 421
Summary 422
Chapter 13: Creating Classes in VBA 423
A Touch of Class 424
Why Use Classes? 426
Creating a Class Module 427
Naming Objects 440
Using Class Events 443
Forms as Objects 452
Variable Scope and Lifetime 458
The Me Property 461
Creating a Clone Method 463
Creating and Using Collection Classes 464
The Three Pillars 476
Summary 483
Chapter 14: Extending VBA with APIs 485
Introducing the Win32 API 485
Introducing Linking 489
Linking Libraries in Access 2007 490
Understanding C Parameters 497
Err.LastDLLError 505
Distributing Applications That Reference Type Libraries and Custom DLLs 507
Summary 507
Chapter 15: SQL and VBA 509
Working with SQL Strings in VBA 509
Using SQL When Opening Forms and Reports 513
Using SQL to Enhance Forms 514
The ReplaceOrderByClause and ReplaceWhereClause Functions 530
Chapter 16: Working with Office Applications 537
Sharing Information Is a Two–Way Street 537
Working with Outlook 538
Sending Information from Access to Excel 543
Exchanging Data with Microsoft Word 549
Sending Data to PowerPoint 553
Pulling Data from Access 556
Summary 559
Chapter 17: Working with SharePoint 561
Overview 562
Access Features on SharePoint 563
SharePoint Features in Access 576
Summary 594
Chapter 18: Database Security 595
Security for the ACCDB File Format 595
Security for the MDB File Format 606
Summary 647
Chapter 19: Understanding Client–Server Development with VBA 649
Client–Server Applications 650
Choosing the Correct File Format 652
Controlling the Logon Process 672
Binding ADODB Recordsets 678
Using Unbound Forms 684
Summary 692
Chapter 20: Working with the Win32 Registry 693
About the Registry 694
Using the Built–In VBA Registry Functions 703
Using the Win32 Registry APIs 709
Summary 720
Chapter 21: Using the ADE Tools 721
The Redistributable Access Runtime 722
The Package Solution Wizard 724
Save as Template 731
Source Code Control Support 747
Summary 753
Chapter 22: Protecting Yourself with Access 2007 Security 755
The Office Trust Center 756
Disabled Mode 761
Digital Signatures and Certificates 769
Access Database Engine Expression Service 778
Summary 780
Appendix A: Upgrading to Access 2007 783
Appendix B: References for Projects 805
Appendix C: Calling Managed Code 815
Appendix D: DAO Object Method and Property Descriptions 839
Appendix E: ADO Object Model Reference 873
Appendix F: ADO Object Argument Enumeration Information 889
Appendix G: The Access Object Model 905
Appendix H: Windows API Reference Information 973
Appendix I: Windows Registry Information 981
Appendix J: Access Wizards, Builders, and Managers 1009
Appendix K: Reserved Words and Special Characters 1017
Appendix L: Naming Conventions 1027
Appendix M: Tips and Tricks 1045
Index 1081