| Introduction | 1 |
Part I | Database Design Unleashed | 5 |
1 | What's New for Developers in Access 2002 | 7 |
| User Interface Changes | 8 |
| Changes to the VBE | 9 |
| Compatibility with Access 2000 Databases | 10 |
| Database Conversion Error Logging | 11 |
| Offline Data Access Pages | 11 |
| Improved Integration with SQL Server 2000 | 12 |
| Other Access 2002 Features | 13 |
| Summary | 14 |
2 | Planning the Development Process | 15 |
| Gathering Requirements | 17 |
| Architecture | 24 |
| Development Planning | 24 |
| Construction | 28 |
| Summary | 43 |
3 | Database Design and Normalization | 45 |
| The Relational Design Theory | 48 |
| Summary | 58 |
4 | Advanced Queries | 59 |
| Using the Query Object | 60 |
| Creating Queries in Access | 62 |
| Creating Advanced Queries | 74 |
| Mastering Totals Queries | 75 |
| Mastering Crosstab Queries | 83 |
| Mastering Parameter Queries | 87 |
| Mastering Pass-Through Queries | 91 |
| Mastering Data Definition Queries | 93 |
| Summary | 97 |
5 | The Microsoft Jet Database Engine | 99 |
| The History of Microsoft Jet | 100 |
| Using Jet 4.0 Features in Your Applications | 103 |
| Summary | 116 |
Part II | Data Access | 117 |
6 | Introduction to ActiveX Data Objects | 119 |
| History of Data Access | 120 |
| Microsoft's Universal Data Access Initiative | 121 |
| ActiveX Data Objects (ADO) | 122 |
| The ADO Object Model | 125 |
| Moving from DAO to ADO | 142 |
| The ADO Object Model Compared to DAO | 145 |
| Summary | 146 |
7 | Advanced ADO | 147 |
| Using the OLE DB Provider for Jet in Access 2002 | 148 |
| Accessing Non-Relational Data with ADO | 157 |
| Advanced Data Manipulation with ADO | 162 |
| Data Definition with ADOX | 167 |
| Summary | 176 |
Part III | User Interfaces Unleashed | 177 |
8 | Advanced Form Design | 179 |
| Form Properties | 180 |
| Access Form Controls | 189 |
| Creating PivotTable and PivotChart Views | 197 |
| Summary | 202 |
9 | Enhancing Forms with ActiveX Controls | 203 |
| How to Use ActiveX Controls | 204 |
| Using ActiveX Controls | 206 |
| 21 ActiveX Controls | 210 |
| Distributing ActiveX Controls | 238 |
| Summary | 239 |
10 | Reporting Unleashed | 241 |
| Approaching Reports | 242 |
| Understanding the Architecture of Access Reports | 242 |
| Building a Single Table Report Using the Report Wizard | 244 |
| Customizing Reports | 247 |
| Working with Subreports | 256 |
| Creating Simple Mailing Labels | 259 |
| Publishing a Report | 262 |
| Modifying a Report at Runtime | 266 |
| Building Reports Programmatically | 274 |
| Tips and Tricks | 279 |
| Summary | 284 |
Part IV | VBA Unleashed | 285 |
11 | Creating Objects with Class Modules | 287 |
| Exploring the Benefits of Using Objects | 289 |
| Reviewing Objects, Properties, and Methods | 291 |
| Creating Classes | 292 |
| Creating Properties | 292 |
| Creating Methods | 299 |
| Creating Events | 300 |
| Using Objects | 302 |
| Creating Multiple Instances of an Object | 304 |
| Examining More Object Examples | 304 |
| Implementing an Error Handler Object | 311 |
| Using Objects with VBA Collections | 312 |
| Summary | 316 |
12 | Debugging Access Applications | 317 |
| Eliminating Logic Errors | 318 |
| Working with the Visual Basic Development Environment (IDE) | 318 |
| Working with the Debug Object | 322 |
| Using the Immediate Window | 324 |
| Using the Debugger | 326 |
| Using Conditional Compilation | 331 |
| Application Testing | 335 |
| Practice Debugging Techniques | 335 |
| Summary | 336 |
13 | Professional Error Handling | 337 |
| Eliminating Syntax Errors | 338 |
| Eliminating Logic Errors | 341 |
| Eliminating Runtime Errors | 341 |
| Errors in Various Applications | 364 |
| Error Handling With Nested Procedures | 365 |
| Advanced Error Topics | 365 |
| Summary | 368 |
14 | Application Optimization | 369 |
| Strengthening the Foundation: Hardware and Windows Optimization | 371 |
| Installing the Application for Optimal Performance | 373 |
| Optimizing the Configuration of the Jet Database Engine | 374 |
| Tools to Measure Performance | 381 |
| Looking Behind the Scenes | 383 |
| Optimizing the Database from the Start | 384 |
| Boosting Query Performance | 387 |
| Getting Forms to Run Faster | 395 |
| Writing Fast Code | 400 |
| Coding Tips and Hints | 402 |
| Summary | 410 |
Part V | Access Client Server | 411 |
15 | Introducing Access Data Projects and the Visual Tools | 413 |
| Introducing Access Data Projects | 414 |
| Using ADPs | 415 |
| Working with ADPs and Existing SQL Server Databases | 419 |
| Creating a Project Based on a New Database | 428 |
| Summary | 434 |
16 | Developing Access Front-Ends to Microsoft SQL Server | 435 |
| Client/Server Architecture: OLE DB Versus ODBC | 436 |
| Setting Up Your SQL Server Front-End Connection | 436 |
| Stored Procedures and SQL Pass-Through Queries | 440 |
| Reporting Against SQL Server in Access 2002 | 443 |
| Using Forms in Your Application | 448 |
| Advanced Features of the SQL Server OLE DB Provider | 450 |
| Executing Commands with Parameters | 452 |
| Using a Connection Class | 459 |
| Summary | 460 |
17 | Access 2002 Front-Ends to Oracle | 461 |
| Accessing Oracle Data with Access | 462 |
| Functions in Oracle Versus Access | 470 |
| Understanding Views and Stored Procedures | 482 |
| Creating an Unbound Interface to Oracle | 491 |
| Summary | 502 |
Part VI | Interoperability | 503 |
18 | Using ActiveX Automation | 505 |
| What Is ActiveX Automation? | 506 |
| Why Use Automation? | 506 |
| Distinguishing Automation Server Versus Automation Client | 506 |
| Determining Automation Resource Requirements | 507 |
| Understanding the Big Picture | 507 |
| Creating and Setting a Reference to Another Application | 507 |
| Assigning an Object Variable to an Application | 512 |
| Creating an Instance of the Application | 513 |
| Using the Automation Object's Properties and Methods | 517 |
| Releasing the Automation Object | 517 |
| Putting It All Together | 518 |
| Closing the Automation Server Application | 519 |
| Using the UserControl Property to Determine How an Application Was Opened | 520 |
| Using WithEvents to Expose Events of the Automation Server | 520 |
| Using the Automation Tips and Techniques | 523 |
| Summary | 527 |
19 | Integrating with Microsoft Office | 529 |
| Why Integrate with Microsoft Office? | 531 |
| Use the Right Tool | 533 |
| Using the Macro Recorder to Write Code | 533 |
| Using Auto Macros | 535 |
| Microsoft Forms | 536 |
| Object Browser | 537 |
| Class Arguments for Office Applications | 537 |
| Automation Example | 538 |
| Automating Word | 540 |
| Automating Excel | 555 |
| Automating PowerPoint | 559 |
| Automating Outlook | 562 |
| Automating Graph | 567 |
| Summary | 569 |
20 | Using Visual Basic with Access | 571 |
| Creating ActiveX Code Components | 572 |
| Creating ActiveX Controls | 593 |
| Summary | 614 |
Part VII | Multiuser Issues | 615 |
21 | Multiple Users and Database Locking | 617 |
| Understanding Multiuser Issues | 618 |
| Reviewing Jet's Multiuser Design | 619 |
| Understanding Jet's Multiuser Locking | 619 |
| Selecting the Proper Architecture | 623 |
| Working With Locks | 623 |
| Optimizing Multiuser Applications | 631 |
| Oracle/SQL Server Locking | 632 |
| Summary | 633 |
22 | Security | 635 |
| Elements of Security | 636 |
| Workgroup Creation | 638 |
| Users and Groups | 641 |
| Implementing Security Using Startup Options | 649 |
| Security Concerns with Replication | 650 |
| Security for Split Databases | 651 |
| Security for Client/Server | 653 |
| Securing a Database Step by Step | 663 |
| Common Security Errors | 664 |
| Summary | 665 |
Part VIII | Web Publishing with Access 2002 | 667 |
23 | Configuring a Web Site for Web Publishing | 669 |
| Development Versus Production Environment | 670 |
| Choosing Your Platform | 671 |
| What Is the Option Pack? | 673 |
| Setting Up Your Web Server | 675 |
| Managing and Configuring Your Web Server | 682 |
| Securing Your Web Applications | 692 |
| What Is the Difference Between a Site and a Virtual Directory? | 693 |
| Summary | 699 |
24 | Web Enabling Access 2002 with Office Web Components | 701 |
| What Are the Office XP Web Components? | 702 |
| Using the Office Spreadsheet Control | 703 |
| Using the Office Chart Control | 707 |
| Using the Office PivotTable Control | 711 |
| Summary | 713 |
25 | Using Data Access Pages | 715 |
| Creating Your First Data Access Page | 717 |
| Implementing Interactive Drill-Downs | 722 |
| Incorporating the Office Web Components with DAPs | 724 |
| Scripting Data Access Pages | 726 |
| Summary | 729 |
26 | Web Publishing with Access 2002 and Active Server Pages | 731 |
| Using Active Server Pages | 732 |
| Understanding Active Server Pages | 733 |
| Getting Started with Active Server Pages | 734 |
| Active Server Pages 101 | 739 |
| Real-Life Example: Establishing a Self-Maintaining Membership-Based Web Page | 754 |
| Access 2002 Web Publishing with XML | 763 |
| Creating Charts with the Chart Control | 770 |
| Summary | 772 |
| Index | 773 |