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
