Contents

PC Repair Tools

Advanced Registry Cleaner PC Diagnosis and Repair

Get Instant Access

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

Microsoft Office Access 2007 2

SQL Server 2005 Express Edition 3

SQL Server 2005 3

How Do You Choose? 4

Developing Databases Without VBA Code 4

Access 2007 Database Templates 5

Access Database Objects 8

Creating Tables 8

Creating Queries 11

Creating Forms 13

Creating Reports 15

Creating Macros 17

Summary 18

Chapter 2: Access, VBA, and Macros_19

VBA in Access 19

Writing Code in Modules 19

Writing Code Behind Forms and Reports 20

VBA versus Macros in Access 22

Creating Macros in Access 2007 23

New Features for Macros in Access 2007 26

Why All the Changes? 30

Advantages to Using VBA over Macros 31

Summary 31

Chapter 3: New Features in Access 2007_33

Who Benefits 34

The End User 34

The Power User 34

The IT Department 35

The Developer 36

New Look 37

Getting Started 38

The Ribbon 38

Tabbed Document 39

Development Environment 39

Navigation Pane 40

Data Source Task Pane 40

Table and Field Templates 40

Field Insertion and Automatic Data Type Detection 41

Interactive Form and Report Designing 41

Field (Column) History 41

Rich Text Fields 41

Search from the Record Selector 42

Save Database As 42

Managed Code and Access Add-ins 43

Forms 43

Split Forms 43

Alternating Row Color 44

Grouped Control 44

New Filtering and Sorting 45

Column Summaries 45

Truncated Number Displays 45

Date Picker 45

Bound Image Controls 46

Edit List Items 46

SubForm 47

Reports 47

Properties, Methods, and Events 47

Layout View 48

Report Browse 48

Group and Total 48

Grid Line Controls and Alternating Row Color 49

PivotTable Views and Charts 49

PDF and XPS Support 49

Embedded Macros 50

Disabled Mode 51

Error Handling 51

Debugging 51

Temporary Variables 51

Access Data Engine 52

Multi-Value Fields 52

Attachment Field Type 53

XML Tables, Schema, and Data 54

Integration with SharePoint 55

Working with Data on SharePoint 56

Publish the Database to SharePoint 57

Additional SharePoint Features 57

External Data Sources 59

Excel 59

Outlook 59

SQL Server 60

Security 60

Encryption with Database Password 60

Signed Database Package 61

Trusted Locations 61

Message Bar 62

Disabled Mode 62

Convert with Confidence 63

Secured Databases 63

Save as MDB 63

ADE and Creating Runtime Files 63

Runtimes 64

Package Wizard 64

Database Template Creator 64

Source Code Control Support 65

What's Gone or Deprecated 65

Data Access Pages 65

Import RunCommand 65

Snapshot Viewer 66

User Interface: Legacy Export and Import Formats 66

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

When Should You Debug Your Code? 71

Immediate Window 72

The Debug.Print Statement 73

The Debug.Assert Statement 74

Breakpoints 74

Stepping Through Code 75

Call Stack 77

Run to Cursor 79

Locals Window 79

Watch Window 80

On-the-Fly Changes 82

Summary 82

Chapter 5: VBA Basics_83

VBA Objects 83

Properties 84

Methods 84

Events 84

Variables and VBA Syntax 85

Variables 85

Naming Your Variables 99

Variable Scope and Lifetime 100

Overlapping Variables 101

Other VBA Structures 104

Comments 104

Line Continuation 105

Constants 107

Enums 109

Summary 110

Chapter 6: Using DAO to Access Data_111

Data Access Objects 111

Why Use DAO? 112

New Features in DAO 113

Multi-Value Lookup Fields 113

Attachment Fields 114

Append Only Fields 114

Database Encryption 115

Referring to DAO Objects 115

The DBEngine Object 117

The Workspaces Collection 117

The Errors Collection 121

The Databases Collection 122

The Default (Access) Database 123

Closing and Destroying Database Object References 126

DAO Object Properties 127

DAO Property Types 127

Creating, Setting, and Retrieving Properties 128

Creating Schema Objects with DAO 132

Creating Tables and Fields 133

Creating Indexes 136

Creating Relations 138

Creating Multi-Value Lookup Fields 140

Database Encryption with DAO 143

Setting the Database Password 143

Setting Encryption Options 146

Managing Access (JET) Security with DAO 147

Creating Security Objects 147

Managing Users and Groups 148

Managing Passwords 153

Managing Permissions 153

Data Access with DAO 157

Working with QueryDefs 158

Working with Recordsets 162

Filtering and Ordering Recordsets 164

Navigating Recordsets 167

Bookmarks and Recordset Clones 172

Finding Records 175

Working with Recordsets 178

Working with Attachment Fields 182

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

Specifying a Cursor Location 195

Server-Side Cursors 195

Client-Side Cursors 195

Rolling Your Own Connection String 196

Creating and Using a Data Link 197

Using Transactions 198

Data Access with ADO 200

Overview of the ADO Object Model 200

Using the Execute Method 201

Creating ADO Recordsets 204

Creating a Recordset from a Command Object 205

Opening a Shaped Recordset 206

Verifying the Options a Recordset Supports 208

Referring to Recordset Columns 209

Filtering and Ordering Recordsets 209

Navigating Recordsets 209

Finding Records 210

Editing Data with Recordsets 212

Persistent Recordsets 214

Creating Schema Recordsets 219

Using ADO Events 221

Testing the State Property 223

Creating Schema Objects with ADOX 223

The ADOX Object Model 223

Working with Queries (Views) 224

Creating Tables and Columns 226

Creating Indexes 228

Creating Relationships 230

Managing Security with ADO 231

Summary 231

Chapter 8: Executing VBA_233

When Events Fire 233

Common Form Events 234

Common Control Events 235

Common Report Events 236

Asynchronous Execution 237

VBA Procedures 238

Function or Sub? 238

Public or Private? 239

Coupling and Cohesion 241

Error Handling 242

Class Modules 242

Using Variables 245

Evaluating Expressions in VBA 247

Checking for Nulls 248

Select Case 249

Using Recordsets 249

Opening Recordsets 250

Looping Through Recordsets 250

Adding Records 251

Finding Records 251

Updating Records 252

Using Multiple Recordsets 252

Copying Trees of Parent and Child Records 252

Using Bookmark and RecordsetClone 254

Cleaning Up 256

Using VBA in Forms and Reports 256

All About Me 257

Referring to Controls 257

Referring to Subforms and Subreports 258

Sizing Reports 259

Closing Forms 259

Debugging VBA 260

Investigating Variables 261

When Hovering Isn't Enough — Using the Immediate Window 263

Setting Breakpoints 264

Setting Watch Values 265

Stopping Runaway Code 265

Stepping Through Your Code 266

Common VBA Techniques 267

Drilling Down with Double-Click 268

Date Handling 269

Handling Rounding Issues 271

String Concatenation Techniques 273

The Difference Between & and + 273

String Concatenation Example 274

Summary 274

Chapter 9: VBA Error Handling_275

Why Use Error Handling? 275

Two Kinds of Errors: Unexpected and Expected 276

Handling Unexpected Errors 276

Absorbing Expected Errors 277

Basic Error Handling 278

Basic Error Handling with an Extra Resume 279

Basic Error Handling with a Centralized Message 284

Cleaning Up After an Error 285

More on Absorbing Expected Errors 286

Issues in Error Handling 289

Don't Use Error Handling with Logging 289

Don't Use Error Handling That Sends e-mail 290

Summary 290

Chapter 10: Using VBA to Enhance Forms_291

VBA Basics 291

Properties 292

Event Properties: Where Does the Code Go? 293

Naming Conventions 294

Creating Forms the 2007 Way 295

Tabular and Stacked Layouts 296

Anchoring 298

The Modal Dialog Mode 299

Control Wizards — Creating Command Buttons Using VBA or Macros 299

Command Button Properties 300

New Attachment Controls 301

Combo Boxes 305

Synchronizing Two Combo Boxes Using AfterUpdate() 314

BeforeUpdate Event 317

Saving E-mail Addresses Using Textbox AfterUpdate Event 319

Output to PDF 322

OpenArgs 323

IsLoaded() 324

Late Binding 327

On Click(): Open a Form Based on a Value on the Current Form 331

Multiple Form Instances 333

Displaying Data in TreeView and ListView Controls 339

Summary 346

Chapter 11: Enhancing Reports with VBA_347

Introduction to Reports 347

How Reports Are Structured 347

How Reports Differ from Forms 348

New in Access 2007 349

Creating a Report 351

Working with VBA in Reports 351

Control Naming Issues 351

The Me Object 352

Important Report Events and Properties 352

Opening a Report 352

Section Events 354

Closing a Report 358

Report Properties 358

Section Properties 359

Control Properties 360

Working with Charts 360

Common Report Requests 361

Gathering Information from a Form 361

Changing the Printer 362

Dictionary-Style Headings 363

Shading Alternate Rows 365

Conditional Formatting of a Control 366

Dashboard Reports 367

Creating a Progress Meter Report 367

Layout View 369

Report View 370

Considerations When Designing for Report View 370

Interactivity 371

Summary 373

Chapter 12: Customizing the Ribbon_375

Ribbon Overview 375

Custom Menu Bars and Toolbars 376

Custom Menu Bars 376

Shortcut Menu Bars 377

Ribbon Customization 377

Saving a Custom Ribbon 377

Specifying the Custom Ribbon 378

Defining a Ribbon Using XML 378

Writing Callback Routines and Macros 390

More Callback Routines 392

Displaying Images 396

Refreshing Ribbon Content 399

Creating an Integrated Ribbon 401

Building the Report Manager 401

Building the Custom Filter Interface 404

Creating a Ribbon from Scratch 407

Defining the Tabs and Groups 408

Building the Home Tab 410

Building the Settings Tab 415

Building the Administration Tab 417

Customizing the Office Menu 418

Customizing the Quick Access Toolbar 420

More Ribbon Tips 421

Additional Resources 421

Summary 422

Chapter 13: Creating Classes in VBA_423

A Touch of Class 424

Why Use Classes? 426

Creating a Class Module 427

Adding a Class Module to the Project 427

A Brief Word on Naming the Class 428

Instantiating Class Objects 429

Creating Class Methods 430

Creating Property Procedures 432

Naming Objects 440

What Does the Object Do? 441

Verbs, Nouns, and Adjectives 441

Using Class Events 443

Initialize and Terminate Events 443

Creating Custom Class Events 444

Responding to Events 445

Handling Errors in Classes 448

Forms as Objects 452

Variable Scope and Lifetime 458

The Me Property 461

Subclassing the Form 461

Creating the Subclassed Form 462

Creating a Parent Property 462

Creating a Clone Method 463

Creating and Using Collection Classes 464

The Collection Object 464

Collection Class Basics 467

The Three Pillars 476

Encapsulation 476

Inheritance 477

Polymorphism 478

Inheriting Interfaces 478

Instancing 482

Summary 483

Chapter 14: Extending VBA with APIs_485

Introducing the Win32 API 485

Know the Rules, Program with Confidence 486

Why You Need the API 487

Introducing Linking 489

Static Linking 489

Dynamic Linking 490

Linking Libraries in Access 2007 490

Referencing a Library 490

How Microsoft Access Resolves VBA References 492

Declaring APIs 493

Understanding C Parameters 497

Signed and Unsigned Integers 498

Numeric Parameters 498

Object Parameters 501

String Parameters 501

Variant Parameters 503

Pointers to Numeric Values 503

Pointers to C Structures 503

Pointers to Arrays 504

Pointers to Functions 505

The Any Data Type 505

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

Building SQL Strings with Quotes 510

Using Single Quotes Instead of Double Quotes 511

Concatenating Long SQL Strings 512

Using SQL When Opening Forms and Reports 513

Using SQL to Enhance Forms 514

Sorting on Columns 514

Selections on Index Forms 516

Cascading Combo Boxes 522

Using SQL for Report Selection Criteria 524

Altering the SQL Inside Queries 529

The ReplaceOrderByClause and ReplaceWhereClause Functions 530

Summary 536

Chapter 16: Working with Office Applications_537

Sharing Information Is a Two-Way Street 537

Working with Outlook 538

Working with Outlook's Security Features 540

Creating Other Types of Outlook Objects from Access 542

Sending Information from Access to Excel 543

Working with Data in Excel 543

Using the Excel OM to Create a New Workbook 545

Using TransferSpreadsheet to Create a New Worksheet 547

Exchanging Data with Microsoft Word 549

Automate Word to Start Your Merge 549

Using VBA to Set Up Your Merge Document 551

Sending Access Objects to Word 552

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

Access Web Datasheet 564

Open with Access 566

Importing from SharePoint 572

SharePoint Features in Access 576

Linked Tables to SharePoint 577

Migrate to SharePoint 583

Publish Database to SharePoint 588

Access Views on SharePoint 591

Summary 594

Chapter 18: Database Security_595

Security for the ACCDB File Format 595

Shared-Level Security for ACCDBs 597

Securing VBA Code in ACCDB 602

Security for the MDB File Format 606

Shared-Level Security 611

Encoding an MDB File 612

Securing VBA Code for MDBs 614

User-Level Security 616

Methods To Create User-Level Security 621

Using the User-Level Security Wizard 621

Using the Access User Interface 623

User-Level Security Using DAO 628

User-Level Security Using ADO 636

User-Level Security Using ADOX 647

Summary 647

Chapter 19: Understanding Client-Server Development with VBA_649

Client-Server Applications 650

Using the Sample Files 650

Installing the Sample Database 651

Choosing the Correct File Format 652

What Are ACCDB and MDB Files? 652

What Is an ADP? 664

Choosing Between ACCDB/MDB and ADP 670

Controlling the Logon Process 672

Using Linked Tables in ACCDB/MDB Files 672

Using Access Projects 675

Binding ADODB Recordsets 678

Binding to a Form, ComboBox, or ListBox 679

Binding to a Report 679

Using Persisted Recordsets 682

Using Unbound Forms 684

Why Use Unbound Forms? 684

Creating Unbound Forms 685

Summary 692

Chapter 20: Working with the Win32 Registry_693

About the Registry 694

What the Registry Does 694

What the Registry Controls 695

Accessing the Registry 696

Registry Organization 697

Registry Organization on x64-Based Windows 702

Using the Built-In VBA Registry Functions 703

SaveSetting 704

GetSetting 704

GetAllSettings 705

DeleteSetting 706

Typical Uses for the Built-In VBA Registry Functions 707

Using the Win32 Registry APIs 709

Getting Started 710

The Function to Create a Key 713

The Function to Set a Key Value 714

The Function to Get a Key Value 715

The Function to Delete a Key Value 717

The Function to Delete a Key 718

Testing the Function Wrappers 718

Summary 720

Chapter 21: Using the ADE Tools_721

The Redistributable Access Runtime 722

Creating Runtime Databases 723

What's the Difference Between Access and the Runtime? 723

The Package Solution Wizard 724

Step 1: Starting the Package Wizard 724

Step 2: Database Solution Installation Options 725

Step 3: Additional Files and Registry Keys 728

Step 4: Additional MSI File Settings 729

Finishing the Wizard 730

Additional Information About MSI Files 731

Save as Template 731

Creating ACCDT Files 732

Deploying ACCDT Files 734

The ACCDT File Format 737

Source Code Control Support 747

Install the Source Code Control Program 747

Using the Source Code Control 748

Summary 753

Chapter 22: Protecting Yourself with Access 2007 Security_755

The Office Trust Center 756

What Is the Trust Center? 756

Trust Center Features 757

Disabled Mode 761

Why Do We Have Disabled Mode? 762

Enabling a Database 763

Modal Prompts 765

AutomationSecurity 765

Macros in Access 2007 767

Digital Signatures and Certificates 769

Types of Digital Certificates 770

Using Self-Certification 772

Signed Packages 776

Access Database Engine Expression Service 778

Sandbox Mode in Access 2007 778

Sandbox Mode Limitations 779

Workarounds 780

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

Introduction

Welcome to Access 2007 VBA Programmer's Reference. This release of Access probably has the most dramatic changes for developers and users since Access 97 and arguably since Access 2.0. With changes of this magnitude, you will want to leverage community resources to get up to speed quickly so that you are working smarter and more efficiently. That's where this book comes in.

Why this book? It has an unparalleled team of authors and tech editors who are as devoted to helping fellow developers as they are passionate about the product. Armen and Teresa have both earned Access MVP status in recognition of their expertise and contributions to the Access community, and Rob and Geoff are members of the Microsoft Access test team. They have the level of familiarity with Access 2007 that can only be developed through time and use. Both of the tech editors are testers on the Microsoft Access team, so they too have been working with Access 2007 for more than a year. In addition to editing, they also contributed resources, suggestions, and some of the tips in Appendix M. Every member of the team has been working with Access since 97 or before. Even with this remarkable level of expertise, we took the opportunity to complement our own experiences with contributions from other developers to bring you the best available information on using VBA (Microsoft Visual Basic for Applications) in Access 2007.

Many of the new features in Access 2007 can accomplish tasks that previously required VBA programming. In addition to reducing development time, these features can create better and more professional looking solutions. For many of us, being able to take advantage of the new features, right out of the box, is more than enough reason to upgrade. So although the primary focus of this book is to help you extend the power of Access by adding VBA, we identify the new features of Access 2007. Because many of you are familiar with prior versions of Access, we also point out some of the major changes, particularly if they affect the way that you will be working.

The goal is for Access 2007 VBA Programmer's Reference to be your primary resource and tool to help you leverage both Access's built-in functionality and VBA in a manner that helps you to create the best applications that you can imagine. Access 2007 makes it easy to start working as soon as it's installed. With the new UI (user interface), people will be building complex applications using the tools and resources that ship with Access. And, with a little outside guidance, they can work a lot smarter, with more confidence, and avoid several pitfalls. So, this book is for the typical Access user as well as the seasoned programmer. It will help you utilize the power of Microsoft Access more effectively and help you choose when to let the wizards do the work, as well as showing you how to modify and enhance the code that the wizards create. Access builds great forms and reports that can be customized on-the-fly by using VBA code to respond to a multitude of events. Interactive reports, or report browse, may be the ultimate example of the power and potential of Access. And Access now offers invaluable opportunities to integrate with external applications and multiple data sources. It's almost as easy as "a click of a button" to retrieve data from e-mail or to work with SharePoint and other online services. You can even use SharePoint for deployment and version control.

With all the new templates, macros, wizards, and help files, it is easier than ever to open the program and quickly start creating tables, forms, and reports. When you consider how easy it is to get started, you'll realize that it is doubly important to be working smart and in the right direction. Use this book and its online resources as your guide to better programming and more effective solutions.

Was this article helpful?

0 0

Post a comment