Special Edition Using Microsoft Access 2002

Roger Jennings is an author and consultant specializing in Windows client/server databases and network operating systems. He was a member of the Microsoft technical beta testing team for all seven editions of Microsoft Access; every release of Visual Basic since version 2.0; Windows 3.1; Windows for Workgroups 3.1 and 3.11; Windows 95, 98, Me, and XP; Windows NT 3.5, 3.51, 4.0, Server; Windows 2000 Server and its successor (collectively called Windows 2000+ in this book); and Microsoft SQL Server 6.0, 6.5, 7.0, and 2000. Roger was one of the founding members of Microsoft's Access Insiders group. Roger's books have more than one million English copies in print and have been translated into 20+ languages. He's the author of Que's Special Edition Using Microsoft Access titles for Access versions 1.0, 1.1, 2.0, 95, 97 (first and second editions), and 2000, and Platinum Edition Using Access 97. He also wrote Que's Special Edition Using Microsoft Windows NT Server 4, Special Edition Using Microsoft Windows 2000 Server, Unveiling Microsoft Windows 95, Access Hot Tips, and Discover Microsoft Windows 3.1 Multimedia. For Pearson Technology Group's Sams imprint, he's written two editions of Access Developer's Guide, three editions of Database Developers Guide with Visual Basic (for versions 3.0, 4.0, and 6.0), and is series editor for the Roger Jennings' Database Workshop titles. Roger is a contributing editor for Fawcette Technical Publication's Visual Basic Programmer's Journal (http://www.vbpj.com/); his "Database Design" columns and feature articles on Microsoft database technology appear regularly in VBPJ. Roger co-authored with Microsoft's Greg Nelson "A Client/Server Application: From Concept to Reality" presentation and white paper on Access 2.0, which appeared on the Microsoft Developer's Network CD-ROM and in the Microsoft Developer Network News. An Access 2002 version of the application described in the white paper is in the /Seua10/Chaptr14 folder of the accompanying CD-ROM. Roger has more than 25 years of computer-related experience, beginning with his work on the Wang 700 desktop calculator/computer. He's a principal of OakLeaf Systems, a Northern California software consulting firm and is the Webmaster for OakLeaf Music, http://www.oakmusic.com/. You can contact Roger at Roger_Jennings@compuserve.com.

Introduction.
I. GETTING ACQUAINTED WITH ACCESS 2002.

1. Access 2002 for Access 97 and 2000 Users: What's New.


Welcome to Another Access Upgrade. Changes to Basic Access 2000 Features. PivotTable and PivotChart Views. Access Data Projects and SQL Server 2000. Using XML Static Data Import and Export. Data Access Pages Revisited. Programmability Enhancements. Access 2002 Installation. SQL Server 2000 Desktop Engine Setup. Troubleshooting. In the Real World-The Road to Access.NET.

2. 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?

3. 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.

4. 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.

II. LEARNING THE FUNDAMENTALS OF JET DATABASES.

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. 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.

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.

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.

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.

III. TRANSFORMING DATA WITH QUERIES AND PIVOTTABLES.

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-Query Design Optimization.

10. Understanding Jet Operators and Expressions.


Writing Expressions for Jet Queries and Data Validation. Understanding the Elements in Expressions. Operators. Literals. Identifiers. Functions. Intrinsic and Named Constants. Creating Jet Expressions. Troubleshooting. In the Real World-The Algebra of Access Expressions.

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.

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 the Performance of PivotTables. Formatting and Manipulating PivotCharts. Troubleshooting. In the Real World-Visualizing Data.

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.

IV. DESIGNING FORMS AND REPORTS.

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.

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. Using Bound Object Frames. Working with Tab Controls. Optimizing the Form's Design. Adding a History Subform to a Tab Control Page. Overriding the Field Properties of Tables. Adding Page Headers and Footers for Printing Forms. Troubleshooting. In the Real World-Access Wizardry.

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.

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.

18. Adding Graphs, PivotCharts, and PivotTables.


Generating Graphs and Charts with Microsoft Graph 10. 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.

V. UPGRADING TO SQL SERVER 2000 DATABASES.

19. Linking Jet Applications to Client/Server Tables.


Evaluating the Benefits of Migrating to Client/Server Databases. Choosing a Client/Server Migration Strategy. Upsizing a Single-User Application to SQL Server 2000. Using the Upsizing Wizard with Linked Jet Tables. Linking Client/Server Tables Manually. Password-Protecting Jet Applications. Applying User-Level Security to Jet Front Ends. Changing Database Group and User Permissions. Troubleshooting. In the Real World-The (Almost) Free Lunch.

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-ADP Close In on Jet's Ease of Use.

21. Moving from Jet Queries to Transact-SQL.


Understanding the Role of SQL in Access 2002. 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.

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. Troubleshooting. In the Real World-Strategic or Not?

23. Administering Databases with SQL Enterprise Manager.


Comparing Jet and SQL Server Database Management Tools. Installing the SQL Server Client Tools. Running SQL Server Enterprise Manager for the First Time. Managing SQL Server Properties. Establishing User Logins. Exploring Database Properties. Working with Database Objects in Enterprise Manager Scheduling Database Backup Operations with the SQL Server Agent. Generating T-SQL Scripts to Clone Database Objects. Setting Up and Testing SQL Server Replication. Managing Linked Servers. Working with SQL Query Analyzer. Troubleshooting. In the Real World-Enterprise-Class Management Tools.

VI. PUBLISHING DATA TO INTRANETS AND THE INTERNET.

24. Exporting and Importing Data with XML.


Moving from Access to Browser-Based Front Ends. Gaining an XML Vocabulary. Understanding the Role of Access's ReportML. Exporting Tables and Queries to XML and HTML. Reformatting HTML Tables and Adding Page Elements. Deploying Exported XML File Sets to a Web Server. Adapting Views of Tables or Queries to Users' Needs. Exporting Static Reports As XML. Exporting Live Web Reports. Using SQL Server 2000's HTTP Query Features. Setting Up SQL Server 2000 and IIS for Web Access. Importing XML Data to Tables. Applying Custom XSL Transforms to XML Data. Troubleshooting. In the Real World-Why Learn XML?

25. 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. Providing Clients with OWC 10 Runtime Files. Delivering Pages on an Intranet or the Internet. Moving from Jet to SQL Server Page Data Sources. Troubleshooting. In the Real World-Are DAP Finally Ready for Prime Time?

26. 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-The Role of Data Access Page Design.

VII. PROGRAMMING AND CONVERTING ACCESS APPLICATIONS.

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.

28. Understanding Universal Data Access, OLE DB, and ADO.


Gaining a Perspective on Microsoft Data Access Components. Interfacing with a Wide Range of Data Sources. 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 AddOrders.adp Sample Project. Troubleshooting. In the Real World-Why Learn ADO Programming?

29. 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 2002's Event Repertoire. Working with Access 2002'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.

30. 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.

31. Upgrading Access 9x and 2000 Applications to Access 2002.


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 to 2002. Upgrading from MSDE 1.0 to SQL Server 2000 Desktop Edition. Troubleshooting. In the Real World-The Upgrade Blues.

Glossary.
Index.