Contents

Acknowledgments xiv

Introduction xv

Part I — Introduction to Access 2003 VBA Programming

Chapter 1 Procedures and Modules 3

Procedure Types 3

Module Types 4

Events, Event Properties, and Event Procedures 6

Why Use Events? 7

Walking Through an Event Procedure 7

Compiling Your Procedures 11

Chapter Summary 11

Chapter 2 The Visual Basic Editor (VBE) 12

Understanding the Project Explorer Window 12

Understanding the Properties Window 13

Understanding the Code Window 14

Other Windows in the VBE 16

On-the-Fly Syntax and Programming Assistance 16

List Properties/Methods 17

Parameter Info 18

List Constants 19

Quick Info 19

Complete Word 20

Indent/Outdent 20

Comment Block/Uncomment Block 21

Using the Object Browser 22

Using the VBA Object Library 24

Using the Immediate Window 25

Chapter Summary 27

Chapter 3 Variables, Data Types, and Constants 28

What Is a Variable? 28

What Are Data Types? 29

Creating Variables 30

Declaring Variables 31

Specifying the Data Type of a Variable 33

Using Type Declaration Characters 35

Assigning Values to Variables 36

Forcing Declaration of Variables 38

Understanding the Scope and Lifetime of Variables 40

Procedure-Level (Local) Variables 40

Module-Level Variables 41

Public Variables 42

Understanding and Using Static Variables 43

Declaring and Using Object Variables 45

Finding a Variable Definition 47

What Type Is This Variable? 47

Using Constants in VBA Procedures 48

Intrinsic Constants 49

Chapter Summary 51

Chapter 4 Passing Arguments to Procedures and Functions 52

Writing a Function Procedure 52

Specifying the Data Type for a Function's Result 54

Passing Arguments by Reference and by Value 56

Using Optional Arguments 57

Using the IsMissing Function 59

Built-in Functions 59

Using the MsgBox Function 59

Returning Values from the MsgBox Function 66

Using the InputBox Function 67

Converting Data Types 69

Using Master Procedures and Subprocedures 71

Chapter Summary 75

Chapter 5 Decision Making with VBA 76

If.. .Then Statement 77

Multi-Line If.. .Then Statement 78

Decisions Based on More Than One Condition 80

If...Then...Else Statement 81

Nested If.. .Then Statements 84

Select Case Statement 85

Using Is with the Case Clause 88

Specifying a Range of Values in a Case Clause 88

Specifying Multiple Expressions in a Case Clause 90

Chapter Summary 90

Chapter 6 Repeating Actions in VBA 91

Using the Do.. .While Loop 91

Another Approach to the Do.. .While Loop 93

Using the Do.. .Until Loop 94

Another Approach to the Do.. .Until Loop 95

For Each.. .Next Loop 98

Exiting Loops Early 99

Nested Loops 99

Chapter Summary 101

Chapter 7 Working with Arrays 102

Declaring Arrays 104

Array Upper and Lower Bounds 105

Using Arrays in VBA Procedures 105

Arrays and Looping Statements 107

Using a Two-Dimensional Array 110

Static and Dynamic Arrays 111

Array Functions 113

The Array Function 113

The IsArray Function 113

The Erase Function 114

The LBound and UBound Functions 115

Errors in Arrays 116

Parameter Arrays 117

Chapter Summary 118

Chapter 8 Custom Collections and Class Modules 119

Terminology 119

Working with Collections 120

Declaring a Custom Collection 121

Adding Objects to a Custom Collection 121

Removing Objects from a Custom Collection 123

Creating Custom Objects 123

Creating a Class 124

Variable Declarations 124

Defining the Properties for the Class 125

Creating the Property Get Procedures 126

Creating the Property Let Procedures 127

Creating the Class Methods 128

Creating an Instance of a Class 129

Event Procedures in the Class Module 130

Creating the User Interface 130

Watching the Execution of Your VBA Procedures 139

Chapter Summary 141

Chapter 9 Debugging VBA Procedures and Handling Errors 142

Testing VBA Procedures 143

Stopping a Procedure 143

Using Breakpoints 144

Removing Breakpoints 149

Using the Immediate Window in Break Mode 149

Using the Stop Statement 150

Adding Watch Expressions 151

Removing Watch Expressions 154

Using Quick Watch 154

Using the Locals Window and the Call Stack Dialog Box 156

Stepping Through VBA Procedures 158

Stepping Over a Procedure 159

Stepping Out of a Procedure 160

Running a Procedure to Cursor 160

Setting the Next Statement 160

Showing the Next Statement 161

Stopping and Resetting VBA Procedures 161

Understanding and Using Conditional Compilation 161

Navigating with Bookmarks 163

Trapping Errors 164

Generating Errors to Test Error Handling 167

Chapter Summary 168

Part II — Creating and Manipulating Databases with ADO

Chapter 10 Accessing Data Using ADO 171

ADO Object Model 172

Establishing a Connection with the Data 173

Opening a Microsoft Jet Database in Read/Write Mode 176

Opening a Microsoft Jet Database in Read-Only Mode 177

Opening a Microsoft Jet Database Secured with a Password 178

Opening a Microsoft Jet Database with User-Level Security 179

Opening a Microsoft Excel Spreadsheet 181

Opening a dBASE File Using MSDASQL Provider 182

Opening a Text File Using ADO 184

Connecting to the Current Access Database 185

Creating a New Access Database 186

Copying a Database 187

Connecting to an SQL Server 188

Database Errors 190

Compacting a Database 192

Chapter Summary 193

Chapter 11 Creating and Accessing Tables and Fields with ADO 194

Creating a Microsoft Access Table 195

Copying a Table 198

Deleting a Database Table 199

Adding New Fields to an Existing Table 200

Removing a Field from a Table 201

Retrieving Table Properties 202

Retrieving Field Properties 203

Linking a Microsoft Access Table 204

Linking a Microsoft Excel Spreadsheet 205

Listing Database Tables 207

Changing the AutoNumber 208

Listing Tables and Fields 209

Listing Data Types 210

Chapter Summary 211

Chapter 12 Setting Up Indexes and Table Relationships with ADO 212

Creating a Primary Key 212

Creating a Single-Field Index 213

Adding a Multiple-Field Index to a Table 215

Listing Indexes in a Table 216

Deleting Table Indexes 217

Creating Table Relationships 218

Chapter Summary 220

Chapter 13 ADO Techniques for Finding and Reading Records 221

Introduction to ADO Recordsets 221

Cursor Types 222

Lock Types 224

Cursor Location 225

The Options Parameter 226

Opening a Recordset 229

Opening a Recordset Based on a Table or Query 230

Opening a Recordset Based on an SQL Statement 233

Opening a Recordset Based on Criteria 234

Opening a Recordset Directly 235

Moving Around in a Recordset 236

Finding the Record Position 236

Reading Data from a Field 237

Returning a Recordset as a String 238

Finding Records Using the Find Method 240

Finding Records Using the Seek Method 241

Finding a Record Based on Multiple Conditions 243

Using Bookmarks 244

Using Bookmarks to Filter a Recordset 246

Using the GetRows Method to Fill the Recordset 247

Chapter Summary 248

Chapter 14 Working with Records 249

Adding a New Record 249

Modifying a Record 250

Canceling Changes to the Data 251

Editing Multiple Records 251

Deleting a Record 253

Copying Records to an Excel Spreadsheet 254

Copying Records to a Word Document 256

Copying Records to a Text File 259

Filtering Records with an SQL Clause 260

Filtering Records Using the Filter Property 261

Sorting Records 262

Chapter Summary 263

Chapter 15 Creating and Running Queries with ADO 264

Creating a Select Query Manually 264

Creating a Select Query from a VBA Procedure 268

Executing a Select Query 271

Creating a Parameter Query 274

Executing a Parameter Query 275

Creating a Pass-Through Query 277

Executing a Pass-Through Query 280

Executing an Update Query 281

Modifying a Stored Query 283

Listing Queries in a Database 285

Deleting a Stored Query 285

Chapter Summary 286

Chapter 16 Using Advanced ADO Features 287

Fabricating a Recordset 287

Disconnected Recordsets 290

Saving a Recordset to Disk 292

Cloning a Recordset 305

Introduction to Data Shaping 310

Writing a Simple SHAPE statement 311

Working with Data Shaping 312

Writing a Complex SHAPE Statement 316

Shaped Recordsets with Multiple Children 317

Shaped Recordsets with Grandchildren 320

Transaction Processing 330

Creating a Simple Transaction 330

Chapter Summary 333

Chapter 17 Implementing Database Security with ADOX and JRO 334

Two Types of Security in Microsoft Access 334

Share-Level Security 334

User-Level Security 335

Understanding Workgroup Information Files 335

Creating and Joining Workgroup Information Files 337

Working with Accounts 345

Creating a Group Account 345

Creating a User Account 348

Adding a User to a New Group 349

Deleting a User Account 351

Deleting a Group Account 352

Listing All Group Accounts 353

Listing All User Accounts 354

Listing Users in Groups 355

Setting and Retrieving User and Group Permissions 356

Determining the Object Owner 356

Setting User Permissions for an Object 358

Setting User Permissions for a Database 361

Setting User Permissions for Containers 362

Checking Permissions for Objects 364

Setting a Database Password 366

Changing a User Password 367

Encrypting a Database 369

Chapter Summary 370

Chapter 18 Database Replication 371

Creating a Design Master 372

Creating a Full Replica 374

Creating a Partial Replica 375

Replicating Objects 378

Keeping Objects Local 380

Synchronizing Replicas 382

Retrieving Replica Properties 384

Synchronization Conflicts 385

Chapter Summary 391

Part III — Programming with the Jet Data Definition Language

Chapter 19 Creating, Modifying, and Deleting Tables and Fields 395

Creating Tables 397

Deleting Tables 401

Deleting Database Files 402

Modifying Tables with DDL 402

Adding New Fields to a Table 402

Changing the Data Type of a Table Column 403

Changing the Size of a Text Column 404

Deleting a Column from a Table 405

Adding a Primary Key to a Table 406

Adding a Multiple-Field Index to a Table 406

Deleting an Indexed Column 407

Deleting an Index 408

Setting a Default Value for a Table Column 409

Changing the Seed and Increment Value of AutoNumber Columns 410

Chapter Summary 412

Chapter 20 Enforcing Data Integrity and Relationships between Tables 413

Using Check Constraints 414

Establishing Relationships between Tables 418

Using the Data Definition Query Window 421

Chapter Summary 423

Chapter 21 Defining Indexes and Primary Keys 424

Creating Tables with Indexes 424

Adding an Index to an Existing Table 425

Creating a Table with a Primary Key 427

Creating Indexes with Restrictions 428

Deleting Indexes 432

Chapter Summary 432

Chapter 22 Database Security 433

Setting the Database Password 433

Removing the Database Password 434

Creating a User Account 435

Changing a User Password 436

Creating a Group Account 437

Adding Users to Groups 438

Removing a User from a Group 438

Deleting a User Account 439

Granting Permissions for an Object 440

Revoking Security Permissions 441

Deleting a Group Account 442

Chapter Summary 443

Chapter 23 Views and Stored Procedures 444

Creating a View 444

Enumerating Views 447

Deleting a View 448

Creating a Stored Procedure 448

Creating a Parameterized Stored Procedure 450

Executing a Parameterized Stored Procedure 452

Deleting a Stored Procedure 454

Changing Database Records with Stored Procedures 454

Chapter Summary 455

Part IV — Event Programming in Forms and Reports

Chapter 24 Using Form Events 459

Data Events 460

Current 460

BeforeInsert 461

AfterInsert 462

BeforeUpdate 463

AfterUpdate 464

Dirty 465

OnUndo 466

Delete 466

BeforeDelConfirm 467

AfterDelConfirm 468

Focus Events 469

Activate 469

Deactivate 469

GotFocus 470

LostFocus 470

Mouse Events 470

Click 470

DblClick 471

MouseDown 471

MouseMove 473

MouseUp 473

MouseWheel 473

Keyboard Events 473

KeyDown 473

KeyUp 474

KeyPress 475

Error Events 476

Error 476

Filter Events 478

Filter 478

ApplyFilter 479

Timing Events 480

Timer 480

PivotTable/PivotChart Events 482

Referencing the Microsoft Office Web Components Object Library 483

Data Source Events 483

OnConnect 483

OnDisconnect 484

BeforeQuery 484

Query 484

Display Events 487

BeforeScreenTip 487

AfterLayout 487

BeforeRender 488

AfterRender 490

AfterFinalRender 491

Change Events 492

DataChange 492

DataSetChange 493

PivotTableChange 493

SelectionChange 494

ViewChange 494

Command Events 495

CommandEnabled 495

CommandChecked 496

CommandBeforeExecute 497

CommandExecute 498

Keyboard and Mouse Events 498

Events Recognized by Form Sections 499

DblClick (Form Section Event) 499

Chapter Summary 500

Chapter 25 Using Report Events 501

Open 501

Close 502

Activate 503

Deactivate 504

NoData 504

Page 505

Error 506

Events Recognized by Report Sections 507

Format (Report Section Event) 507

Print (Report Section Event) 510

Retreat 513

Chapter Summary 513

Chapter 26 Events Recognized by Controls 514

Enter (Control) 515

BeforeUpdate (Control) 516

AfterUpdate (Control) 517

NotInList (Control) 519

Click (Control) 520

DblClick (Control) 525

Chapter Summary 527

Chapter 27 More about Event Programming 528

Sinking Events in Standalone Class Modules 529

Writing Event Procedure Code in Two Places 537

Responding to Control Events in a Class 537

Declaring and Raising Events 541

Chapter Summary 546

Part V — Taking Your VBA Programming Skills to the Web

Chapter 28 Access and Active Server Pages 549

Introduction to Active Server Pages 552

The ASP Object Model 555

Installing Internet Information Services (IIS) 556

Creating a Virtual Directory 557

Connecting to a Microsoft Access Database via DSN 559

Other Methods of Connecting to a Microsoft Access Database 565

Establishing a DSN-less Connection 566

Connecting to a Microsoft Access Database Using OLE DB 566

Retrieving Records 567

Breaking Up a Recordset When Retrieving Records 569

Retrieving Records with the GetRows Method 576

Database Lookup Using Drop-Down Lists 579

Database Lookup Using a Multiple Selection List Box 583

Adding Data into a Table 588

Modifying a Record 592

Deleting a Record 597

Creating a Web User Interface for Database Access 601

Chapter Summary 605

Chapter 29 XML Features in Access 2003 607

What Is XML? 607

XML Support in Access 2003 609

Exporting XML Data 610

Understanding the XML Data File 612

Understanding the XML Schema File 615

Understanding the XSL Transformation Files 617

Viewing XML Documents Formatted with Stylesheets 620

Advanced XML Export Options 620

Data Export Options 620

Schema Export Options 622

Presentation Export Options 623

Applying XSLT Transforms to Exported Data 624

Importing XML Data 629

Exporting to and Importing from XML Programmatically 634

Exporting to XML Using the ExportXML Method 635

Transforming XML Data with the TransformXML Method 643

Importing to XML Using the ImportXML Method 650

Manipulating XML Documents Programmatically 650

Loading and Retrieving the Contents of an XML File 652

Working with XML Document Nodes 653

Retrieving Information from Element Nodes 655

Retrieving Specific Information from Element Nodes 656

Retrieving the First Matching Node 657

Using ActiveX Data Objects with XML 658

Saving an ADO Recordset as XML to Disk 658

Attribute-Centric and Element-Centric XML 660

Changing the Type of an XML File 660

Applying an XSL Stylesheet 661

Transforming Attribute-Centric XML Data into an HTML Table 663

Loading an XML Document in Excel 666

Chapter Summary 668

Index 671

0 0

Post a comment