Access 2007 VBA Programmer's Reference

Access 2007 VBA Programmer's Reference covers a wide spectrum of programming topics relevant to Access. Although it assumes the reader has some familiarity with VBA programming language, it begins with a brief introduction to VBA. And to help you leverage the tools that Access provides, a chapter highlights the new features in Microsoft Office Access 2007 — including new wizards and GUI (graphical user interface) elements that previously required VBA code, as well as new VBA features. The book also discusses how to create and name variables, how to use Data Access Object (DAO) and ActiveX Data Object (ADO) to manipulate data both within Access and within other applications, proper error handling techniques, and advanced functions such as creating classes and using APIs. Key new objects such as using Macros and the Ribbon are explored, too, as are forms and reports, the two most powerful tools for working with and displaying data. Working with other applications is covered extensively both in a general nature and for working specifically with Microsoft Office applications, Windows SharePoint Services, and SQL Server. Of course, this book wouldn’t be complete without discussing security issues and the Developer Extensions.

Table of Contents:
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