Skip to main content

Access 2007 VBA Programmer's Reference

Access 2007 VBA Programmer's Reference

Teresa Hennig, Rob Cooper, Geoffrey L. Griffith, Armen Stein

ISBN: 978-1-118-05852-7

Jun 2011

1152 pages

Select type: E-Book



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.

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

Chapter 1 sample files
Code downloads for this title are available here.
Chapter 1 sample files
Code downloads for this title are available here.
Chapter 1 sample files
Code downloads for this title are available here.
Chapter 1 sample files
Code downloads for this title are available here.
Chapter 2 sample files
Chapter 3 sample files
Chapter 4 sample files
Chapter 5 sample files
Chapter 6 sample files
Chapter 7 sample files
Chapters 8, 9, 15 sample files
ReadMe file added 2/15/11
Chapter 10 sample files
Chapter 11 sample files
Chapter 12 sample files
Chapter 13 sample files
Chapter 14 sample files
Chapter 16 sample files
Chapter 17 sample files
Chapter 18 sample files
Chapter 19 sample files
The Chapter 19 zip was revised on 3/1/10 with an updated “SampleACCDB.accdb” file.
Chapter 22 sample files
Appendix C sample files
Appendix H sample files
Appendix I sample files
Corrected Code Downloads
Corrected Ch 19 code download
ChapterPageDetailsDatePrint Run
2Typo in Text
Line 24 on the page:

... , which make build Access database ...

Should be

... , which make building Access database ...

14Error in Text
line 8:
for Tables Queries, Forms

should be:
for Tables, Queries, Forms

19Error in Text Reference
4th paragrapgh, line 4 reads:
The Properties pane (lower-right)

It should read:
The Properties pane (lower-left)

19Error in Text
Errata, page 19, 4th paragrapgh, line 4 reads...
The Properties pane (lower-right)...
Should be...
The Properties pane (lower-left)...

137Error in Code
7th line from top:

tdf.Indexes.Delete varPKey

should be:

tdf.Indexes.Delete strPKey

137Error in Code
IntCounter spelled wrong in second line of code

137Error in Text
Second gray box of code, second line from top of box:
For intCouter = LBound.....
should be:
For int Counter = LBound.....

142Error in Text
The gray box for the code, on page 142, 2/3 down the page, is broken and the word Country looks out of place, even though it is part of the comment just above it. This initially is confusing.

144Error in Text
On page 144 in the code for Using the NewPassword Method section, before actually changing the password, shouldn't you check to see that the strNewPassword variable is the correct length. This would be consistent with other coding shown for setting/changing the password shown in the sections Compacting a Database and Creating a New Database , on pages 144 and 145.

151Error in Code
Example: Adding Users to groups Code (8th line)

Set grp = wrk.Groups(strUser)

should be:

Set grp = wrk.Groups(strGroup)

157Error in Text
Under Setting Permissions header:

set only

should be:

only set

160Error in Text
Queries that insert, update, or delete queries are known as action queries

Should be

Queries that insert, update, or delete records are known as action queries

363Error in Code
The second line of code reads:
DoCmd.OpenReport strReport, acViewPreview

but should read:
DoCmd.OpenReport strReport, acViewDesign

674, 675Error in Text
on page 674: line 28

ODBC: & _
should be
ODBC; & _

on page 675: line 15

should be

19Chapter 19 File Update
A revised zip file for Chapter 19 containing an updated “SampleACCDB.accdb” file was posted to the Download Code page on 3/1/10.

823Error in Text
The 3rd paragraph implies that the Interface for a class is NOT required to interop with COM, only if you want to have IntelliSense. However, this is not the case. The Interface is required, as shown in this MSDN article:

831Error in Text
Step 2 in the text:

name the Resource file as Ribbon.XML

The file should be named as “wrkgadm.resx”, which is how it is named in the project and text examples.


381Error in Text
The 2 VB and C# examples near the bottom of the page have a code error in them. For the VB code, on the 3rd line:

Return My.Resources.Resource1.rbnWrkGadm

Should be:

Return My.Resources.wrkgadm.rbnWrkGadm

because that is how you named the resource in the project.

Similarly, for the C# code, the return statement should be “return wrkgadm.rbnWrkGadm;”.


833Error in Text
The code to call SN.exe is slightly misleading because it needs to be run from the Visual Studio command prompt, not just any prompt. It requires that the VS tools in the path to work correctly. But the text just says command prompt, suggesting that any old windows command prompt would work, which is not the case.

833Error in Text
The regasm code needs to be called from the VS command prompt, which should probably be called out in step 2, where it only says “Launch a command window.”

833Error in Text
“The Registering managed code” section outlines four steps to registering the dll on the machine, however, when I do that it doesn’t necessarily always work for any old class. The class MUST have 2 things (see MSDN article:

a. Be COM Visible (which is eluded to several paragraphs earlier, but not directly called out that the Class is required to have the COM Visible attribute).
b. That the Class implements an Interface that must also be COM Visible. This is really the most important part, because the appendix implies that that interface is NOT required when it really is required.