Special Edition Using Microsoft Access 2000

Roger Jennings is a principal of OakLeaf Systems, a northern California consulting firm specializing in Windows client/server database and digital video applications. Roger is the author of numerous Access, Windows, and Windows NT books. Roger is a Microsoft Access Insider, working with the Access team early in beta cycle testing both alphas and betas for Microsoft. Roger is a contributing editor for Fawcette Technical Publications, Inc.¿s Visual Basic Programmer¿s Journal.

I. LEARNING ACCESS FUNDAMENTALS.

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


Putting Access 2000 in Perspective. Deploying Data Access Pages with Office Web Components. Creating Access Data Projects for the Microsoft Data Engine. Integrating the Office VBA Editor with Access. Working with the HTML Source Editor for DAP and DHTML. Viewing and Editing Related Records in Subdatasheets. Conforming Jet and SQL Server 7.0/MSDE Databases. Giving Access a Year 2000 Facelift. Installing Access 2000. Installing and Configuring the Microsoft Data Engine. In the Real World—Why Upgrade?

2. Building Your First Access 2000 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. The Real World—Putting What You've Learned in Perspective.

3. Navigating Within Access.


Understanding Access Functions and Modes. 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.

4. Working with Access Databases and Tables.


Defining the Elements of Access Databases. Understanding Relational Databases. Using Access Database Files and Tables. Creating a New Database. Understanding the Properties of Tables and Fields. Choosing Field Data Types, Sizes, and Formats. Using the Northwind Traders Sample Database. Adding a New Table to an Existing Database. Setting Default Values of Fields. Working with Relations, Key Fields, and Indexes. Altering Fields and Relationships. Copying and Pasting Tables. Troubleshooting. In the Real World—Database Strategy and Table Tactics.

5. Entering, Editing, and Validating Data in Tables.


Entering Test 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 Personnel Actions Table. Entering Personnel Actions Table Data and Testing Validation Rules. Troubleshooting. In the Real World—Heads-Down Data Entry.

6. Sorting, Finding, and Filtering Data in Tables.


Understanding the Role of Sorting and Filtering. Sorting Table Data. Finding Matching Records in a Table. Replacing Matched Field Values Automatically. Filtering Table Data. Customizing Datasheet View. Copying, Exporting, and Mailing Sorted and Filtered Data. Troubleshooting. In the Real World—Computer-Based Sorting and Searching.

7. Linking, Importing, and Exporting Tables.


Moving Data from and to Other Applications. Understanding How Access Handles Tables in Other Database File Formats. Importing and Linking Spreadsheet Files. Working with Microsoft Outlook and Exchange Folders. Exporting and Importing Jet 4.0 Tables with Outlook. Importing Text Files. Using the Clipboard to Import Data. Exporting Data from Access Tables. Troubleshooting. In the Real World—Microsoft Giveth and Microsoft Taketh Away.

II. GETTING THE MOST OUT OF QUERIES.

8. Designing Access Queries.


Introducing Queries. Trying the Simple Query Wizard. Using the Query Design Window. Creating Other Types of Queries. Troubleshooting. In the Real World—Query Design Optimization.

9.Understanding Query Operators and Expressions.


Writing Expressions for Query Criteria and Data Validation. Understanding the Elements in Expressions. Operators. Creating Access Expressions. Troubleshooting. In the Real World—The Algebra of Access Expressions.

10. 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. Creating Queries from Tables in Other Databases. Troubleshooting. In the Real World—Optimizing Multitable Queries.

11. Modifying Data 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 Updating. Troubleshooting. In the Real World—Alternatives to Action Queries.

III. DESIGNING FORMS AND REPORTS.

12. Creating and Using Forms.


Understanding the Role of Access Forms and Controls. Creating a Transaction-Processing Form with the Form Wizard. Using the Form Design Window. Selecting, Editing, and Moving Form Elements and Controls. Rearranging the Personnel Actions Form. Using Transaction-Processing Forms. Modifying the Properties of a Form or Control After Testing. In the Real World—The Art of Form Design. Understand the Audience.

13. Designing Custom Multitable Forms.


Expanding Your Form Design Repertoire. Understanding the Access Toolbox. Using the Toolbox To Add Controls. Completing the Main Personnel Actions Entry Form. Creating a Subform Using the Subform/Subreport Wizard. Modifying the Design of Continuous Forms. Overriding the Field Properties of Tables. Adding Page Headers and Footers for Printing Forms. Troubleshooting. In the Real World—Access Wizardry.

14. Printing Basic 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. 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 Apocryphal Paperless Office.

15. Preparing Advanced Reports.


Creating Reports from Scratch. Grouping and Sorting Report Data. Working from a Blank Report. Incorporating Subreports. Customizing De Novo Reports. Reducing the Length of Reports. Adding Other Controls to Reports. Mailing Report Snapshots. Troubleshooting. In the Real World—The Art of Report Design.

IV. PUBLISHING DATA ON INTRANETS AND THE INTERNET.

16. Working with Hyperlinks and HTML.


Data-Enabling Web Pages. Putting Microsoft's Internet Program in Perspective. Navigating the Web and Intranets with Hyperlinks. Understanding Access 2000's Hyperlink Field Data Type. Using Hyperlinks with Access Controls. Specifying Other Internet Uniform Resource Locators. Troubleshooting. In the Real World—To Internet or.

17. Generating Web Pages from Tables and Queries.


Easing the Way to Web-Based Decision Support. Exporting Table and Query Datasheets to HTML. Importing Data from HTML Tables. Creating Dynamic Web Pages. Troubleshooting. In the Real World—ASP versus.

18. Designing Data Access Pages.


Moving to a New Access Form Model. Understanding Access's Dynamic HTML Implementation. Getting Acquainted with DAP. Read-Write Pages. Using the Page Wizard to Create Simple DAP. Using AutoPage to Create Columnar DAP. Modifying the Design of AutoPage. Starting a DAP from Scratch. Adding Charts to DAP with a PivotTable List. Troubleshooting. In the Real World—Are DAP Ready for Prime Time?

V. INTEGRATING ACCESS WITH OTHER OFFICE 2000 APPLICATIONS.

19. Adding Charts and Graphics to Forms and Reports.


Enlivening Forms and Reports with Graphics. Creating Graphs and Charts with Microsoft Graph 2000. Using the Chart Web Control in Pages. Adding a Bound Object Control to a Form or Report. Using the Image Control. Troubleshooting. In the Real World—Visualizing Data.

20. Using Access with Microsoft Excel.


Slicing and Dicing Data with PivotTables. Using the Access PivotTable Wizard. Manipulating PivotTables in Excel. Improving PivotTable Formatting. Formatting PivotTable Reports. Creating a PivotChart from a PivotTable. Using Excel as an OLE Server. Troubleshooting. In the Real World—OLAP and PivotTables.

21. Using Access with Microsoft Word and Mail Merge.


Integrating Access 2000 with Word 2000. Using the Access Mail Merge Wizard. Using Word 2000's Mail Merge Feature with Access Databases. Embedding or Linking Word Documents in Access Tables. Troubleshooting. In the Real World—Microsoft Query and OLE DB.

VI. USING ADVANCED ACCESS TECHNIQUES.

22. Exploring Relational Database Design and Implementation.


Reviewing Access 2000's New Database Design Features. Integrating Objects and Relational Databases. Understanding Database Systems. Data Modeling. Normalizing Data to the Relational Model. Using Access 2000's Table Analyzer Wizard. Generating a Data Dictionary with the Database Documenter. Using Access Indexes. Enforcing Database Integrity. Troubleshooting. In the Real World—Why Learn Relational Theory?

23. Working with Structured Query Language.


Understanding the Role of SQL in Access 2000. Using Access to Learn SQL. Understanding SQL Grammar. Writing SELECT Queries in SQL. Writing Action and Crosstab Queries. Adding IN to Use Tables in Another Database. Using SQL Statements in Forms, Reports, and Macros. Troubleshooting. In the Real World—SQL As a Second Language.

24. Securing Multiuser Network Applications.


Networking Access 2000 Applications. Installing Access in a Networked Environment. Sharing Your Access Database Files with Other Users. Using Command-Line Options to Open a Shared Database. Maintaining Database Security. Understanding Database Object Ownership. Granting and Revoking Permissions for Database Objects. Sharing Databases on the Network. Accessing the Shared Workgroup and Data Files. Attaching the Shared Workgroup System File. Administering Databases and Applications. Troubleshooting. In the Real World—Shared-File versus Client/Server Back Ends.

25. Creating Access Data Projects.


Moving Access to the Client/Server Model. Understanding the Role of MSDE. Installing and Starting MSDE. Getting Acquainted with ADP. Using the Project Designer. Using the Upsizing Wizard to Create ADP. Downsizing Databases with the DTS Wizard. Connecting to Remote MSDE Databases. Establishing MSDE Security. Troubleshooting. In the Real World—ADP on Trial.

VII. PROGRAMMING AND CONVERTING ACCESS APPLICATIONS.

26. Writing Visual Basic for Applications Code.


Understanding the Role of VBA in Access. Introducing VBA 6.0. Controlling Program Flow. Handling Runtime Errors. Exploring the VBA Editor. Examining the Utility Functions Module. In the Real World—Macro Schizophrenia.

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


Gaining a Perspective on Microsoft's New Data Access Components. Interfacing with a Wide Range of Data Sources. Creating ADODB.Recordsets. Binding Controls to a Recordset with Code. Making the Form Updatable. Exploring Top-Level ADO Properties, Methods, and Events. Working with the ADODB.Connection Object. Using the ADODB.Command Object. Understanding the ADODB.Recordset Object. Troubleshooting. In the Real World—Struggling with ADO.

28. Responding to Events with VBA 6.0.


Introducing Event-Driven Programming. Understanding the Role of Class Modules. Examining Project Class Module Members in the Object Browser and Project Explorer. Adding Event-Handling Code with the Command Button Wizard. Using Functions to Respond to Events. Understanding Access 2000's Event Repertoire. Working with Access 2000's DoCmd Methods. Customizing Applications with CommandBar Objects. Specifying a Custom CommandBar and Setting Other Startup Properties. Referring to Access Objects with VBA. Using Alternative Collection Syntax. Responding to Data Events Triggered by Forms and Controls. Troubleshooting. In the Real World—Dealing with Event-Driven Programming.

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. Converting Your Combo Box Form to an Access Data Project. Drilling Down from a List Box Selection. Adding New Features to List and Combo Boxes. Dealing with Jet-Specific Functions in Migrating to ADP. Troubleshooting. In the Real World—Access Combo and List Boxes.

30. Working with ADO Recordsets, Forms, and Controls.


Navigating Recordsets with VBA. Using the Find Method and Bookmarks. Modifying Rows of Recordsets. Populating a Combo Box from a Recordset. Altering the Sequence of Combo Box Lists. Filling List Boxes from Recordset Objects. Formatting Value List Combo Box Columns. Porting frmComboVBA to an Access Data Project. Troubleshooting. In the Real World—Adapting to ADO.

31. Migrating Access 9x Applications to Access 2000.


Understanding the .mdb File Upgrade Process. Converting Unsecured Files from Access 9x to Access 2000. Upgrading After Opening the File in Access 2000. Converting Secure Access 9x Files. Upgrading Access 2.0 Application .mdb Files to Access 2000. Troubleshooting. In the Real World—The Upgrade Blues.

VIII. APPNENDIX.

Appendix A. Glossary.
Index.