Learn Microsoft Access 2000 Programming by Example

Learn Microsoft Access 2000 Programming by Example takes a gradual approach to teaching you about VBA. Start by learning VBA structures and constructs, and familiarizing yourself with the Visual Basic environment. Then find out about two object models that Access uses-DAO and ADO. Add some SQL/DDL skills, and then determine what really happens in forms and reports with event-driven programming. Finally, learn how to operate your database from a Web browser

Learn How to:

• Use Visual Basic Editor (VBE) to write and debug your programming code
• Understand and use common VBA programming structures such as conditions, loops, arrays, and collections.
• Perform common database tasks with Data Access Objects (DAO).
• Create and manage databases with ActiveX Data Objects (ADO).
• Change the default behavior of Access by using event procedures in forms and reports.

• Source code and files used in the exercises and examples
• Trial version of VBACodePrint, a VBA add-in from Jn-Software.com
• Trial version of MindMapper 3.0, mind mapping software from SimTech Systems, Inc.

Introduction ..... xiii

Chapter 1: Introduction to Access 2000 VBA Programming ..... 1

Procedures and Modules ..... 1

Procedure Types ..... 2

Module Types ..... 2

Events, Event Properties, and Event Procedures ..... 3

Walking Through an Event Procedure ..... 4

Section Summary ..... 7

The Visual Basic Editor (VBE) ..... 7

Understanding the Project Explorer Window ..... 8

Understanding the Properties Window ..... 9

Understanding the Code Window ..... 10

Other Windows in the VBE ..... 12

On-the-Fly Syntax and Programming Assistance ..... 12

List Properties/Methods ..... 13

Parameter Info ..... 14

List Constants ..... 15

Quick Info ..... 15

Complete Word ..... 16

Indent/Outdent ..... 16

Comment Block/Uncomment Block ..... 17

Using the Object Browser ..... 18

Using the VBA Object Library ..... 20

Using the Immediate Window ..... 21

Section Summary ..... 23

Variables, Data Types, and Constants ..... 24

What are Variables? ..... 24

What are Data Types? ..... 25

Creating Variables ..... 26

Declaring Variables ..... 27

Specifying the Data Type of a Variable ..... 29

Assigning Values to Variables ..... 31

Forcing Declaration of Variables ..... 33

Understanding the Scope of Variables ..... 35

Procedure-Level (Local) Variables ..... 35

Module-Level Variables ..... 35

Lifetime of Variables ..... 37

Understanding and Using Static Variables ..... 37

Declaring and Using Object Variables ..... 39

Finding a Variable Definition ..... 40

What Type is This Variable? ..... 41

Using Constants in VBA Procedures ..... 42

Intrinsic Constants ..... 43

Section Summary ..... 45

Passing Arguments to Procedures and Functions ..... 45

Writing a Function Procedure ..... 45

Passing Arguments by Reference and by Value ..... 48

Using Optional Arguments ..... 49

Locating Built-in Functions ..... 51

Using the MsgBox Function ..... 51

Returning Values from the MsgBox Function ..... 58

Using the InputBox function ..... 59

Converting Data Types ..... 61

Using Master Procedures and Subprocedures ..... 63

Section Summary ..... 66

Decision Making with VBA ..... 66

If ...Then Statement ..... 67

Decisions Based on More Than One Condition ..... 70

Indenting If Block Instructions ..... 71

If ...Then...Else Statement ..... 71

If ...Then...ElseIf Statement ..... 73

Nested If ...Then...Statements ..... 74

Select Case Statement ..... 76

Using Is with the Case Clause ..... 78

Specifying Range of Values in a Case Clause ..... 78

Specifying Multiple Expressions in a Case Clause ..... 80

Section Summary ..... 80

Repeating Actions in Visual Basic ..... 81

Do Loops - Do ...While and Do ...Until ..... 81

For ...Next Loop ..... 85

For Each ...Next Loop ..... 86

Exiting Loops Early ..... 88

Nested Loops ..... 89

Section Summary ..... 89

Understanding Arrays ..... 90

Declaring Arrays ..... 92

Array Upper and Lower Bounds ..... 93

Using Arrays in VBA Procedures ..... 93

Arrays and Looping Statements ..... 95

Using a Two-Dimensional Array ..... 97

Static and Dynamic Arrays ..... 98

Array Functions ..... 100

The Array Function ..... 100

The IsArray Function ..... 101

The Erase Function ..... 101

The LBound and UBound Functions ..... 102

Errors in Arrays ..... 103

Parameter Arrays ..... 105

Section Summary ..... 106

Custom Collections and Class Modules ..... 106

Terminology ..... 106

Working with Collections ..... 107

Declaring a Custom Collection ..... 108

Adding Objects to a Custom Collection ..... 108

Removing Objects from a Custom Collection ..... 109

Insert: Module or Class Module? ..... 110

Creating Custom Objects ..... 110

Creating a Class ..... 111

Variable Declarations ..... 111

Defining the Properties for the Class ..... 112

Creating the Property Get Procedures ..... 113

Creating the Property Let Procedures ..... 113

Creating the Class Methods ..... 115

Creating an Instance of a Class ..... 116

Event Procedures in the Class Module ..... 116

Creating the User Interface ..... 117

Watching the Execution of your VB4 Procedures ..... 126

Section Summary ..... 128

Debugging VBA Procedures and Handling Errors ..... 129

Testing VBA Procedures ..... 130

Stopping a Procedure ..... 130

Using Breakpoints ..... 131

Using the Immediate Window in Break Mode ..... 135

Using the Stop Statement ..... 137

Adding Watch Expressions ..... 138

Using Quick Watch ..... 141

Using the Locals Window and the Call Stack Dialog Box ..... 142

Stepping Through VBA Procedures ..... 144

Stepping Through a Procedure ..... 144

Stepping Over a Procedure ..... 145

Setting the Next Statement ..... 146

Showing the Next Statement ..... 147

Stopping and Resetting VBA Procedures ..... 147

Understanding and Using Conditional Compilation ..... 147

Navigating with Bookmarks ..... 150

Trapping Errors ..... 150

Generating Errors to Test Error Handling ..... 154

Section Summary ..... 154

Chapter 2: Creating and Manipulating Databases with DAO ..... 155

Working with a Database Using DAO ..... 158

Creating a Database ..... 158

Copying a Database ..... 159

Opening and Examining an Existing Database ..... 160

Adding User-Defined Properties to a Database ..... 162

Deleting a Database ..... 165

Creating and Linking Tables with DAO ..... 166

Creating a Table and Setting Field Properties ..... 166

Adding New Fields to a Table ..... 171

Removing a Field from a Table ..... 172

Modifying Table and Field Properties ..... 173

Retrieving Table Properties ..... 174

Linking a Table to a Database ..... 175

Retrieving the Names of All the Fields in All the Tables ..... 176

Creating Indexes and Establishing Table Relationships with DAO ..... 176

Creating a Primary Key and Setting Its Properties ..... 177

Creating a Multi-Field Index ..... 179

Listing All Indexes in a Table ..... 180

Establishing Relationships Between Tables ..... 180

Creating and Running Queries with DAO ..... 184

Creating a Select Query with DAO ..... 184

Creating and Running a Parameter Query with DAO ..... 186

Creating and Running an Update Query with DAO ..... 188

Running a Delete Query with DAD ..... 189

Creating and Running a Make-Table Query with DAO ..... 190

Creating and Running an Append Query with DAD ..... 191

Creating and Running a Pass-Through Query with DAO ..... 192

Performing Other Operations with Queries ..... 194

Retrieving Query Properties ..... 194

Listing All Queries in a Database ..... 196

Sorting Data in a Query ..... 196

Deleting a Query from a Database ..... 197

Determining If a Query Can be Updated ..... 198

Finding and Reading Records ..... 198

Opening and Closing Various 'lopes of Recordsets ..... 200

Opening a Snapshot and Counting Records ..... 201

Retrieving the Contents o_ a Specific Field in a Table ..... 202

Moving Between Records in a Table ..... 203

Counting Records in a Recordset ..... 204

Finding Records in a Table-Type Recordset ..... 204

Finding Records in Dynasets or Snapshots ..... 206

Finding the nth Record in a Dynaset or Snapshot ..... 207

Working with Records ..... 208

Adding New Records ..... 208

Modifying Records ..... 210

Deleting Records ..... 211

Saving Field Values to Variables ..... 213

Saving Records to an Array ..... 213

Copying Records to a Worksheet Range ..... 216

Filtering Records ..... 217

Creating a Recordset Object from a Form ..... 219

Database Security and Transaction Processing ..... 220

Understanding Workspaces ..... 220

Creating a New User Account ..... 223

Listing All Users ..... 227

Creating a New Group Account ..... 228

Adding a User to a Group ..... 229

Listing User Accounts in Groups ..... 230

Removing Users from Groups ..... 231

User Passwords ..... 232

User and Group Permissions ..... 233

Changing the Owner of an Object ..... 235

Checking Permissions to Objects ..... 236

Checking Permissions to a Specific Object ..... 237

Assigning Specific Permissions to Groups ..... 238

Transaction Processing ..... 238

Summary ..... 242

Chapter 3: Creating and Manipulating Databases with ADO ..... 243

Accessing Data Using ADO ..... 245

Opening a Microsoft Jet Database in Read/Write Mode ..... 246

Opening a Microsoft Jet Database in Read-Only Mode Using ADO ..... 248

Opening a Microsoft Jet Database Secured with a Password ..... 249

Opening a Microsoft Jet Database with User-Level Security ..... 250

Opening a Microsoft Excel Spreadsheet Using ADO ..... 251

Opening a dBASE File Using MSDASQL Provider ..... 251

Opening a Text File Using ADO ..... 253

Connecting to the Current Access Database ..... 253

Creating a New Access Database ..... 255

Copying a Database ..... 256

Connecting to a SQL Server ..... 256

Database Errors ..... 258

Creating and Accessing Tables and Fields with ADO ..... 259

Creating a Microsoft Access Table ..... 261

Copying a Table ..... 264

Deleting a Database Table ..... 265

Adding New Fields to an Existing Table ..... 265

Removing a Field from a Table ..... 266

Retrieving Table Properties ..... 267

Retrieving Field Properties ..... 268

Linking a Microsoft Access Table ..... 268

Linking a Microsoft Excel Spreadsheet ..... 270

Listing Database Tables ..... 271

Changing the Auto Number ..... 272

Listing Tables and Fields ..... 273

Listing Data Types ..... 275

Placing an Access Table in a Microsoft Word Document ..... 276

Creating Table Indexes and Setting Up Relationships with ADO ..... 277

Creating a Primary Key ..... 277

Creating a Single-Field Index ..... 278

Adding a Multiple-Field Index to a Table ..... 280

Listing Indexes in a Table ..... 281

Deleting Table Indexes ..... 281

Creating Table Relationships ..... 282

Creating and Running Queries ..... 284

Creating a Select Query ..... 285

Creating a Parameter Query ..... 287

Creating a Pass-Through Query ..... 289

Executing a Select Query ..... 290

Executing a Parameter Query ..... 293

Executing an Update Query ..... 294

Modifying a Stored Query ..... 295

Listing Queries in a Database ..... 297

Deleting a Stored Query ..... 298

Finding and Reading Records ..... 298

Opening a Recordset ..... 299

Opening a Recordset Based on a Table or Query ..... 301

Opening a Recordset Based on a SQL Statement ..... 302

Opening a Recordset Based on Criteria ..... 303

Opening a Recordset Directly ..... 303

Moving Around in a Recordset ..... 304

Finding the Record Position ..... 305

Reading Data from a Field ..... 306

Retrieving Contents of a Record ..... 307

Finding Records Using the Find Method ..... 308

Finding Records Using the Seek Method ..... 309

Finding Records Based on Multiple Conditions ..... 311

Counting the Number of Returned Records ..... 311

Working with Records ..... 312

Adding a New Record ..... 313

Modifying a Record ..... 313

Deleting a Record ..... 314

Copying Records to an Excel Spreadsheet ..... 315

Copying Records to a Word Document ..... 317

Copying Records to a Text File ..... 318

Filtering Records with a SQL Clause ..... 319

Filtering Records Using the Filter Property ..... 320

Sorting Records ..... 321

Using Advanced Recordset Features ..... 322

Creating a Recordset from Scratch ..... 322

Using Persisted Recordsets ..... 324

Database Security with ADOX ..... 327

Creating a Group Account ..... 327

Creating a User Account ..... 328

Adding a User to a New Group ..... 329

Deleting a User Account ..... 330

Deleting a Group Account ..... 331

Listing All Group Accounts ..... 332

Listing All User Accounts ..... 333

Listing Users in Groups ..... 333

Setting and Retrieving User and Group Permissions ..... 334

Determining the Object Owner ..... 335

Setting User Permissions to an Object ..... 336

Setting User Permissions to a Database ..... 339

Setting User Permissions to Containfrs ..... 340

Checking Permissions to Objects ..... 341

Securing a Database with a Password ..... 342

Changing the Database Password ..... 343

Changing the User Password ..... 344

Transaction Processing ..... 345

Compacting a Database ..... 347

Encrypting a Database ..... 349

Database Replication ..... 349

Creating a Design Master ..... 351

Creating a Full Replica ..... 352

Creating a Partial Replica ..... 353

Replicating Objects ..... 355

Keeping Objects Local ..... 356

Synchronizing Replicas ..... 357

Retrieving Replica Properties ..... 359

Synchronization Conflicts ..... 360

Summary ..... 365

Chapter 4: Programming with the Jet Data Definition Language ..... 367

Creating and Deleting Tables ..... 368

Enforcing Data Integrity ..... 372

Establishing Relationships Between Tables ..... 376

Modifying Tables with DDL ..... 380

Adding New Fields to a Table ..... 381

Changing the Data Type of a Table's Column ..... 381

Changing the Size of a Text Column ..... 381

Deleting a Column from a Table ..... 382

Adding a Primary Key to a Table ..... 382

Adding a Multiple-Field Index to a Table ..... 383

Deleting an Indexed Column ..... 383

Deleting an Index ..... 384

Setting a Default Value for a Table Column ..... 384

Changing the Seed and Increment Value of AutoNumber Columns ..... 385

Defining Indexes and Primary Keys ..... 386

Creating Tables with Indexes ..... 386

Creating Indexes with Restrictions ..... 389

Deleting Indexes ..... 391

Database Security: Passwords and User and Group Accounts ..... 392

Setting the Database Password ..... 392

Removing the Database Password ..... 393

Creating a User Account ..... 394

Changing a User Password ..... 395

Creating a Group Account ..... 395

Adding Users to Groups ..... 395

Removing a User from a Group ..... 396

Deleting a User Account ..... 396

Granting Permissions for an Object ..... 396

Revoking Security Permissions ..... 397

Deleting a Group Account ..... 398

Views and Stored Procedures ..... 399

Creating a View ..... 399

Enumerating Views ..... 401

Deleting a View ..... 402

Creating a Parameterized Stored Procedure ..... 402

Executing a Parameterized Stored Procedure ..... 403

Deleting a Stored Procedure ..... 404

Summary ..... 405

Chapter 5: Event Programming in Forms and Reports ..... 07

Form Events ..... 408

Data Events ..... 408

Window Events ..... 413

Focus Events ..... 416

Mouse Events ..... 418

Keyboard Events ..... 419

Error Events ..... 422

Filter Events ..... 422

Timing Events ..... 424

Events Recognized by Form Sections ..... 425

Report Events ..... 427

Events Recognized by Report Sections ..... 431

Events Recognized by Controls ..... 435

BeforeUpdate ..... 436

AfterUpdate ..... 437

NotInList ..... 438

Enter ..... 438

Click ..... 439

DblClick ..... 442

Form Events in the Asset Management Application ..... 444

Summary ..... 448

Chapter 6: Taking Your VBA Programming Skills to the Web ..... 449

Introduction to Active Server Pages ..... 450

Installing Internet Information Services (IIS) or Personal Web Server ..... 453

Creating a Virtual Directory ..... 454

Connecting to a Microsoft Access Database via DSN ..... 457

Establishing a DSN-less Connection to a Microsoft Access Database ..... 462

Connecting to a Microsoft Access Database Using OLE DB ..... 463

Retrieving Records ..... 463

Breaking Up a Recordset ..... 465

Limiting the Number of Returned Records ..... 472

Retrieving Records with the GetRows Method ..... 473

Database Lookup Using Drop-Down Lists ..... 476

Database Lookup Using a Multiple Selection List Box ..... 480

Adding Data into a Table ..... 485

Modifying a Record ..... 489

Deleting a Record ..... 494

Creating a Web User Interface for Database Access ..... 498

Summary ..... 502

Index ..... 503