Home > Store

Special Edition Using Microsoft Access 2002

Register your product to gain access to bonus material or receive a coupon.

Special Edition Using Microsoft Access 2002

Book

  • Sorry, this book is no longer in print.
Not for Sale

Description

  • Copyright 2001
  • Edition: 1st
  • Book
  • ISBN-10: 0-7897-2510-X
  • ISBN-13: 978-0-7897-2510-3

Special Edition Using Access 2002 is a reader's authoritative guide to mastering the essential facets of this powerful database development platform. Detailed, step-by-step instructions guide the reader through the process of designing and using Access tables, queries, forms and reports.

Special Edition Using Access 2002 will include comprehensive coverage of the transition to MSDE/SQL Server for all multi-user applications, expanded coverage of Web Applications, and expanded coverage of XML.

This book contains elements such as Tips, Notes, cautions, cross-references and Troubleshooting information, giving the text a "Layered" quality that meets the needs of readers with different learning styles.

Extras

Tips

Untitled Query Subdatasheets
The default value of the Subdatasheet Name property for new tables you create is [Auto], which adds the column of boxed + symbols to a new table datasheet. To open the Add Subdatasheet dialog for a new table, choose Insert, Subdatasheet. Alternatively, you can set the subdatasheet properties directly in the Table Properties window. To remove a subdatasheet, set the Subdatasheet Name property value to [None]. If you remove a subdatasheet from a table, setting Subdatasheet Name to [Auto] displays the boxed + symbols and lets you add a new subdatasheet in Datasheet view.

Four-Digit Year Format
Don't depend on Windows 98 and NT users to change the Short Date Format with Control Panel's Regional Settings tool to make your application Y2K compliant. Marking the All Databases check box of the Use Four-Digit Year Formatting option (on the General page of the Options dialog) solves most Y2K issues. To be safe, however, use fixed four-digit year formats for all your date fields.

PivotTables versus Crosstab Queries
Substitute PivotTables for crosstab queries when your data presentation needs crossfooting or you want to apply sophisticated report formatting to the presentation. It's usually much faster to use PivotTable features to generate row totals, subtotals, and grand totals than it is to use crosstab queries. Another advantage of PivotTables is that users can set the amount of detail information that appears in the report and then generate their own graphs or charts from the data.

SQL Server Enterprise Manager
To give the SQL Server 2000 Client Tools a test run without purchasing Office XP Developer Edition or another version of SQL Server, order the SQL Server Evaluation Edition for US$7.50 from http://www.microsoft.com/sql/productinfo/evaluate.htm. The license for the Evaluation Edition expires 120 days after installation.

DAP Parameters
Don't attempt to pass a cookie value as a parameter to another page in Page view; both pages must run in IE to share the cookie. You receive an "Application uses a value of the wrong type for the current operation" error if the destination page is open in Page view.

Sample Content

Online Sample Chapter

Access 2002: Building a Simple Desktop and Web Application

Table of Contents



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.

Updates

Submit Errata

More Information

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020