| | Copyright |
| | About the Author |
| | Acknowledgments |
| | Tell Us What You Think! |
| | Introduction |
|
| | Conventions Used In This Book |
|
| | Part I.
The Basics of Access Development |
|
| |
Chapter 1.
Access As a Development Tool |
|
| | Why This Chapter Is Important |
|
| | What Types of Applications Can You Develop in Access? |
|
| | Access As a Scalable Product |
|
| | What Exactly Is a Database? |
|
| | Getting to Know the Database Objects |
|
| | Object Naming Conventions |
|
| | Hardware Requirements |
|
| | How Do I Get Started Developing an Access Application? |
|
| | What''s New in Access 2002 and 2003? |
|
| | Practical Examples: The Application Design for a Computer Consulting Firm |
|
| | Summary |
|
|
| |
Chapter 2.
What Every Developer Needs to Know About Databases and Tables |
|
| | Why This Chapter Is Important |
|
| | Creating a New Database |
|
| | Building a New Table |
|
| | Selecting the Appropriate Field Type for Your Data |
|
| | Working with Field Properties |
|
| | The All-Important Primary Key |
|
| | Working with the Lookup Feature |
|
| | Working with Table Properties |
|
| | Using Indexes to Improve Performance |
|
| | Access Tables and the Internet |
|
| | Adding Smart Tags to Your Tables |
|
| | Creating a Pivot Table or Pivot Chart from a Table |
|
| | Viewing Object Dependencies |
|
| | Database Specifications and Limitations |
|
| | Table Specifications and Limitations |
|
| | Practical Examples: Designing the Tables Needed for a Computer Consulting Firm''s Time and Billing Application |
|
| | Summary |
|
|
| |
Chapter 3.
Relationships: Your Key to Data Integrity |
|
| | Why This Chapter Is Important |
|
| | Introduction to Relational Database Design |
|
| | Establishing Relationships in Access |
|
| | Establishing Referential Integrity |
|
| | Looking at the Benefits of Relationships |
|
| | Examining Indexes and Relationships |
|
| | Practical Examples: Establishing the Relationships Between the Tables Included in the Time and Billing Database |
|
| | Summary |
|
|
| |
Chapter 4.
What Every Developer Needs to Know About Query Basics |
|
| | Why This Chapter Is Important |
|
| | What Is a Query, and When Should You Use One? |
|
| | Everything You Need to Know About Query Basics |
|
| | Ordering Your Query Result |
|
| | Refining Your Query with Criteria |
|
| | Working with Dates in Criteria |
|
| | Understanding How You Can Update Query Results |
|
| | Building Queries Based on Multiple Tables |
|
| | Creating Calculated Fields |
|
| | Getting Help from the Expression Builder |
|
| | Summarizing Data with Totals Queries |
|
| | Excluding Fields from the Output |
|
| | Nulls and Query Results |
|
| | Refining Your Queries with Field, Field List, and Query Properties |
|
| | Building Parameter Queries When You Don''t Know the Criteria at Design Time |
|
| | Adding Smart Tags to Your Queries |
|
| | Creating a Pivot Table or Pivot Chart from a Query |
|
| | Query Specifications and Limitations |
|
| | Practical Examples: Building Queries Needed by the Time and Billing Application for a Computer Consulting Firm |
|
| | Summary |
|
|
| |
Chapter 5.
What Every Developer Needs to Know About Forms |
|
| | Why This Chapter Is Important |
|
| | Uses of Forms |
|
| | Anatomy of a Form |
|
| | Creating a New Form |
|
| | Working with the Form Design Window |
|
| | Selecting the Correct Control for the Job |
|
| | Control Morphing |
|
| | Conditional Formatting |
|
| | What Form Properties Are Available, and Why Should You Use Them? |
|
| | What Control Properties Are Available, and Why Should You Use Them? |
|
| | Bound, Unbound, and Calculated Controls |
|
| | Using Expressions to Enhance Your Forms |
|
| | The Command Button Wizards: Programming Without Typing |
|
| | Building Forms Based on More Than One Table |
|
| | Basing Forms on Queries: The Why and How |
|
| | Access Forms and the Internet |
|
| | Adding Smart Tags to Your Forms |
|
| | Creating a Pivot Table or Pivot Chart from a Form |
|
| | Form Specifications and Limitations |
|
| | Practical Examples: Designing Forms for Your Application |
|
| | Summary |
|
|
| |
Chapter 6.
What Every Developer Needs to Know About Reports |
|
| | Why This Chapter Is Important |
|
| | Types of Reports Available |
|
| | Anatomy of a Report |
|
| | Creating a New Report |
|
| | Working with the Report Design Window |
|
| | Selecting the Correct Control for the Job |
|
| | What Report Properties Are Available and Why Use Them? |
|
| | What Control Properties Are Available and Why Use Them? |
|
| | Inserting Page Breaks |
|
| | Unbound, Bound, and Calculated Controls |
|
| | Using Expressions to Enhance Your Reports |
|
| | Building Reports Based on More Than One Table |
|
| | Working with Sorting and Grouping |
|
| | Improving Performance and Reusability by Basing Reports on Stored Queries or Embedded SQL Statements |
|
| | Access Reports and the Internet |
|
| | Report Specifications and Limitations |
|
| | Practical Examples: Building Reports Needed for Your Application |
|
| | Summary |
|
|
| |
Chapter 7.
VBA: An Introduction |
|
| | Why This Chapter Is Important |
|
| | VBA Explained |
|
| | What Are Access Class Modules, Standard Modules, Form Modules, and Report Modules? |
|
| | Working with Variables |
|
| | Adding Comments to Your Code |
|
| | Using the Line Continuation Character |
|
| | Using the VBA Control Structures |
|
| | Passing Parameters and Returning Values |
|
| | Executing Procedures from the Module Window |
|
| | The DoCmd Object: Performing Macro Actions |
|
| | Working with Built-In Functions |
|
| | Working with Constants |
|
| | Tools for Working in the Visual Basic Editor |
|
| | Customizing the VBE |
|
| | Practical Examples: Event Routines, User-Defined Functions, and Subroutines |
|
| | Summary |
|
|
| |
Chapter 8.
Objects, Properties, Methods, and Events Explained |
|
| | Why This Chapter Is Important |
|
| | Understanding Objects, Properties, Events, and Methods |
|
| | Using the Object Browser to Learn About Access''s Objects |
|
| | Referring to Objects |
|
| | Properties and Methods Made Easy |
|
| | Declaring and Assigning Object Variables |
|
| | Understanding the Differences Between Objects and Collections |
|
| | Passing Objects to Subroutines and Functions |
|
| | Determining the Type of a Control |
|
| | Special Properties That Refer to Objects |
|
| | Understanding Access''s Object Model |
|
| | Properties Introduced with Access 2002 and Access 2003 |
|
| | Practical Examples: Working with Objects |
|
| | Summary |
|
|
| |
Chapter 9.
Advanced Form Techniques |
|
| | Why This Chapter Is Important |
|
| | What Are the Form Events, and When Do You Use Them? |
|
| | What Are the Section and Control Events, and When Do You Use Them? |
|
| | Referring to Me |
|
| | What Types of Forms Can I Create, and When Are They Appropriate? |
|
| | Using Built-In Dialog Boxes |
|
| | Adding Custom Menus, Toolbars, and Shortcut Menus to Your Forms |
|
| | Taking Advantage of Built-In, Form-Filtering Features |
|
| | Including Objects from Other Applications: Linking Versus Embedding |
|
| | OpenArgs |
|
| | Switching a Form''s RecordSource |
|
| | Power Combo Box and List Box Techniques |
|
| | Power Subform Techniques |
|
| | Automatic Error Checking |
|
| | Viewing Object Dependencies |
|
| | AutoCorrect Options |
|
| | Propagating Field Properties |
|
| | Windows XP Theme Support |
|
| | Synchronizing a Form with Its Underlying Recordset |
|
| | Creating Custom Properties and Methods |
|
| | Practical Examples: Applying Advanced Techniques to Your Application |
|
| | Summary |
|
|
| |
Chapter 10.
Advanced Report Techniques |
|
| | Why This Chapter Is Important |
|
| | Events Available for Reports, and When to Use Them |
|
| | Order of Events for Reports |
|
| | Events Available for Report Sections, and When to Use Them |
|
| | Programmatically Manipulating Report Sections |
|
| | Special Report Properties |
|
| | Controlling the Printer |
|
| | Viewing Object Dependencies |
|
| | Automatic Error Checking |
|
| | Propagating Field Properties |
|
| | Practical Applications of Report Events and Properties |
|
| | Practical Examples: Practicing What You Learned |
|
| | Summary |
|
|
| |
Chapter 11.
Advanced Query Techniques |
|
| | Why This Chapter Is Important |
|
| | Action Queries |
|
| | Special Query Properties |
|
| | Optimizing Queries |
|
| | Crosstab Queries |
|
| | Outer Joins |
|
| | Self Joins |
|
| | Understanding SQL |
|
| | Union Queries |
|
| | Pass-Through Queries |
|
| | The Propagation of Nulls and Query Results |
|
| | Subqueries |
|
| | Using SQL to Update Data |
|
| | Using SQL for Data Definition |
|
| | Using the Result of a Function as the Criteria for a Query |
|
| | Passing Parameter Query Values from a Form |
|
| | Jet 4.0 ANSI-92 Extensions |
|
| | Practical Examples: Applying These Techniques in Your Application |
|
| | Summary |
|
|
| |
Chapter 12.
Advanced VBA Techniques |
|
| | Why This Chapter Is Important |
|
| | What Are User-Defined Types, and Why Would You Use Them? |
|
| | Working with Constants |
|
| | Working with Arrays |
|
| | Advanced Function Techniques |
|
| | Working with Empty and Null |
|
| | Creating and Working with Custom Collections |
|
| | Low-Level File Handling |
|
| | Understanding and Effectively Using Compilation Options |
|
| | Importing and Exporting Code Modules |
|
| | Working with Project Properties |
|
| | Practical Examples: Putting Advanced Techniques to Use |
|
| | Summary |
|
|
| |
Chapter 13.
Exploiting the Power of Class Modules |
|
| | Why This Chapter Is Important |
|
| | Object OrientationAn Introduction |
|
| | Creating and Using a Class Module |
|
| | Setting Values with Property Set |
|
| | Creating Multiple Class Instances |
|
| | The Initialize and Terminate Events |
|
| | Working with Enumerated Types |
|
| | Building Hierarchies of Classes |
|
| | Adding a Parent Property to Classes |
|
| | The Implements Keyword |
|
| | Working with Custom Collections |
|
| | Adding Your Own Events |
|
| | Practical Examples: Class Modules |
|
| | Summary |
|
|
| |
Chapter 14.
What Are ActiveX Data Objects and Data Access Objects, and Why Are They Important? |
|
| | Why This Chapter Is Important |
|
| | Using ADO Versus DAO |
|
| | Examining the ADO Model |
|
| | Understanding ADO Recordset Types |
|
| | Working with ADO Recordset Properties and Methods |
|
| | Modifying Table Data Using ADO Code |
|
| | Creating and Modifying Database Objects Using ADO Code |
|
| | Examining the DAO Model |
|
| | Getting to Know DBEngine |
|
| | Using CurrentDB() |
|
| | Understanding DAO Recordset Types |
|
| | Selecting Among the Available Types of DAO Recordset Objects |
|
| | Working with DAO Recordset Properties and Methods |
|
| | Modifying Table Data Using DAO Code |
|
| | Creating and Modifying Database Objects Using DAO Code |
|
| | Using the DAO Containers Collection |
|
| | Practical Examples: Applying These Techniques to Your Application |
|
| | Summary |
|
|
| | Part II.
What to Do When Things Don''t Go as Planned |
|
| |
Chapter 15.
Debugging: Your Key to Successful Development |
|
| | Why This Chapter Is Important |
|
| | Avoiding Bugs |
|
| | Harnessing the Power of the Immediate Window |
|
| | Invoking the Debugger |
|
| | Using Breakpoints to Troubleshoot |
|
| | Stepping Through Code |
|
| | Setting the Next Statement to Execute |
|
| | Using the Call Stack Window |
|
| | Working with the Locals Window |
|
| | Working with Watch Expressions |
|
| | Continuing Execution After a Runtime Error |
|
| | Looking At Gotchas with the Immediate Window |
|
| | Using Assertions |
|
| | Debugging Tips |
|
| | Practical Examples: Debugging Real Applications |
|
| | Summary |
|
|
| |
Chapter 16.
Error Handling: Preparing for the Inevitable |
|
| | Why This Chapter Is Important |
|
| | Implementing Error Handling |
|
| | Using On Error Statements |
|
| | Using Resume Statements |
|
| | Clearing an Error |
|
| | Examining the Cascading Error Effect |
|
| | Using the Err Object |
|
| | Raising an Error |
|
| | Using the Errors Collection |
|
| | Creating a Generic Error Handler |
|
| | Preventing Your Own Error Handling from Being Invoked |
|
| | Creating a Call Stack |
|
| | Building a Custom Error Handler Class |
|
| | Working with Error Events |
|
| | Creating a List of Error Codes and Descriptions |
|
| | Practical Examples: Incorporating Error Handling |
|
| | Summary |
|
|
| |
Chapter 17.
Optimizing Your Application |
|
| | Why This Chapter Is Important |
|
| | Introducing Optimization |
|
| | Modifying Hardware and Software Configurations |
|
| | Features Built Into Jet 3.5 |
|
| | Understanding What Jet 4.0 Does to Improve Performance |
|
| | Letting the Performance Analyzer Determine Problem Areas |
|
| | Designing Tables to Optimize Performance |
|
| | Optimizing the Performance of Your Queries |
|
| | Making Coding Changes to Improve Performance |
|
| | Designing Forms and Reports to Improve Performance |
|
| | Practical Examples: Improving the Performance of Your Applications |
|
| | Summary |
|
|
| | Part III.
Developing Multiuser and Enterprise Applications |
|
| |
Chapter 18.
A Strategy to Developing Access Applications |
|
| | Why This Chapter Is Important |
|
| | Splitting Databases into Tables and Other Objects |
|
| | Basing Forms and Reports on Queries or Embedded SQL Statements |
|
| | Understanding the Access Runtime Engine |
|
| | Using an EXE Versus Access Database: What It Means to You |
|
| | Understanding the Importance of Securing Your Database |
|
| | Using Access as a Front End |
|
| | Practical Examples: Applying the Strategy to the Computer Consulting Firm Application |
|
| | Summary |
|
|
| |
Chapter 19.
Using External Data |
|
| | Why This Chapter Is Important |
|
| | Importing, Linking, and Opening Files: When and Why |
|
| | Importing External Data |
|
| | Creating a Link to External Data |
|
| | Opening an External Table |
|
| | Understanding Windows Registry Settings |
|
| | Using the Jet OLEDB:Link Provider String |
|
| | Working with Passwords |
|
| | Refreshing and Removing Links |
|
| | Sharing Data with SharePoint Team Services |
|
| | Looking at Special Considerations |
|
| | Troubleshooting |
|
| | Looking at Performance Considerations and Links |
|
| | Working with HTML Documents |
|
| | Practical Examples: Working with External Data from Within Your Application |
|
| | Summary |
|
|
| |
Chapter 20.
Developing Multiuser and Enterprise Applications |
|
| | Why This Chapter Is Important |
|
| | Designing Your Application with Multiuser Issues in Mind |
|
| | Understanding Access''s Locking Mechanisms |
|
| | Understanding the Client/Server Model |
|
| | Deciding Whether to Use the Client/Server Model |
|
| | The Roles Access Plays in the Application Design Model |
|
| | Learning the Client/Server Buzzwords |
|
| | Upsizing: What to Worry About |
|
| | Proactively Preparing for Upsizing |
|
| | An Introduction to Transaction Processing |
|
| | Understanding the Benefits of Transaction Processing |
|
| | Modifying the Default Behavior of Transaction Processing |
|
| | Implementing Explicit Transaction Processing |
|
| | Introduction to Replication |
|
| | Uses of Replication |
|
| | Understanding When Replication Isn''t Appropriate |
|
| | Understanding the Implementation of Replication |
|
| | Practical Examples: Getting Your Application Ready for an Enterprise Environment |
|
| | Summary |
|
|
| | Part IV.
Black-Belt Programming |
|
| |
Chapter 21.
Using ActiveX Controls |
|
| | Why This Chapter Is Important |
|
| | Incorporating ActiveX Controls in Access 2003 |
|
| | Setting Properties of an ActiveX Control at Design Time |
|
| | Coding Events of an ActiveX Control |
|
| | Using the Calendar Control |
|
| | Using the UpDown Control |
|
| | Using the StatusBar Control |
|
| | Using the Common Dialog Control |
|
| | Using the Rich Textbox Control |
|
| | Using the TabStrip Control |
|
| | Using the ImageList Control |
|
| | Licensing and Distribution Issues |
|
| | Practical Examples: Implementing ActiveX Controls |
|
| | Summary |
|
|
| |
Chapter 22.
Automation: Communicating with Other Applications |
|
| | Why This Chapter Is Important |
|
| | Defining Some Automation Terms |
|
| | Declaring an Object Variable to Reference Your Application |
|
| | Creating an Automation Object |
|
| | Manipulating an Automation Object |
|
| | Early Binding Versus Late Binding |
|
| | Controlling Excel from Access |
|
| | Closing an Excel Automation Object |
|
| | Creating a Graph from Access |
|
| | Controlling Word from Access |
|
| | Controlling PowerPoint from Access |
|
| | Automating Outlook from Access |
|
| | Controlling Access from Other Applications |
|
| | Practical Examples: Using Automation to Extend the Functionality of Your Applications |
|
| | Summary |
|
|
| |
Chapter 23.
Exploiting the Power of the Windows API |
|
| | Why This Chapter Is Important |
|
| | Declaring an External Function to the Compiler |
|
| | Working with Constants and Types |
|
| | Calling DLL Functions: Important Issues |
|
| | Using API Functions |
|
| | Practical Examples: Using Windows API Functions in Your Applications |
|
| | Summary |
|
|
| |
Chapter 24.
Creating Your Own Libraries |
|
| | Why This Chapter Is Important |
|
| | Preparing a Database to Be a Library |
|
| | Creating a Reference |
|
| | Debugging a Library Database |
|
| | Securing an Access Library |
|
| | Practical Examples: Building a Library for Your Application |
|
| | Summary |
|
|
| |
Chapter 25.
Using Builders, Wizards, and Menu Add-Ins |
|
| | Why This Chapter Is Important |
|
| | Using Builders |
|
| | Using Wizards |
|
| | Using Menu Add-Ins |
|
| | Practical Examples: Designing Your Own Add-Ins |
|
| | Summary |
|
|
| |
Chapter 26.
An Introduction to Access and the Internet/Intranet |
|
| | Why This Chapter Is Important |
|
| | Saving Database Objects as HTML |
|
| | Linking to HTML Files |
|
| | Importing HTML Files |
|
| | Understanding Data Access Pages |
|
| | Creating Data Access Pages |
|
| | Modifying Important Properties of a Data Access Page |
|
| | Modifying the Record Navigation Control Properties |
|
| | Creating Grouped Data Access Pages |
|
| | Augmenting Data Access Pages with VBScript |
|
| | Practical Examples |
|
| | Summary |
|
|
| | Part V.
Adding Polish to Your Application |
|
| |
Chapter 27.
Database Security Made Easy |
|
| | Why This Chapter Is Important |
|
| | Implementing Share-Level Security: Establishing a Database Password |
|
| | Encrypting a Database |
|
| | Establishing User-Level Security |
|
| | Securing VBA Code with a Password |
|
| | Providing an Additional Level of Security: Creating an MDE |
|
| | Securing a Database Without Requiring Users to Log On |
|
| | Looking at Special Issues |
|
| | Practical Examples: Securing a Copy of the Northwind Database |
|
| | Summary |
|
|
| |
Chapter 28.
Advanced Security Techniques |
|
| | Why This Chapter Is Important |
|
| | Using Code to Maintain Groups |
|
| | Using Code to Maintain Users |
|
| | Listing All Groups and Users |
|
| | Working with Passwords |
|
| | Assigning and Revoking Permissions to Objects Using Code |
|
| | Encrypting a Database Using Code |
|
| | Accomplishing Field-Level Security Using Queries |
|
| | Prohibiting Users and Groups from Creating Objects |
|
| | Accomplishing Prohibited Tasks by Logging On as a Different User |
|
| | Securing Client/Server Applications |
|
| | Security and Replication |
|
| | Implementing Security with SQL |
|
| | DAO and Security |
|
| | Choosing Between ADOX, SQL, and DAO |
|
| | Practical Examples: Applying Advanced Techniques to Your Application |
|
| | Summary |
|
|
| |
Chapter 29.
Documenting Your Application |
|
| | Why This Chapter Is Important |
|
| | Preparing Your Application to Be Self-Documenting |
|
| | Using the Database Documenter |
|
| | The Object Dependency Feature |
|
| | Writing Code to Create Your Own Documentation |
|
| | Practical Examples: Applying What You Learned |
|
| | Summary |
|
|
| |
Chapter 30.
Maintaining Your Application |
|
| | Why This Chapter Is Important |
|
| | Compacting Your Database |
|
| | Backing Up Your Database |
|
| | Converting an Access Database |
|
| | Detecting Broken References |
|
| | Practical Examples: Maintaining Your Application |
|
| | Summary |
|
|
| |
Chapter 31.
Third-Party Tools That Can Help You Get Your Job Done Effectively |
|
| | Why This Chapter Is Important |
|
| | Total Visual CodeTools |
|
| | Total Access Analyzer |
|
| | Total Access Emailer |
|
| | Total Visual Agent |
|
| | Total Access Startup |
|
| | Total Access Components |
|
| | Total Access Memo |
|
| | Total Access Statistics |
|
| | Total Access Detective |
|
| | Total Visual SourceBook |
|
| | Total Access Speller |
|
| | Total Access Admin |
|
| | Solutions::Explorer |
|
| | Component Toolbox OCX |
|
| | Solutions::Schedule |
|
| | Solutions::PIM Professional |
|
| | Practical Examples: Using Third-Party Tools with Your Applications |
|
| | Summary |
|
|
| |
Chapter 32.
Distributing Your Application |
|
| | Why This Chapter Is Important |
|
| | Introducing the Packaging Wizard |
|
| | Loading the Packaging Wizard Add-In |
|
| | Distributing Your Application to Run with a Full Copy of Access |
|
| | Using Full Versions Versus Runtime Versions of Access |
|
| | Preparing Your Database for Use with the Access Runtime Version |
|
| | Looking at Other Issues |
|
| | Practical Examples |
|
| | Summary |
|
|
| | Part VI.
Appendixes |
|
| |
Appendix A.
Table Structures |
|
| | The tblClients Table |
|
| | The tblClientAddresses Table |
|
| | The tblAddressTypes Table |
|
| | The tblClientPhones Table |
|
| | The tblPhoneTypes Table |
|
| | The tblCorrespondence Table |
|
| | The tblCorrespondenceTypes Table |
|
| | The tblTerms Table |
|
| | The tblContactTypes Table |
|
| | The tblCompanyInfo Table |
|
| | The tblEmployees Table |
|
| | The tblErrorLog Table |
|
| | The tblErrors Table |
|
| | The tblExpenseCodes Tables |
|
| | The tblPaymentMethods Table |
|
| | The tblPayments Table |
|
| | The tblProjects Table |
|
| | The tblTimeCardExpenses Table |
|
| | The tblTimeCardHours Table |
|
| | The tblTimeCards Table |
|
| | The tblWorkCodes Table |
|
|
| |
Appendix B.
Naming Conventions |
|
| |
License Agreement |
| |
What''s on the CD-ROM |
|
| | Windows Installation Instructions |
|
| | Index
|