Access 2007 VBA Programmer's Reference

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