| Special Edition Using Microsoft Office Access 2003 is divided into seven parts arranged in increasing levels of detail and complexity. Each division after Part I, "Getting Acquainted with Access 2003," draws on the knowledge and experience that you've gained in the previous parts, so use of the book in a linear, front-to-back manner through Part IV, "Designing Forms and Reports," is recommended during the initial learning process. After you absorb the basics of working with Jet databases, you progress through upsizing databases to SQL Server 2000. Chapters on Access's new XML and Data Access Pages features follow the SQL Server chapters because some XML and DAP elements depend on MSDE as the data source. As you progress through the chapters in this book, you create a model of an Access application called Human Resources Actions. In Chapter 5, "Working with Jet Databases and Tables," you create the HR Actions table. In the following chapters, you add new features to the HR Actions application. Be sure to perform the example exercises for the HR Actions application each time you encounter them because succeeding examples build on your previous work. (The accompanying CD-ROM contains sample databases at each stage of the process). The seven parts of Special Edition Using Microsoft Office Access 2003 and the topics that they cover are described in the following sections. Part I: "Getting Acquainted with Access 2003" The chapters in Part I introduce you to Access and many of the unique features that make Access 2003 the premier desktop database management system. Chapter 1, "Access 2003 for Access 97 and 2000/2002 Users: What's New," provides a summary of the most important new features of Access 2003 and a detailed description of each addition and improvement. Much of this chapter's content is of interest primarily to readers who now use Access 97 or 2000 because many of the changes from Access 2000 to Access 2003 are incremental in nature. Readers new to Access, however, benefit from the explanations of why many of these new features are significant in everyday Access 2003 use. In Chapter 2, "Building a Simple Desktop and Web Application," you use the Database Wizard to create a database from the standard database templates included with Access 2003. You gain a basic understanding of the standard data-related objects of Access, including tables, forms, reports, pages, and Visual Basic for Applications (VBA) modules. Chapter 2 also introduces you to automating Access operations with VBA Class Modules, the replacement for Access macros, and the Office VBA editor. Chapter 3, "Navigating the Access User Interface," shows you how to take best advantage of Access by explaining its toolbar and menu choices and then showing how they relate to the structure of the Access object model. Chapter 4 also shows you how to use Access 2003's new online help system, including the Type a Question for Help text box and the Help task pane that replace Office 2000's intrusive Office Assistant.
Part II: "Learning the Fundamentals of Jet Databases" Part II is devoted to understanding the design principals of relational databases, creating new Jet tables, adding and editing table data, and integrating Jet tables with other sources of data. Most of the techniques that you learn in Part II also apply to SQL Server tables. Chapter 4, "Exploring Relational Database Theory and Practice," describes the process that you use to create relational database tables from real-world data a technique called normalizing the database structure. The chapter also introduces you to the concepts of key fields, data integrity, and views of tables that contain related data. Chapter 5, "Working with Jet Databases and Tables," delves into the details of Jet desktop database tables, shows you how to create tables, and explains how to choose the optimum data types from the many new types that Access offers. Chapter 5 explains how to use subdatasheets and lookup tables to display and edit records in related tables. The chapter also explains how to use the Database Documentor tool included with Access 2003 to create a data dictionary that fully identifies each object in your database. Chapter 6, "Entering, Editing, and Validating Jet Table Data," describes how to add new records to tables, enter data in the new records, and edit data in existing records. Using keyboard shortcuts instead of the mouse for editing speeds manual data entry. Adding input masks and data validation rules minimizes the chance for typographic errors when entering new data. Chapter 7, "Sorting, Finding, and Filtering Data," shows you how to arrange the data in tables to suit your needs and to limit the data displayed to only that information you want. You learn how to use Find and Replace to search for and alter multiple instances of data in the fields of tables. Chapter 7 further describes how to make best use of the Filter by Form and Filter by Selection features of Access 2003. Chapter 8, "Linking, Importing, and Exporting Data," explains how to import and export files of other database managers, spreadsheet applications, and text files downloaded from mainframe or Unix database servers or the Internet. You also learn how use the Access Mail Merge Wizard to create form letters from data stored in Jet tables.
Part III: "Transforming Data with Queries and PivotTables" The chapters in Part III explain how to create Access queries to select the way that you view data contained in tables and how to take advantage of Access's relational database structure to link multiple tables with joins. Part III also covers Access 2003's PivotTable and PivotChart views of query result sets. Chapter 9, "Designing Queries for Jet Databases," starts with simple queries you create with Access's graphical Query Design window. You learn how to choose the fields of the tables included in your query and return query result sets from these tables. Examples of Jet SQL generated by the queries that you design let you learn SQL "by osmosis." Chapter 9 shows you how to use the Simple Query Wizard to simplify the design process. Chapter 10, "Understanding Jet Operators and Expressions," introduces you to the operators and expressions that you need to create queries that provide a meaningful result. Most Jet operators and expressions are the same as those that you use in VBA programs. You use the Immediate window of the Office 2003 VBA editor to evaluate the expressions that you write. In Chapter 11, "Creating Multitable and Crosstab Queries," you create relations between tables, called joins, and learn how to add criteria to queries so that the query result set includes only records that you want. Chapter 11 also takes you through the process of designing powerful crosstab queries to summarize data and to present information in a format similar to that of worksheets. Chapter 12, "Working with PivotTable and PivotChart Views," shows you how to manipulate data from multitable queries in the OWC's PivotTable control and then display the results in PivotChart controls. The query design and PivotTable/PivotChart techniques that you learn here also apply to PivotTables and PivotCharts that you embed in Access forms and Data Access Pages. Chapter 13, "Creating and Updating Jet Tables with Action Queries," shows you how to develop action queries that update the tables underlying append, delete, update, and make-table queries. Chapter 13 also covers Access 2003's advanced referential integrity features, including cascading updates and cascading deletions.
Part IV: "Designing Forms and Reports" The chapters in Part IV introduce you to the primary application objects of Access. (Tables and queries are considered database objects.) Forms make your Access applications come alive with the control objects that you add by using Access 2003's Toolbox. Access's full-featured report generator lets you print fully formatted reports, export or mail report snapshot files, and save reports to files that you can process in Excel 2003 or Word 2003. Chapter 14, "Creating and Using Access Forms," shows you how to use Access's Form Wizards to create simple forms and subforms that you can modify to suit your particular needs. Chapter 14 introduces you to the Subform Builder Wizard that uses drag-and-drop techniques to automatically create subforms for you. Chapter 15, "Designing Custom Multitable Forms," shows you how to design custom forms for viewing and entering your own data with Access's advanced form design tools. Chapter 16, "Working with Simple Reports and Mailing Labels," describes how to design and print basic reports with Access's Report Wizard, and how to print preformatted mailing labels by using the Mailing Label Wizard. Chapter 17, "Preparing Advanced Reports," describes how to use more sophisticated sorting and grouping techniques, as well as subreports, to obtain a result that exactly meets your detail and summary data-reporting requirements. Chapter 17 also covers the snapshot technology that lets you distribute Access reports as Outlook email attachments and save reports as snapshot (.snp) files. The Snapshot Viewer lets users without Access view and print email attachments or .snp files. In Chapter 18, "Adding Graphs, PivotCharts, and PivotTables," you first learn to use the OLE-based Chart Wizard to create databound graphs and charts based on Jet crosstab queries. PivotCharts are destined to replace conventional Access Charts, so Chapter 18 builds on Chapter 12 by showing you how to add bound PivotTables and Pivot Charts whose data is supplied by the form's data source.
Part V: "Upgrading to SQL Server Databases" Jet isn't dead, but version 4.0 is Microsoft's last iteration of this venerable desktop database. From Access 2003 on, SQL Server is the preferred desktop or back-end data source for Access applications in Access Data Project (.adp) format. If you're new to client/server RDBMSs, Access 2003 is the ideal learning tool for upgrading your database design and management skills to the requirements of today's job market. Chapter 19, "Linking Access Front-Ends to Jet and Client/Server Tables," explains how to use the Upsizing Wizard to migrate from single-file or split (front-end/back-end) Jet applications to SQL Server back-end databases. Retaining the front-end queries and application objects in a Jet (.mdb) file, and using the SQL Server ODBC driver to connect to the server database, minimizes application changes required to take advantage of client/server technology. This chapter also explains how to secure Jet databases with workgroup information (.mdw) files. Chapter 20, "Exploring Access Data Projects and SQL Server 2000," introduces you to Access Data Projects. The chapter shows you how to use Access 2003's built-in project designer to create and modify SQL Server tables, views, functions, and stored procedures. Backing up, restoring, copying, and moving SQL Server databases is covered in detail. You also learn how to link other databases, including Jet .mdb files, with OLE DB data providers and how to secure ADP front ends as .ade files. Chapter 21, "Moving from Jet Queries to Transact-SQL," provides a formal introduction to ANSI-92 SQL and explains how the Jet and Transact-SQL dialects differ. Special emphasis is given to queries that you can't create in the graphical project designer such as UNION queries and subqueries and enabling transactions in stored procedures that update two or more tables. Chapter 22, "Upsizing Jet Applications to Access Data Projects," explains how to use the Upsizing Wizard to convert existing Jet applications directly to Access data project front ends and SQL Server tables, views, functions, and stored procedures. The Wizard can't upsize Jet crosstab queries, so the chapter explains how to write T-SQL make-table statements to emulate crosstab queries.
Part VI: "Publishing Data to Intranets and the Internet" The chapters in Part VI explain how to take advantage of Access's new XML features and the upgraded Data Access Pages technology of Access 2002. Chapter 23, "Exporting and Importing Data with XML," explains the role of XML in database applications, how Access 2003's ReportML XML schema describes Access objects as an XML data document. The chapter shows you how to take advantage of the Report2HTML4.xsl XML transform to generate HTML pages from tables and queries with the Save As XML option. You learn how to modify Access's standard XSLT files to format the resulting tables and add images to the tables. Exporting conventional Access reports as fully formatted static and live Web reports also receives detailed coverage. Chapter 24, "Designing and Deploying Data Access Pages," shows you how to design dynamic Web pages to display and update data on your organization's intranet. The chapter guides your use of the Page Wizard and AutoPage option to create simple pages to display and edit live data. The chapter also covers adding PivotTables and PivotCharts to pages, and deploying pages from file shares or Internet Information Server 5+ virtual directories. Chapter 25, "Converting Access Objects to Data Access Pages," explains the benefits and limitations of using the Access 2003's Save As Data Access Page feature to export tables, queries, and reports as preformatted pages. The chapter includes VBScript examples for creating navigation pages to open and pass parameters as cookie crumbs to pages saved from parameter queries and reports based on parameter queries. Chapter 26, "Integrating with InfoPath and SharePoint Services," introduces you to these new members of Office System 2003 and their data-related features. You get acquainted with Office InfoPath 2003 form templates by modifying a sample form and then progress to creating a single-view query/data entry form that connects to a Jet or SQL Server table. You learn to export Jet or SQL Server tables to Windows SharePoint Services lists, and how to link the lists to Jet tables (and vice versa). Finally, the chapter shows you how to publish data-bound InfoPath templates to SharePoint forms libraries.
Part VII: "Programming and Converting Access Applications" The chapters in Part VII assume that you have no programming experience in any language. These chapters explain the principles of writing programming code in VBA. They also show you how to apply these principles to automate Access applications and work directly with ADO Recordset objects. XML Web services are destined to play a major role in sharing data between Office and other applications running on a variety of platforms. Thus, Part VII includes an advanced chapter that shows you how to integrate Web services in your Access applications. Part VII also supplies tips for converting Access 97 applications to Access 2003. Chapter 27, "Learning Visual Basic for Applications," introduces you to the VBA language with emphasis on using VBA to automate your Access front ends. The chapter describes how to write VBA code to create user-defined functions stored in modules and to write simple procedures that you activate directly from events. Chapter 28, "Handling Events with VBA 6.0," describes how to use VBA event-handling subprocedures in class modules to replace the macros used by earlier versions of Access. This chapter explains the events triggered by Access form, report, and control objects, and tells you how to use methods of the DoCmd object to respond to events, such as clicking a command button. Chapter 29, "Programming Combo and List Boxes," shows you how to take maximum advantage of Access 2003's unique combo and list boxes in decision-support applications. This chapter explains the VBA coding techniques for loading combo box lists and populating text and list boxes based on your combo box selections. Chapter 30, "Understanding Universal Data Access, OLE DB, and ADO," explains Microsoft's approach to Jet and SQL Server data connectivity in Office applications, describes how to migrate from Data Access Objects (DAO) to ActiveX Data Objects (DAO), and tells why this direction is important for your new Access projects. Chapter 31, "Consuming and Providing XML Web Services," shows you how to write the VBA code to create an XML Web service consumer (client) and how to use Microsoft's SQLXML 3.0 add-on to SQL Server 2000 to publish a Web service from a stored procedure. You learn how to use the Office Web Services Toolkit and its Web Service Reference tool to generate most of the VBA code for the client, which makes connecting to basic Web services practical for non-developers. Chapter 32, "Upgrading Access 97 and 2000/2002 Applications to Access 2003," tells you what changes you need to make when you convert your current 32-bit Access database applications and data access pages to Access 2003.
Glossary The glossary presents a descriptive list of the terms, abbreviations, and acronyms used in this book that you might not be familiar with and that can't be found in commonly used dictionaries. The Accompanying CD-ROM  | The CD-ROM that accompanies this book includes Access database files containing tables, forms, reports, HTML pages, VBA and VBScript code, and special files to complement design examples, and it shows you the expected result. An icon identifies sections that point to chapter files included on the accompanying CD-ROM. |
A very large (20MB) database, Oakmont.mdb, is included for optional use with some of the examples in this book. Oakmont University is a fictitious institution in Texas with 30,000 students and 2,300 employees. Databases with a large number of records in their tables are useful when designing applications to optimize performance, so the CD-ROM also includes a version of the Northwind.mdb database, NwindXL19.mdb, that has 21,096 records in the Orders table and 193,280 Order Details records. The CD-ROM also includes the following Visual Basic 6.0 utility programs: Crosstab Upsizer (Crosstab.exe) detects conventional and parameterized crosstab queries in Jet .mdb files and automatically generates SQL Server T-SQL stored procedures to emulate the output of the original crosstab query. User Login and Permissions Manager for MSDE 2000 (UserMan.exe) is a tool for establishing SQL Server logins and database user accounts, and assigning user roles for databases or applying specific user permissions to any database object. UserMan fills the MSDE management gap as a result of Microsoft Office Access 2000's SQL Server security management features from Access 2002 and later.
Installing the sample files with the Setup.exe application on the accompanying CD-ROM requires about 150MB of free disk space. |
