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.

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.

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.

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

Speci…