Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

Sitemap

Main Page

Table of content

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

'Null's 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

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

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 'Null's 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

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

xplorer

Component Toolbox OCX

chedule

IM 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

Index SYMBOL

Index A

Index B

Index C

Index D

Index E

Index F

Index G

Index H

Index I

Index J

Index K

Index L

Index M

Index N

Index O

Index P

Index Q

Index R

Index S

Index T

Index U

Index V

Index W

Index X

Index Y

Index Z