Contents

Introduction xv

Chapter 1: Introduction to Access 2003 VBA_1

What Is Access 2003 VBA? 1

What's New in Access 2003 VBA? 2

Access 2003 VBA Programming 101 2

Requirements Gathering Phase 2

Design Phase 3

Development Phase 17

Testing Phase 22

Implementation Phase 23

Summary 24

Exercises 25

Chapter 2: The Basics of Writing and Testing VBA Code_27

Creating Modules 27

Standard Modules versus Class Modules 29

Creating and Calling Procedures 33

Sub versus Function Procedure 33

Upgrading Macros to Procedures 46

Using Variables and Constants to Store Values 47

Types of Variables 47

Declaring and Using Variables 49

Constants 52

Scope and Lifetime of Variables and Constants 52

Naming Conventions 53

Controlling Program Flow 54

Decision Making 54

Loops 59

Documenting Your Code 62

Error Debugging 62

Types of Errors 63

Debugging Your Code 65

Error Handling 69

Default Error Messages 70

Handling Errors with an On Error Statement 70

The Err Object 72

Using the Errors Collection 73

Creating a Generic Error Handler 73

Summary 76

Exercises 76

Chapter 3: Programming Applications Using Objects_77

Elements of an Object 77

What Is a Property? 78

What Is a Method? 80

What Is an Event? 81

Viewing Objects Using the Object Browser 89

Ways to Refer to Objects 90

Using Access Collections and Objects 91

The Forms Collection and Form Object 92

The Reports Collection and Report Object 94

The CurrentProject Object 94

The DoCmd Object 95

The Screen Object 95

The Printers Collection and Printer Object 96

Other Objects 97

Summary 98

Exercises 98

Chapter 4: Creating Your Own Objects_99

Using Class Modules to Create Your Own Objects 99

Class Diagrams 100

Creating Properties 103

Creating Methods 109

Creating Events 111

Using the Class 112

Advanced Techniques 118

Create Custom Properties for Existing Objects 121

Using Existing Tag Property 121

Using Public Variables 121

Using Property Let, Property Get, and Property Set Routines 121

Create Custom Methods for Existing Objects 122

Using Public Sub Procedures and Functions 122

Summary 123

Exercises 124

Chapter 5: Interacting with Data Using ADO and SQL_125

Introduction to Data Access 125

Using ADODB to Work with Data 126

The ADO Object Model 126

The Connection Object 126

The Command Object 128

The Recordset Object 128

Introduction to SQL 155

Retrieving Data Using SQL Select Statements 155

Inserting Data Using SQL Insert Statements 160

Updating Data Using SQL Statements 160

Deleting Data Using SQL Statements 161

Using ADOX to Manipulate Data Objects 172

Creating a Table with ADOX 173

Managing Security with ADOX 174

Summary 176

Exercises 176

Chapter 6: Building Interactive Forms_172

Form Navigation and Flow 177

Working with Controls 183

Combo Boxes versus List Boxes 184

Tab Controls and Subforms 188

Building User Interaction 189

The MsgBox Function 190

Validating User Input 190

Adding Polish to Your Forms 191

Summary 192

Exercises 192

Chapter 7: Importing, Linking, and Exporting Using External Data Sources 193

Linking, Importing, and Exporting Overview 193

Access and Other Databases 194

Transferring Complete SQL Server Database 197

Spreadsheets 197

Text Files 198

XML Files 201

E-mails and Outlook 202

Other Ways to Export Data 204

Using Data from Web Services 204

Summary 210

Exercises 210

Chapter 8: Creating Reports and Web-Enabled Output_211

Working with Reports from VBA 211

Setting the Report Data Source 211

Creating Reports Programmatically 213

Exporting Access Data to Web Formats 223

Creating Static HTML Pages 223

Creating ASP Pages 225

Creating Data Access Pages 227

Summary 232

Exercises 232

Chapter 9: Building SQL Server Applications with Access Projects_233

Access Projects—Using Access as a Front End to SQL server 233

Setting Up SQL Server 234

SQL Server Versions 235

Obtaining and Installing SQL Server Desktop Engine 236

Creating Access Projects (ADPs) 239

Using an Existing SQL Server Database 239

Using a New SQL Server Database 240

Working with SQL Server Objects from Access 243

SQL Server Tables 243

SQL Server Stored Procedures 246

SQL Server Views 248

SQL Server Functions 252

Migrating an Existing Access Database to an Access Project 254

Changes to an Existing Database 254

Using the Upsizing Wizard 255

Summary 261

Exercises 264

Chapter 10: Advanced Access Programming_265

Using Dynamic Link Libraries (DLLs) 265

Standard DLLs 265

Using External DLLs 269

Automation 272

Working with Automation Objects 272

Controlling Microsoft Excel 273

Controlling Microsoft Word 276

Controlling Microsoft Outlook 279

ActiveX Controls 282

Libraries 285

Add-ins 289

Transactions 289

Summary 290

Exercises 291

Chapter 11: Finishing the Application_293

Multi-User Considerations 293

Record Locking and Update Conflicts 294

Multi-User Architecture Considerations 296

Optimizing Your Applications 299

Improving Actual Performance 300

Improving Perceived Performance 302

Running the Performance Analyzer 303

Securing Your Application 305

Adding a Database Password 305

Adding a Password for VBA Code 306

Adding Users, Groups, and Permissions 306

Encrypting a Database 307

Distributing Your Application 308

Distributing the Application to Users with the Full Version of Access 308

Distributing the Application with the Access Runtime 308

Distributing an Execute-Only Application 309

Maintaining the Application 309

Compacting and Repairing the Database 309

Making Backup Copies of the Database 309

Summary 310

Exercises 310

Chapter 12: Case Study 1: Project Tracker Application_311

Design Specifications 311

Building the Database 317

Building the User Interface 320

Building the Class Modules for the Objects 330

The Project Class 331

The Contact Class 345

Building the Standard Modules 356

Connecting the User Interface to the Code 382

The Projects Form 382

The Contacts Form 411

Touring the Finished Project Tracker Application 422

Summary 428

Chapter 13: Case Study 2: Customer Service Application_429

Design Specifications 429

Building the Database and Database Objects 432

Building the User Interface 443

Building the Class Module for the Objects 448

Building the Standard Modules 455

Connecting the User Interface to the Code 467

The Customer Search Form 468

The View/Manage Customer Accounts Form 473

Touring the Finished Customer Service Application 481

Summary 487

Appendix A: Exercise Answers 489

Index 495

0 0

Post a comment