Table of content

•	Table of Contents
•	Index

Special Edition Using® Microsoft® Office Access 2003
By Roger Fennings
Publisher	: Que
Pub Date	: September 15, 2003
ISBN	: 0-7897-2952-0
Pages	: 1504

		Copyright
		About the Author
		Acknowledgments
		We Want to Hear from You!
		Reader Services
		Introduction
			Who Should Read This Book
			How This Book Is Organized
			How This Book Is Designed
			Typographic Conventions Used in This Book
			System Requirements for Access 2003
			Other Sources of Information for Access
		Part I:  Getting Acquainted with Access 2003
			Chapter 1.  Access 2003 for Access 97 and 2000/2002 Users: What's New
				Welcome to Microsoft Office Access 2003
				What's New in Access 2003
				Access 2002 Changes to Basic Access 2000 Features
				PivotTable and PivotChart Views
				Access Data Projects and SQL Server 2000
				Data Access Pages Revisited
				Programmability Enhancements
				SQL Server 2000 Desktop Engine Setup
				In the Real World Still Waiting for "Access .NET"
			Chapter 2.  Building a Simple Desktop and Web Application
				Understanding Access's Approach to Application Design
				Creating an Access Application from a Template File
				Touring the Contact Management Application
				Using the Switchboard Manager
				Exploring Form Design View and VBA Class Modules
				Downloading Templates from the Microsoft Office Update Site
				Saving a Form as a Data Access Page
				In the Real World Putting What You've Learned in Perspective
			Chapter 3.  Navigating the Access User Interface
				Understanding Access Functions and Modes
				Opening the Northwind.mdb Sample Database
				Understanding Access's Table Display
				Using the Function Keys
				Setting Default Options
				Using Access Help
				Using the Database Utilities
				Troubleshooting
				In the Real World HTML Help or Hindrance
		Part II:  Learning the Fundamentals of Jet Databases
			Chapter 4.  Exploring Relational Database Theory and Practice
				Moving from Spreadsheets to Databases
				Reliving Database History
				Defining the Structure of Relational Databases
				Conforming to Table Design Rules
				Choosing Primary Key Codes
				Maintaining Data Integrity and Accuracy
				Displaying Data with Queries and Views
				Partitioning Databases
				In the Real World When and Why Learn Relational Theory?
			Chapter 5.  Working with Jet Databases and Tables
				Understanding Jet Database Files
				Creating a New Jet Database
				Exploring the Properties of Tables and Fields
				Choosing Field Data Types, Sizes, and Formats
				Working with the Northwind Traders Sample Database
				Preparing to Add a Table Related to the Employees Table
				Designing the HRActions Table
				Setting Default Values of Fields
				Working with Relations, Key Fields, and Indexes
				Altering Fields and Relationships
				Working with Object Dependencies and Access Smart Tags
				Copying and Pasting Tables
				Using the Table Analyzer Wizard
				Generating a Data Dictionary with the Database Documenter
				Troubleshooting
				In the Real World Database Strategy and Table Tactics
			Chapter 6.  Entering, Editing, and Validating Jet Table Data
				Entering Data in Access Tables
				Using Keyboard Operations for Entering and Editing Data
				Adding Records to a Table
				Selecting, Appending, Replacing, and Deleting Table Records
				Validating Data Entry
				Adding Records to the HRActions Table
				Testing Field and Table Validation Rules
				Troubleshooting
				In the Real World Heads-Down Data Entry
			Chapter 7.  Sorting, Finding, and Filtering Data
				Understanding the Role of Sorting and Filtering
				Sorting Table Data
				Finding Matching Records in a Table
				Replacing Matched Field Values Automatically
				Filtering Table Data
				Applying Advanced Filters and Sort Orders
				Customizing Table Datasheet View
				Copying, Exporting, and Mailing Sorted and Filtered Data
				Troubleshooting
				In the Real World Computer-Based Sorting and Searching
			Chapter 8.  Linking, Importing, and Exporting Data
				Moving Data from and to Other Applications
				Working with Tables in Other Database File Formats
				Importing and Linking Spreadsheet Files
				Working with Microsoft Outlook and Exchange Folders
				Importing Text Files
				Using the Access Mail Merge Wizard
				Exporting Tables to Word and Excel
				Exporting Data to Web Servers
				Exporting Table Data as Text Files
				Exporting Data in Other File Formats
				Troubleshooting
				In the Real World Microsoft Giveth and Microsoft Taketh Away
		Part III:  Transforming Data with Queries and PivotTables
			Chapter 9.  Designing Queries for Jet Databases
				Introducing Jet Queries
				Trying the Simple Query Wizard
				Using the Query Design Window
				Creating Other Types of Queries
				Troubleshooting
				In the Real World Optimizing Query Design
			Chapter 10.  Understanding Jet Operators and Expressions
				Writing Expressions for Query Criteria and Data Validation
				Understanding the Elements of Expressions
				Operators
				Literals
				Identifiers
				Functions
				Intrinsic and Named Constants
				Creating Jet Expressions
				Troubleshooting
				In the Real World The Algebra of Access Expressions
			Chapter 11.  Creating Multitable and Crosstab Queries
				Introducing Joins on Tables
				Joining Tables to Create Multitable Queries
				Using Lookup Fields in Tables
				Adding Subdatasheets to a Table or Query
				Outer, Self, and Theta Joins
				Updating Table Data with Queries
				Making All Fields of Tables Accessible
				Making Calculations on Multiple Records
				Designing Parameter Queries
				Creating Crosstab Queries
				Writing UNION Queries and Subqueries
				Creating Queries from Tables in Other Databases
				Troubleshooting
				In the Real World Optimizing Multitable Queries
			Chapter 12.  Working with PivotTable and PivotChart Views
				Understanding the Role of PivotTables and PivotCharts
				Slicing and Dicing Data with PivotTables
				Creating the Query for a Sample PivotTable View
				Designing the PivotTable View of the Sample Query
				Setting PivotTable Property Values
				Exporting the PivotTable to Excel
				Optimizing Performance of PivotTables
				Formatting and Manipulating PivotCharts
				Troubleshooting
				In the Real World Visualizing Data
			Chapter 13.  Creating and Updating Jet Tables with Action Queries
				Getting Acquainted with Action Queries
				Creating New Tables with Make-Table Queries
				Creating Action Queries to Append Records to a Table
				Deleting Records from a Table with an Action Query
				Updating Values of Multiple Records in a Table
				Testing Cascading Deletion and Cascading Updates
				Troubleshooting
				In the Real World Alternatives to Action Queries
		Part IV:  Designing Forms and Reports
			Chapter 14.  Creating and Using Access Forms
				Understanding the Role of Access Forms and Controls
				Creating a Basic Transaction-Processing Form with the Form Wizard
				Using the Form Design Window
				Setting Form Appearance Properties
				Selecting, Editing, and Moving Form Elements and Controls
				Rearranging the HRActions Form
				Using Transaction-Processing Forms
				Modifying the Properties of a Form or Control After Testing
				Troubleshooting
				In the Real World The Art of Form Design
			Chapter 15.  Designing Custom Multitable Forms
				Expanding Your Form Design Repertoire
				Understanding the Access Toolbox
				Access's Control Wizards, Builders, and Toolbars
				Using the Toolbox to Add Label and Text Controls
				Adding Option Groups with the Wizard
				Using the Clipboard to Copy Controls to Another Form
				Adding Combo and List Boxes
				Working with Tab Controls
				Optimizing the Form's Design
				Adding a History Subform to a Tab Control Page
				Adding New Records in the HRActionEntry Form
				Overriding the Field Properties of Tables
				Adding Page Headers and Footers for Printing Forms
				Troubleshooting
				In the Real World Access Wizardry
			Chapter 16.  Working with Simple Reports and Mailing Labels
				Understanding the Relationship Between Forms and Reports
				Categorizing Types of Access Reports
				Creating a Grouping Report with the Report Wizard
				Using Access's Report Windows
				Using AutoFormat and Customizing Report Styles
				Modifying a Basic Wizard Report
				Adding Calculated Controls to a Report
				Aligning and Formatting Controls and Adjusting Line Spacing
				Adjusting Margins and Printing Conventional Reports
				Preventing Widowed Records with the Group Keep Together Property
				Printing Multicolumn Reports as Mailing Labels
				Troubleshooting
				In the Real World The Ephemeral Paperless Office
			Chapter 17.  Preparing Advanced Reports
				Creating Reports from Scratch
				Grouping and Sorting Report Data
				Working from a Blank Report
				Incorporating Subreports
				Customizing De Novo Reports
				Mailing Report Snapshots
				Troubleshooting
				In the Real World The Art of Report Design
			Chapter 18.  Adding Graphs, PivotCharts, and PivotTables
				Generating Graphs and Charts with Microsoft Graph
				Printing Graphs or Charts in Reports
				Creating a Linked Graph from a Jet Crosstab Query
				Working with PivotChart Forms
				Substituting or Adding a PivotTable in a Form
				Troubleshooting
				In the Real World A Hobson's Choice: MSGraph Objects Versus PivotCharts
		Part V:  Upgrading to SQL Server Databases
			Chapter 19.  Linking Access Front-Ends to Jet and Client/Server Tables
				Creating Multiuser Access Applications by Linking Tables
				Evaluating the Benefits of Migrating to Client/Server Databases
				Choosing a Client/Server Migration Strategy
				Upsizing a Single-File Application to SQL Server 2000
				Moving the Upsized Database to Another Server
				Linking Client/Server Tables Manually
				Adding User Logins with the OSQL Utility
				Password-Protecting Access Front-Ends
				Applying User-Level Security to Access Front-Ends
				Changing Database Group and User Permissions
				Troubleshooting
				In the Real World The (Almost) Free Lunch
			Chapter 20.  Exploring Access Data Projects and SQL Server 2000
				Moving Access to the Client/Server Model
				Understanding the Role of SQL Server and ADP
				Getting Acquainted with Access Data Projects
				Working with SQL Server Tables in the Project Designer
				Exploring SQL Server Views
				Taking Advantage of In-line Functions
				Examining Stored Procedures
				Diagramming Table Relationships
				Backing Up and Restoring Databases
				Transferring the Project's Database to a Server
				Connecting to a Remote SQL Server Database
				Linking Remote Servers
				Securing Your Project as an .ade File
				Troubleshooting
				In the Real World Strategic Database Schizophrenia
			Chapter 21.  Moving from Jet Queries to Transact-SQL
				Understanding the Role of SQL in Access 2003
				Understanding SQL Grammar
				Writing SELECT Queries in SQL
				Writing Action Queries and Stored Procedures
				Working with Tables in Another Database
				Creating Tables with ANSI-92 DDL
				Using SQL Statements with Forms, Reports, and Controls
				Troubleshooting
				In the Real World SQL as a Second Language
			Chapter 22.  Upsizing Jet Applications to Access Data Projects
				Taking a Hard Look at the Upsizing Process
				Preparing to Upsize Your Jet Applications
				Upsizing with the Trial-and-Error Approach
				Comparing ANSI-92 SQL, T-SQL, and Jet SQL
				Emulating Jet Crosstab Queries with T-SQL
				Running the Jet Crosstab Upsizer
				Securing Projects with the MSDE 2000 Login/User Tool
				Troubleshooting
				In the Real World Strategic or Not?
		Part VI:  Publishing Data to Intranets and the Internet
			Chapter 23.  Exporting and Importing Data with XML
				Adopting XML as a Data Interchange Format
				Gaining an XML Vocabulary
				Moving from Access to XML-Based Web Front-Ends
				Exporting Tables and Queries to XML and HTML
				Reformatting HTML Tables and Adding Page Elements
				Deploying Exported XML File Sets to a Web Server
				Exporting Static Reports as XML
				Exporting Live Web Reports
				Using SQL Server 2000's HTTP Query Features
				Setting Up IIS 5.0 and SQL Server 2000 for Web Access
				Importing XML Data to Tables
				Exporting and Importing Data in Related Tables
				Applying Custom XSL Transforms to XML Data
				Troubleshooting
				In the Real World Why Learn XML?
			Chapter 24.  Designing and Deploying Data Access Pages
				Moving to a New Access Form Model
				Understanding Access's Dynamic HTML Implementation
				Getting Acquainted with Page and Page Design Views
				Using the Page Wizard to Create Simple DAP
				Using AutoPage to Create Columnar DAP
				Using the Record Navigation Control's Filter and Sort Features
				Modifying the Design of AutoPage DAP
				Starting a Single-Level Page from Scratch
				Adding Bound Office Web Controls to DAP
				Generating a Grouped Page
				Deploying Pages for Network Access to Jet Data Sources
				Delivering Pages on an Intranet or the Internet
				Moving from Jet to SQL Server Page Data Sources
				Troubleshooting
				In the Real World Is the Third Time a Charm for DAP??
			Chapter 25.  Converting Access Objects to Data Access Pages
				Understanding the Limitations of the DAP Conversion Process
				Saving Tables as Data Entry Pages
				Saving Queries as Read-Only Pages
				Saving Forms as DAP
				Saving Reports as DAP
				Saving the Inventory by Category Report to a Page
				Troubleshooting
				In the Real World Enhancing Data Access Page Design
			Chapter 26.  Integrating with InfoPath and SharePoint Services
				Working with InfoPath Forms
				Designing and Deploying Form Templates
				Exploring the Contents of Form Template Files
				Designing an InfoPath Query and Data Editing Form
				Applying Advanced InfoPath Techniques
				Working with Windows SharePoint Services and Access
				Publishing Data-Bound InfoPath Templates to WSS
				Troubleshooting
				In the Real World Data-Centric Collaboration
		Part VII:  Programming and Converting Access Applications
			Chapter 27.  Learning Visual Basic for Applications
				Understanding the Role of VBA in Access
				Getting Acquainted with VBA 6.0
				Controlling Program Flow
				Handling Runtime Errors
				Exploring the VBA Editor
				Examining the Utility Functions Module
				Using Text Comparison Options
				In the Real World Macro Schizophrenia
			Chapter 28.  Handling Events with VBA 6.0
				Introducing Event-Driven Programming
				Understanding the Role of Class Modules
				Examining Project Class Module Members in Object Browser and Project Explorer
				Adding Event-Handling Code with the Command Button Wizard
				Using Functions to Respond to Events
				Understanding Access 2003's Event Repertoire
				Working with Access 2003's DoCmd Methods
				Customizing Applications with CommandBar Objects
				Specifying Database Startup Properties
				Referring to Access Objects with VBA
				Responding to Data Events Triggered by Forms and Controls
				Troubleshooting
				In the Real World Dealing with Event-Driven Programming
			Chapter 29.  Programming Combo and List Boxes
				Streamlining Decision Support Front Ends
				Constraining Query Choices with Combo Boxes
				Adding Code to Create the Query's SQL Statement
				Drilling Down from a List Box Selection
				Adding New Features to List and Combo Boxes
				Converting Your Combo Box Form to an Access Data Project
				Troubleshooting
				In the Real World Access Combo and List Boxes
			Chapter 30.  Understanding Universal Data Access, OLE DB, and ADO
				Gaining a Perspective on Microsoft Data Access Components
				Creating ADODB.Recordset Objects
				Using the Object Browser to Display ADO Properties, Methods, and Events
				Working with the ADODB.Connection Object
				Using the ADODB.Command Object
				Understanding the ADODB.Recordset Object
				Taking Advantage of Disconnected Recordsets
				Programming Stream Objects
				Exploring the AddOrder.adp Sample Project
				Troubleshooting
				In the Real World Why Learn ADO Programming?
			Chapter 31.  Consuming and Providing XML Web Services
				Integrating Access 2003 with XML Web Services
				Gaining a Web Services Vocabulary
				Executing ASP.NET Web Services from Internet Explorer
				Downloading and Installing the Office Web Services Toolkit
				Creating an Access Web Service Consumer
				Solving WSR Class Generation Problems
				Exploring XML Web Services with .NET WebService Studio
				Providing SQLXML Web Services
				Creating and Consuming a Local Alpha Web Service
				Troubleshooting
				In the Real World Why Web Services Are Important
			Chapter 32.  Upgrading Access 97 and 2000/2002 Applications to Access 2003
				Understanding the .mdb File Upgrade Process
				Converting Unsecured Files from Access 9x to Jet 4.0
				Converting Secure Access 9x Files
				Converting Data Access Pages from Access 2000 or 2002 to 2003
				Moving from MSDE 1.0 to the SQL Server 2000 Desktop Engine
				Troubleshooting
				In the Real World The Access Upgrade Blues
		Part VIII:  Appendix
			Appendix A.  Glossary
		Index