Contents

About the Authors vii

Acknowledgments ix

Foreword xxiii

Introduction 1

Chapter 1: Introduction to Microsoft Access_7

Why Use Microsoft Access? 7

Is Access the Only Database I'll Ever Need? 8

Microsoft Access 8

MSDE 8

SQL Server 9

Automating Microsoft Access Without VBA Code 10

The Database Wizard 10

Creating a Switchboard for Your Database 13

Begin at the Beginning 14

Create a Switchboard through the Switchboard Manager 19

Add and Configure Controls Using Wizards 20

Building Automation into Your Project 21

Using Expression Builder 22

Using the Macro Builder 23

Using the Code Builder 24

Summary 25

Chapter 2: Access, VBA, and Macros 27

VBA within Access 27

Writing Code within Modules 27

VBA versus Macros in Access 29

Macros in Access 2003 30

Advantages to Using VBA over Macros 34

Summary 35

Chapter 3: New Features in Access 2003 (and 2002)_37

Easy Upgrading 37

Database Structure and Management Tools for Developers 38

Find Object Dependencies 38

Form/Report Error Checking 40

Propagate Field Properties 42

Customize SQL Font 43

Get Context-Sensitive SQL Help 44

Backup Database/Project 46

Sorting Option on Lookup Wizard 47

Copy and Paste Linked Table as Local Table 47

Notable Mention 49

New Wizards and Builders and Managers 51

A Wizard for Every Occasion 52

Available Builders 52

Managers 53

Changes to Jet 53

Service Pack 8 53

Security and Related Features 54

What Is Macro Security? And Why Are We Talking about Macros? 54

Digital Signatures 56

Expression Sandbox 58

End-User Enhancements 59

Pivot Charts 59

Windows XP Theming 59

Templates 60

Smart Tags 61

AutoCorrect Options Buttons 62

Better Accessibility 62

XML and Access 62

Relating XML to HTML 63

The Advantages of XML 64

Using Access 2003 with SharePoint Services 66

Export Information to a SharePoint Server 66

Import Information From a SharePoint Server 67

Access Developer Extensions 68

The Property Scanner 69

The Custom Startup Wizard 69

The Package Wizard 71

Summary 72

Chapter 4: VBA Basics_73

VBA Objects 73

Objects 73

Methods 74

Events 74

Variables and VBA Syntax 75

Variables 75

Other VBA Structures 89

Comments 89

Line Continuation 91

Constants 93

Enums 94

Summary 95

Chapter 5: Using the VBA Editor_97

Anatomy of the VBA Editor 97

Your Access Database and VBA Project—Better Together 99

Using the Object Browser 99

Testing and Debugging VBA Code 102

Summary 112

Chapter 6: Using DAO to Access Data_113

Direct Access Objects 113

Why Use DAO? 114

Referring to DAO Objects 116

Default Collection Items 117

The DBEngine Object 118

The Workspaces Collection 118

The Errors Collection 122

The Databases Collection 123

The Connections Collection 128

DAO Object Properties 129

DAO Property Types 129

Creating Schema Objects with DAO 134

Managing Jet Security with DAO 142

Creating Security Objects 143

Managing Users and Groups 143

Managing Passwords 148

Managing Permissions 148

Data Access with DAO 152

Working with QueryDefs 153

Working with Recordsets 157

Filtering and Ordering Recordsets 159

Navigating Recordsets 162

Bookmarks and Recordset Clones 166

Finding Records 169

Working with Recordsets 171

Summary 175

Chapter 7: Using ADO to Access Data_177

Ambiguous References 178

Referring to ADO Objects 179

Default Collection Items 179

Connecting to a Data Source 179

Specifying a Cursor Location 180

Rolling Your Own Connection String 181

Creating and Using a Data Link 182

Data Access with ADO 185

The ADO Object Model 185

Executing Action Queries 186

Creating ADO Recordsets 190

Creating a Standard Recordset 190

Creating a Recordset from a Command Object 190

Opening a Shaped Recordset 191

Verifying the Options That a Recordset Supports 194

Referring to Recordset Columns 195

Filtering and Ordering Recordsets 196

Navigating Recordsets 196

Finding Records 197

Editing Data with Recordsets 200

Persistent Recordsets 201

Creating Schema Recordsets 206

Using ADO Events 209

Testing the State Property 210

Creating Schema Objects with ADOX 211

The ADOX Object Model 211

Working with Queries (Views) 212

Creating Tables and Columns 213

Creating Indexes 215

Creating Relations 217

Managing Jet Security with ADO 218

Creating Groups and Users 218

Managing Permissions 219

Summary 220

Chapter 8: Executing VBA_223

When Events Fire 224

Common Form Events 224

Common Control Events 225

Common Report Events 225

Asynchronous Execution 226

VBA Procedures 227

Function or Sub? 227

Public or Private? 228

Coupling and Cohesion 229

Error Handling 231

Class Modules 231

Using Variables 234

Using Appropriate Data Types and Sizes 234

Using Global Variables 235

Evaluating Expressions in VBA 235

Checking for Nulls 236

Nulls and Empty Strings 236

Select Case 237

Using Recordsets 237

Opening Recordsets 238

Looping Through Recordsets 238

Adding Records 239

Finding Records 239

Updating Records 240

Using Multiple Recordsets 240

Cleaning Up 244

Using VBA in Forms and Reports 244

All About "Me" 245

Referring to Controls 245

Referring to Subforms and Subreports 246

Sizing Reports 247

Closing Forms 247

Debugging VBA 248

Responding to Errors 248

Investigating Variables 250

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

Setting Breakpoints 252

Setting Watch Values 253

Stopping Runaway Code 255

Stepping Through Your Code 255

Common VBA Techniques 256

Date Handling 258

Handling Rounding Issues 260

String Concatenation Techniques 262

Summary 263

Chapter 9: VBA Error Handling_265

Why Use Error Handling? 265

Two Kinds of Errors: Unexpected and Expected 266

Handling Unexpected Errors 266

Absorbing Expected Errors 267

Basic Error Handling 267

Basic Error Handling with a Twist 269

Example of the Extra Resume 270

Cleaning Up After an Error 274

More on Absorbing an Expected Error: Example 275

Error Handling with Logging 278

Error Handling That Sends E-mail 279

Summary 279

Chapter 10: Using VBA to Enhance Forms_281

Concept Review 282

Properties 283

Event Properties 283

Associating Code to an Event Property 284

You Talking to Me? 286

Event Property and Procedure Examples 287

Form_Open(): Passing the OpenArgs Parameter 287

OnTimer(): Performing an Action on an Interval 288

OnClick(): Open a Form Based on Value on Current Form 289

OnCurrent(): Opening Existing Records as "Read-Only" 290

BeforeUpdate(): Performing Data Validation 291

AfterUpdate(): Synchronizing Two Combo Boxes 292

OnChange(): Late Bind a Subform on a Tab Control 293

NotInList(): Adding a Value to a Combo Box at Runtime 297

On Close(): Save a Setting to the Registry 300

Creating Forms and Controls with VBA 303

Managing Multiple Form Instances 306

When Not to Use VBA 310

Syncronized Subforms 310

Displaying Data in a Treeview Control 311

Summary 318

Chapter 11: Enhancing Reports with VBA_319

Event Properties 319

Associating Code to an Event Property 320

You Talking to Me? 322

Event Property and Procedure Examples 323

Report_Open(): Execute a Query Before Report Displays 323

Report_NoData(): What to do When There is No Data to Display 324

Section _Format(): Dynamically Display Page Numbers 326

Section_Print(): Conditional Formatting of a Text Box 327

Compute a Running Balance 328

Running Sum Property 330

The Report Design 331

VBA Code Behind Report 332

When Not to Use VBA 334

Summary 337

Chapter 12: Creating Classes in VBA 339

A Touch of Class 340

Why Use Classes? 342

Creating a Class Module 343

Adding a Class Module to the Project 343

A Brief Word on Naming the Class 344

Instantiating Class Objects 344

Creating Class Methods 345

Creating Property Procedures 348

Naming Objects 354

What Does the Object Do? 354

Verbs, Nouns, and Adjectives 355

Using Class Events 356

Handling Errors in Classes 361

Forms as Objects 365

Variable Scope and Lifetime 370

The Me Property 373

Creating and Using Collection Classes 374

The Three Pillars 384

Inheriting Interfaces 387

Instancing 391

Summary 391

Chapter 13: Extending VBA with APIs_393

Introducing the Win32 API 393

Why Do You Need the API? 395

Introducing DLLs 397

Static Linking 397

Dynamic Linking 398

Linking Libraries in Access 2003 398

Referencing a Library 398

Declaring APIs 401

Understanding C Parameters 404

Signed and Unsigned Integers 405

8-Bit Numeric Parameters 405

16-Bit Numeric Parameters 406

32-Bit Numeric Parameters 406

Currency Parameters 407

Floating-Point Parameters 407

Boolean Parameters 407

Handle Parameters 408

Object Parameters 409

String Parameters 409

Variant Parameters 410

Pointers to Numeric Values 411

Pointers to C Structures 411

Pointers to Arrays 411

Pointers to Functions 412

The Any Datatype 413

Err.LastDLLError 413

Distributing Applications That Reference Type Libraries and Custom DLLs 414

Summary 414

Chapter 14: SQL and VBA_415

Working with SQL Strings in VBA 415

Building SQL Strings with Quotes 416

Using Single Quotes Instead of Double Quotes 417

Concatenating Long SQL Strings 418

Using SQL When Opening Forms and Reports 419

Using SQL to Enhance Forms 420

Sorting on Columns 420

Selections on Index Forms 422

Cascading Combo Boxes 429

Using SQL for Report Selection Criteria 431

Altering the SQL Inside Queries 436

The ReplaceOrderByClause and ReplaceWhereClause Functions 437

Summary 443

Chapter 15: Working with Office Applications_445

Sharing Information Is a Two-Way Street 445

Access and Outlook—Sharing Data with Others 446

Working with Outlook's Security Features 450

Creating Other Types of Outlook Items from Access 451

Sending Information from Access to Excel 452

Provide Management with Flexible Data Access 452

Exchanging Information with Microsoft Word 460

The Easy Way—Use Access VBA to Start Your Merge 461

The Hard Way—Using VBA to Set Up Your Merge Document 462

Non-Mail Merge Operations—Sending Access Data to Word 463 An Advanced Example—Creating a Graph in Access

0 0

Post a comment