深入 access 2010.pdf

发布时间：2022-06-24 发布人：admin 分类：说明书资料大小：28.59M 资料格式：pdf 举报版权申诉

u011711528-8806171-4744302543376593896.pdf-第1页.png

第1页 / 共1397页

u011711528-8806171-4744302543376593896.pdf-第2页.png

第2页 / 共1397页

u011711528-8806171-4744302543376593896.pdf-第3页.png

第3页 / 共1397页

u011711528-8806171-4744302543376593896.pdf-第4页.png

第4页 / 共1397页

u011711528-8806171-4744302543376593896.pdf-第5页.png

第5页 / 共1397页

u011711528-8806171-4744302543376593896.pdf-第6页.png

第6页 / 共1397页

u011711528-8806171-4744302543376593896.pdf-第7页.png

第7页 / 共1397页

u011711528-8806171-4744302543376593896.pdf-第8页.png

第8页 / 共1397页

Introduction [This content is currently in development.]

Getting Acquainted with Access 2010

Access 2010 for Access 2007 Users: What’s New [This content is currently in development.]

Building Simple Tracking Applications [This content is currently in development.]

Navigating the Fluent User Interface [This content is currently in development.]

Learning the Fundamentals of Access Databases

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

Working with Access Databases and Tables

Understanding Access Database Files

Creating a New Access Database from Scratch

Exploring the Properties of Tables and Fields

Choosing Field Data Types, Sizes, and Formats

Adding a Table to the Northwind Traders Sample Database

Preparing to Add a Table Related to the Employees 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

Entering, Editing, and Validating Access Table Data

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

Sorting, Finding, and Filtering Data

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

Linking, Importing, and Exporting Data

Importing and Linking Spreadsheet Files

Working with Microsoft Outlook and Exchange Folders

Importing Text Files

Working with Tables in Other Database File Formats

Using the Linked Table Manager Add-in to Relink Tables

Using the Access Mail Merge Wizard

Exporting Tables to Word, Excel, PDF, and XPS Files

Exporting Table Data as Text Files

Exporting Data in Other File Formats

Transforming Data with Queries and PivotTables

Designing Queries for Access Databases

Trying the Simple Query Wizard

Using the Query Design Window

Testing the Other Query Wizards

Creating Other Types of Queries

Understanding Access Query Operators and Expressions

Understanding the Elements of Expressions

Operators

Literals

Identifiers

Functions

Intrinsic and Named Constants

Creating Access Expressions

Creating Multitable and Crosstab Queries

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

Working with PivotTable and PivotChart Views

Slicing and Dicing Data with PivotTables

Setting PivotTable Property Values

Exporting the PivotTable to Excel

Optimizing Performance of PivotTables

Formatting and Manipulating PivotCharts

Creating and Updating Access Tables 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

Designing Forms and Reports

Creating and Using Access Forms

Autogenerating a Basic Transaction-Processing Form

Form Layout View’s Contextual Ribbons

Rearranging the Default Form Layout

Changing Form View from a Tabbed Document to a Modal Pop-up Window

Creating a Master/Child Form with the Form Wizard

Generating Multiple Items and Split Forms

Using Transaction-Processing Forms

Modifying the Properties of a Form or Control After Testing

Designing Custom Multitable Forms

Getting Acquainted with Form Design View’s Contextual Ribbons

Working in Form Design View

Adding Label and Text Box Controls to a Blank Form

Adding Group Boxes with the Wizard

Using the Clipboard to Copy Controls to Another Form

Adding Combo and List Boxes

Adding an Attachment Control for Images

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

Using the New Navigation Control and Application Parts

Using the Web Browser Control

Overriding the Field Properties of Tables

Adding Page Headers and Footers for Printing Forms

Working with Simple Reports and Mailing Labels

Categorizing Types of Access Reports

Autogenerating a Simple Tabular Report

Creating a Grouped Report with the Report Wizard

Using Access’s Report Ribbons

The Print Preview Ribbon

Modifying a Basic Wizard Report

Changing the Report’s Record Source and Adding Calculated Controls

Adjusting Margins and Printing Conventional Reports

Preventing Widowed Records with the Group Keep Together Property

Printing Multicolumn Reports as Mailing Labels

Preparing Advanced Reports

Grouping and Sorting Report Data

Working from a Blank Report

Incorporating Subreports

Customizing De Novo Reports

Mailing Reports as Attachments

Adding Graphs, PivotCharts, and PivotTables

Generating Graphs and Charts with Microsoft Graph

Printing Graphs or Charts in Reports

Creating a Linked Graph from an Access Crosstab Query

Working with PivotChart Forms

Substituting or Adding a PivotTable in a Form

Programming Databases with Macros

Automating Access Applications with Macros

What Are Access Macros?

How Do I View a Macro’s Actions?

Why Use Macros Instead of VBA?

Exploring Access 2010’s Event Repertoire

Generating Embedded Macros with the Command Button Wizard

Responding to Events from Combo and List Boxes

Exploring Access 2010’s Macro-based Switchboard Manager

Emulating Table Triggers with Access Data Macros

Designing Simple Event-Driven Data Macros

Writing and Invoking Named Data Macros

Collaborating with Access Data

Linking Access Front Ends to Access and Client/Server Tables

Evaluating the Benefits of Migrating to Client/Server Databases

Choosing a Client/Server Migration Strategy

Upsizing a Single-File Application to SQL Server 2005 or Later

Moving the Upsized Database to Another Server

Linking Client/Server Tables Manually

Writing and Executing Pass-through Queries

Adding SQL Server User Logins with SQL Server Management Studio

Password-Protecting and Encrypting Access Front Ends

Collaborating with Windows SharePoint Foundation 2010

Installing SPF 2010 Under 64-Bit Windows Server 2008

Getting Acquainted with SPF 2010

Managing Data with Access and SharePoint 2010

Saving an Existing Access Database in a SharePoint Document Library

Moving Tables and Saving an Existing Database to SharePoint

Working Offline and Synchronizing Lists

Exporting Tables or Queries to a SharePoint List

Linking a SharePoint List to an Access Table

Sharing Web Databases with SharePoint Server 2010

Licensing SharePoint Server 2010

Understanding the Role of Access Services

Installing SharePoint Server 2010

Creating a Web Database from a Template

Making Design Changes to Web Databases

Working with a Hosted SharePoint Site

Working with HTML and XML Documents

Importing and Exporting Web Pages

Importing or Linking Data from HTML Tables

Importing HTML Lists to Access Tables

Fixing Source Content Before Importing with HTML Tidy

Exporting Access Tables to HTML Files

Exporting Reports to HTML Tables

Modifying Page Layout with HTML Templates

Upgrading Access HTML Documents to HTML 4.01 and CSS

Converting HTML 4.01 Files to XHTML 1.0 with Tidy

Gathering Data by Email with HTML Forms

Integrating with XML and InfoPath 2010

Gaining an XML Vocabulary

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 and Web Pages

Importing XML Data to Tables

Exporting and Importing Data in Related Tables

Gathering Data by Email with InfoPath 2010 Forms

Creating Access Front Ends to SQL Server Databases

Exploring Access Data Projects and SQL Server 2008

Understanding the Role of SQL Server and ADP

Exploring the NorthwindSQL Sample Project

Working with SQL Server Tables in the Project Designer

Exploring SQL Server Views

Taking Advantage of Inline 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

Designing Forms and Reports with SQL Server Data Sources

Securing Your Project as an .ade File

Moving from Access Queries to Transact-SQL

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

Upsizing Access Applications to Access Data Projects and SQL Azure [This content is currently in development.]

Programming and Converting Access Applications

Learning Visual Basic for Applications

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

Handling Events with VBA and Macros

Understanding the Role of Class Modules

Examining Project Class Module Members in Object Browser and Project Explorer

Using Functions to Respond to Events

Working with Access 2010’s DoCmd Methods

Customizing Applications with Ribbon Objects

Referring to Access Objects with VBA

Responding to Data Events Triggered by Forms and Controls

Programming Combo and List Boxes

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

Understanding Data Access Objects, OLE DB, and ADO

Comparing DAO and ADO Objects

Creating DAO.Recordset2 and ADODB.Recordset Objects

Using the Object Browser to Display DAO and ADODB Properties, Methods, and Events

Working with the ADODB.Connection Object

Using the ADODB.Command Object

Understanding the ADODB.Recordset Object

Exploring the AddOrders.adp Sample Project

Upgrading Access 2003 and Earlier Applications to Access 2010

Reviewing Upgrade Pros and Cons

Converting Unsecured Files from Access 9x to 200x

Converting Secure Access 9x Files to 200x

Upgrading Access 200x Files to Access 2010

Moving from MSDE to the SQL Server 2005 or Later Express Edition

Changing the Database Owner and Setting Database Compatibility Level

Appendix

What Was New in Access 2007 for Users of Access 2003 and Earlier

What Was New in Microsoft Office Access 2007: An Overview

The Office 2007 Ribbon User Interface

Security, Trusted Locations, Packages and Certificates

The Navigation Pane

Changes to Tables and the Access Database Engine

Application Development by Templates

New Form and Report Features

Access Macros Redux

Collaboration with SharePoint

Features Missing from Access 2007

SQL Server 2005 Express Edition SP2 Setup

Glossary

Table of Contents Copyright................................................................................................................................ 1 Introduction [This content is currently in development.]....................................................... 2 Part I: Getting Acquainted with Access 2010........................................................................... 3 Chapter 1. Access 2010 for Access 2007 Users: What’s New [This content is currently in development.].................................................................................. 4 Chapter 2. Building Simple Tracking Applications [This content is currently in development.].................................................................................................. 5 Chapter 3. Navigating the Fluent User Interface [This content is currently in development.]..................................................................................................... 6 Part II: Learning the Fundamentals of Access Databases........................................................ 7 Chapter 4. Exploring Relational Database Theory and Practice.................................................................................................................................................... 8 Moving from Spreadsheets to Databases.................................................................................................................................................................................... 8 Reliving Database History........................................................................................................................................................................................................... 9 Defining the Structure of Relational Databases........................................................................................................................................................................ 12 Conforming to Table Design Rules............................................................................................................................................................................................ 17 Choosing Primary Key Codes.................................................................................................................................................................................................... 23 Maintaining Data Integrity and Accuracy................................................................................................................................................................................. 25 Displaying Data with Queries and Views.................................................................................................................................................................................. 28 Chapter 5. Working with Access Databases and Tables............................................................................................................................................................... 34 Understanding Access Database Files....................................................................................................................................................................................... 34 Creating a New Access Database from Scratch......................................................................................................................................................................... 36 Exploring the Properties of Tables and Fields.......................................................................................................................................................................... 38 Choosing Field Data Types, Sizes, and Formats....................................................................................................................................................................... 47 Adding a Table to the Northwind Traders Sample Database.................................................................................................................................................... 61 Preparing to Add a Table Related to the Employees Table...................................................................................................................................................... 62 Setting Default Values of Fields................................................................................................................................................................................................. 74 Working with Relations, Key Fields, and Indexes.................................................................................................................................................................... 76 Altering Fields and Relationships............................................................................................................................................................................................. 87 Working with Object Dependencies and Access Smart Tags.................................................................................................................................................... 91 Copying and Pasting Tables...................................................................................................................................................................................................... 99 Using the Table Analyzer Wizard............................................................................................................................................................................................. 101 Generating a Data Dictionary with the Database Documenter............................................................................................................................................... 107 Chapter 6. Entering, Editing, and Validating Access Table Data................................................................................................................................................ 115 Using Keyboard Operations for Entering and Editing Data.................................................................................................................................................... 115 Adding Records to a Table....................................................................................................................................................................................................... 122 Selecting, Appending, Replacing, and Deleting Table Records.............................................................................................................................................. 124 Validating Data Entry............................................................................................................................................................................................................... 125 Adding Records to the HRActions Table................................................................................................................................................................................. 134 Testing Field and Table Validation Rules................................................................................................................................................................................ 136 Troubleshooting....................................................................................................................................................................................................................... 138 Chapter 7. Sorting, Finding, and Filtering Data......................................................................................................................................................................... 140 Sorting Table Data................................................................................................................................................................................................................... 140 Finding Matching Records in a Table...................................................................................................................................................................................... 145 Replacing Matched Field Values Automatically...................................................................................................................................................................... 148 Filtering Table Data................................................................................................................................................................................................................. 149 Applying Advanced Filters and Sort Orders............................................................................................................................................................................ 163 Customizing Table Datasheet View.......................................................................................................................................................................................... 173 Copying, Exporting, and Mailing Sorted and Filtered Data.................................................................................................................................................... 175 Chapter 8. Linking, Importing, and Exporting Data................................................................................................................................................................... 177 Importing and Linking Spreadsheet Files............................................................................................................................................................................... 178 Working with Microsoft Outlook and Exchange Folders........................................................................................................................................................ 190 Importing Text Files................................................................................................................................................................................................................. 199 Working with Tables in Other Database File Formats........................................................................................................................................................... 209 Using the Linked Table Manager Add-in to Relink Tables..................................................................................................................................................... 218 Using the Access Mail Merge Wizard...................................................................................................................................................................................... 220 Exporting Tables to Word, Excel, PDF, and XPS Files........................................................................................................................................................... 235 Exporting Table Data as Text Files......................................................................................................................................................................................... 238 Exporting Data in Other File Formats.................................................................................................................................................................................... 240 Part III: Transforming Data with Queries and PivotTables................................................. 242 Chapter 9. Designing Queries for Access Databases.................................................................................................................................................................. 243 Trying the Simple Query Wizard............................................................................................................................................................................................. 243 Using the Query Design Window............................................................................................................................................................................................ 254 Testing the Other Query Wizards............................................................................................................................................................................................ 274 Creating Other Types of Queries............................................................................................................................................................................................. 279 Chapter 10. Understanding Access Query Operators and Expressions..................................................................................................................................... 286 Understanding the Elements of Expressions.......................................................................................................................................................................... 288 Operators................................................................................................................................................................................................................................. 289 Literals..................................................................................................................................................................................................................................... 293 Identifiers................................................................................................................................................................................................................................. 294 Functions................................................................................................................................................................................................................................. 295 Intrinsic and Named Constants............................................................................................................................................................................................... 310 Creating Access Expressions.................................................................................................................................................................................................... 311 Microsoft Access 2010 In Depth. Microsoft Access 2010 In Depth, ISBN: 9780768695229 Prepared for dwhite@nofearzx10.com, david white Copyright © 2010 by Que Publishing. This download file is made available for personal use only and is subject to the Terms of Service. Any other use requires prior written consent from the copyright owner. Unauthorized use, reproduction and/or distribution are strictly prohibited and violate applicable laws. All rights reserved.

Chapter 11. Creating Multitable and Crosstab Queries.............................................................................................................................................................. 332 Joining Tables to Create Multitable Queries........................................................................................................................................................................... 332 Using Lookup Fields in Tables................................................................................................................................................................................................ 358 Adding Subdatasheets to a Table or Query............................................................................................................................................................................. 378 Outer, Self, and Theta Joins.................................................................................................................................................................................................... 387 Updating Table Data with Queries.......................................................................................................................................................................................... 396 Making All Fields of Tables Accessible................................................................................................................................................................................... 404 Making Calculations on Multiple Records.............................................................................................................................................................................. 404 Designing Parameter Queries.................................................................................................................................................................................................. 413 Creating Crosstab Queries....................................................................................................................................................................................................... 419 Writing UNION Queries and Subqueries................................................................................................................................................................................ 435 Creating Queries from Tables in Other Databases................................................................................................................................................................. 440 Chapter 12. Working with PivotTable and PivotChart Views..................................................................................................................................................... 444 Slicing and Dicing Data with PivotTables............................................................................................................................................................................... 444 Setting PivotTable Property Values......................................................................................................................................................................................... 466 Exporting the PivotTable to Excel........................................................................................................................................................................................... 468 Optimizing Performance of PivotTables................................................................................................................................................................................. 470 Formatting and Manipulating PivotCharts............................................................................................................................................................................. 475 Chapter 13. Creating and Updating Access Tables with Action Queries.................................................................................................................................... 483 Creating New Tables with Make-Table Queries..................................................................................................................................................................... 483 Creating Action Queries to Append Records to a Table......................................................................................................................................................... 494 Deleting Records from a Table with an Action Query............................................................................................................................................................ 496 Updating Values of Multiple Records in a Table..................................................................................................................................................................... 501 Testing Cascading Deletion and Cascading Updates.............................................................................................................................................................. 514 Part IV: Designing Forms and Reports................................................................................ 519 Chapter 14. Creating and Using Access Forms........................................................................................................................................................................... 520 Autogenerating a Basic Transaction-Processing Form.......................................................................................................................................................... 520 Form Layout View’s Contextual Ribbons................................................................................................................................................................................ 523 Rearranging the Default Form Layout.................................................................................................................................................................................... 528 Changing Form View from a Tabbed Document to a Modal Pop-up Window....................................................................................................................... 532 Creating a Master/Child Form with the Form Wizard........................................................................................................................................................... 554 Generating Multiple Items and Split Forms........................................................................................................................................................................... 563 Using Transaction-Processing Forms..................................................................................................................................................................................... 565 Modifying the Properties of a Form or Control After Testing................................................................................................................................................ 568 Chapter 15. Designing Custom Multitable Forms....................................................................................................................................................................... 570 Getting Acquainted with Form Design View’s Contextual Ribbons....................................................................................................................................... 570 Working in Form Design View................................................................................................................................................................................................ 576 Adding Label and Text Box Controls to a Blank Form........................................................................................................................................................... 584 Adding Group Boxes with the Wizard..................................................................................................................................................................................... 596 Using the Clipboard to Copy Controls to Another Form........................................................................................................................................................ 602 Adding Combo and List Boxes................................................................................................................................................................................................ 603 Adding an Attachment Control for Images............................................................................................................................................................................. 620 Working with Tab Controls..................................................................................................................................................................................................... 623 Optimizing the Form’s Design................................................................................................................................................................................................ 628 Adding a History Subform to a Tab Control Page.................................................................................................................................................................. 630 Adding New Records in the HRActionEntry Form................................................................................................................................................................. 636 Using the New Navigation Control and Application Parts..................................................................................................................................................... 638 Using the Web Browser Control.............................................................................................................................................................................................. 641 Overriding the Field Properties of Tables............................................................................................................................................................................... 642 Adding Page Headers and Footers for Printing Forms........................................................................................................................................................... 643 Chapter 16. Working with Simple Reports and Mailing Labels................................................................................................................................................. 645 Categorizing Types of Access Reports..................................................................................................................................................................................... 647 Autogenerating a Simple Tabular Report............................................................................................................................................................................... 648 Creating a Grouped Report with the Report Wizard.............................................................................................................................................................. 650 Using Access’s Report Ribbons............................................................................................................................................................................................... 660 The Print Preview Ribbon....................................................................................................................................................................................................... 665 Modifying a Basic Wizard Report............................................................................................................................................................................................ 667 Changing the Report’s Record Source and Adding Calculated Controls................................................................................................................................ 675 Adjusting Margins and Printing Conventional Reports......................................................................................................................................................... 688 Preventing Widowed Records with the Group Keep Together Property................................................................................................................................ 691 Printing Multicolumn Reports as Mailing Labels................................................................................................................................................................... 693 Chapter 17. Preparing Advanced Reports................................................................................................................................................................................... 706 Grouping and Sorting Report Data......................................................................................................................................................................................... 706 Working from a Blank Report.................................................................................................................................................................................................. 714 Incorporating Subreports........................................................................................................................................................................................................ 725 Customizing De Novo Reports................................................................................................................................................................................................. 731 Mailing Reports as Attachments............................................................................................................................................................................................. 736 Chapter 18. Adding Graphs, PivotCharts, and PivotTables........................................................................................................................................................ 738 Generating Graphs and Charts with Microsoft Graph............................................................................................................................................................ 738 Printing Graphs or Charts in Reports...................................................................................................................................................................................... 761 Creating a Linked Graph from an Access Crosstab Query...................................................................................................................................................... 766 Working with PivotChart Forms.............................................................................................................................................................................................. 777 Substituting or Adding a PivotTable in a Form...................................................................................................................................................................... 794 Microsoft Access 2010 In Depth. Microsoft Access 2010 In Depth, ISBN: 9780768695229 Prepared for dwhite@nofearzx10.com, david white Copyright © 2010 by Que Publishing. This download file is made available for personal use only and is subject to the Terms of Service. Any other use requires prior written consent from the copyright owner. Unauthorized use, reproduction and/or distribution are strictly prohibited and violate applicable laws. All rights reserved.

Part V: Programming Databases with Macros..................................................................... 796 Chapter 19. Automating Access Applications with Macros........................................................................................................................................................ 797 What Are Access Macros?........................................................................................................................................................................................................ 797 How Do I View a Macro’s Actions?.......................................................................................................................................................................................... 799 Why Use Macros Instead of VBA?........................................................................................................................................................................................... 801 Exploring Access 2010’s Event Repertoire............................................................................................................................................................................. 802 Generating Embedded Macros with the Command Button Wizard...................................................................................................................................... 805 Responding to Events from Combo and List Boxes................................................................................................................................................................ 812 Exploring Access 2010’s Macro-based Switchboard Manager............................................................................................................................................... 819 Chapter 20. Emulating Table Triggers with Access Data Macros.............................................................................................................................................. 826 Designing Simple Event-Driven Data Macros........................................................................................................................................................................ 828 Writing and Invoking Named Data Macros............................................................................................................................................................................ 842 Part VI: Collaborating with Access Data.............................................................................. 856 Chapter 21. Linking Access Front Ends to Access and Client/Server Tables............................................................................................................................. 857 Linking Access Front Ends to Access and Client/Server Tables............................................................................................................................................. 857 Evaluating the Benefits of Migrating to Client/Server Databases......................................................................................................................................... 866 Choosing a Client/Server Migration Strategy......................................................................................................................................................................... 869 Upsizing a Single-File Application to SQL Server 2005 or Later............................................................................................................................................ 871 Moving the Upsized Database to Another Server................................................................................................................................................................... 881 Linking Client/Server Tables Manually.................................................................................................................................................................................. 884 Writing and Executing Pass-through Queries........................................................................................................................................................................ 890 Adding SQL Server User Logins with SQL Server Management Studio................................................................................................................................ 892 Password-Protecting and Encrypting Access Front Ends...................................................................................................................................................... 902 Chapter 22. Collaborating with Windows SharePoint Foundation 2010.................................................................................................................................. 908 Installing SPF 2010 Under 64-Bit Windows Server 2008..................................................................................................................................................... 909 Getting Acquainted with SPF 2010.......................................................................................................................................................................................... 911 Managing Data with Access and SharePoint 2010................................................................................................................................................................. 923 Saving an Existing Access Database in a SharePoint Document Library............................................................................................................................... 923 Moving Tables and Saving an Existing Database to SharePoint............................................................................................................................................ 927 Working Offline and Synchronizing Lists............................................................................................................................................................................... 948 Exporting Tables or Queries to a SharePoint List.................................................................................................................................................................. 950 Linking a SharePoint List to an Access Table......................................................................................................................................................................... 953 Chapter 23. Sharing Web Databases with SharePoint Server 2010........................................................................................................................................... 956 Licensing SharePoint Server 2010.......................................................................................................................................................................................... 956 Understanding the Role of Access Services............................................................................................................................................................................ 959 Installing SharePoint Server 2010.......................................................................................................................................................................................... 960 Creating a Web Database from a Template............................................................................................................................................................................. 967 Making Design Changes to Web Databases............................................................................................................................................................................ 977 Working with a Hosted SharePoint Site................................................................................................................................................................................. 984 Part VII: Working with HTML and XML Documents........................................................... 994 Chapter 24. Importing and Exporting Web Pages...................................................................................................................................................................... 995 Importing or Linking Data from HTML Tables...................................................................................................................................................................... 995 Importing HTML Lists to Access Tables............................................................................................................................................................................... 1006 Fixing Source Content Before Importing with HTML Tidy.................................................................................................................................................. 1010 Exporting Access Tables to HTML Files................................................................................................................................................................................ 1013 Exporting Reports to HTML Tables....................................................................................................................................................................................... 1016 Modifying Page Layout with HTML Templates..................................................................................................................................................................... 1019 Upgrading Access HTML Documents to HTML 4.01 and CSS............................................................................................................................................. 1021 Converting HTML 4.01 Files to XHTML 1.0 with Tidy......................................................................................................................................................... 1025 Gathering Data by Email with HTML Forms........................................................................................................................................................................ 1026 Chapter 25. Integrating with XML and InfoPath 2010............................................................................................................................................................ 1033 Gaining an XML Vocabulary................................................................................................................................................................................................. 1033 Exporting Tables and Queries to XML and HTML............................................................................................................................................................... 1037 Reformatting HTML Tables and Adding Page Elements...................................................................................................................................................... 1043 Deploying Exported XML File Sets to a Web Server............................................................................................................................................................ 1046 Exporting Static Reports as XML and Web Pages................................................................................................................................................................ 1047 Importing XML Data to Tables............................................................................................................................................................................................. 1050 Exporting and Importing Data in Related Tables................................................................................................................................................................. 1054 Gathering Data by Email with InfoPath 2010 Forms........................................................................................................................................................... 1056 Part VIII: Creating Access Front Ends to SQL Server Databases........................................ 1063 Chapter 26. Exploring Access Data Projects and SQL Server 2008......................................................................................................................................... 1064 Understanding the Role of SQL Server and ADP.................................................................................................................................................................. 1066 Exploring the NorthwindSQL Sample Project...................................................................................................................................................................... 1068 Working with SQL Server Tables in the Project Designer.................................................................................................................................................... 1070 Exploring SQL Server Views.................................................................................................................................................................................................. 1078 Taking Advantage of Inline Functions.................................................................................................................................................................................. 1084 Examining Stored Procedures............................................................................................................................................................................................... 1088 Diagramming Table Relationships........................................................................................................................................................................................ 1095 Backing Up and Restoring Databases................................................................................................................................................................................... 1096 Transferring the Project’s Database to a Server.................................................................................................................................................................... 1099 Connecting to a Remote SQL Server Database..................................................................................................................................................................... 1100 Designing Forms and Reports with SQL Server Data Sources.............................................................................................................................................. 1102 Microsoft Access 2010 In Depth. Microsoft Access 2010 In Depth, ISBN: 9780768695229 Prepared for dwhite@nofearzx10.com, david white Copyright © 2010 by Que Publishing. This download file is made available for personal use only and is subject to the Terms of Service. Any other use requires prior written consent from the copyright owner. Unauthorized use, reproduction and/or distribution are strictly prohibited and violate applicable laws. All rights reserved.

Securing Your Project as an .ade File..................................................................................................................................................................................... 1102 Chapter 27. Moving from Access Queries to Transact-SQL...................................................................................................................................................... 1104 Understanding SQL Grammar............................................................................................................................................................................................... 1105 Writing SELECT Queries in SQL........................................................................................................................................................................................... 1106 Writing Action Queries and Stored Procedures..................................................................................................................................................................... 1124 Working with Tables in Another Database............................................................................................................................................................................ 1129 Creating Tables with ANSI-92 DDL....................................................................................................................................................................................... 1131 Using SQL Statements with Forms, Reports, and Controls.................................................................................................................................................. 1132 Chapter 28. Upsizing Access Applications to Access Data Projects and SQL Azure [This content is currently in development.]......................................... 1134 Part IX: Programming and Converting Access Applications............................................... 1135 Chapter 29. Learning Visual Basic for Applications.................................................................................................................................................................. 1136 Getting Acquainted with VBA 6.0.......................................................................................................................................................................................... 1136 Controlling Program Flow...................................................................................................................................................................................................... 1154 Handling Runtime Errors....................................................................................................................................................................................................... 1159 Exploring the VBA Editor....................................................................................................................................................................................................... 1161 Examining the Utility Functions Module............................................................................................................................................................................... 1165 Using Text Comparison Options............................................................................................................................................................................................ 1169 Chapter 30. Handling Events with VBA and Macros................................................................................................................................................................ 1170 Understanding the Role of Class Modules............................................................................................................................................................................. 1170 Examining Project Class Module Members in Object Browser and Project Explorer.......................................................................................................... 1176 Using Functions to Respond to Events.................................................................................................................................................................................. 1178 Working with Access 2010’s DoCmd Methods...................................................................................................................................................................... 1180 Customizing Applications with Ribbon Objects.................................................................................................................................................................... 1183 Referring to Access Objects with VBA................................................................................................................................................................................... 1193 Responding to Data Events Triggered by Forms and Controls............................................................................................................................................. 1197 Chapter 31. Programming Combo and List Boxes.................................................................................................................................................................... 1203 Constraining Query Choices with Combo Boxes................................................................................................................................................................... 1203 Adding Code to Create the Query’s SQL Statement.............................................................................................................................................................. 1210 Drilling Down from a List Box Selection................................................................................................................................................................................ 1213 Adding New Features to List and Combo Boxes.................................................................................................................................................................... 1217 Converting Your Combo Box Form to an Access Data Project............................................................................................................................................. 1222 Chapter 32. Understanding Data Access Objects, OLE DB, and ADO..................................................................................................................................... 1229 Comparing DAO and ADO Objects........................................................................................................................................................................................ 1230 Creating DAO.Recordset2 and ADODB.Recordset Objects.................................................................................................................................................. 1231 Using the Object Browser to Display DAO and ADODB Properties, Methods, and Events................................................................................................. 1241 Working with the ADODB.Connection Object....................................................................................................................................................................... 1241 Using the ADODB.Command Object..................................................................................................................................................................................... 1250 Understanding the ADODB.Recordset Object...................................................................................................................................................................... 1259 Exploring the AddOrders.adp Sample Project...................................................................................................................................................................... 1272 Chapter 33. Upgrading Access 2003 and Earlier Applications to Access 2010....................................................................................................................... 1276 Reviewing Upgrade Pros and Cons........................................................................................................................................................................................ 1276 Converting Unsecured Files from Access 9x to 200x............................................................................................................................................................ 1277 Converting Secure Access 9x Files to 200x........................................................................................................................................................................... 1282 Upgrading Access 200x Files to Access 2010........................................................................................................................................................................ 1283 Moving from MSDE to the SQL Server 2005 or Later Express Edition............................................................................................................................... 1286 Changing the Database Owner and Setting Database Compatibility Level.......................................................................................................................... 1290 Part X: Appendix............................................................................................................... 1293 Appendix A. What Was New in Access 2007 for Users of Access 2003 and Earlier................................................................................................................ 1294 What Was New in Microsoft Office Access 2007: An Overview........................................................................................................................................... 1294 The Office 2007 Ribbon User Interface................................................................................................................................................................................ 1298 Security, Trusted Locations, Packages and Certificates........................................................................................................................................................ 1321 The Navigation Pane.............................................................................................................................................................................................................. 1323 Changes to Tables and the Access Database Engine............................................................................................................................................................. 1328 Application Development by Templates................................................................................................................................................................................ 1334 New Form and Report Features............................................................................................................................................................................................ 1334 Access Macros Redux............................................................................................................................................................................................................. 1338 Collaboration with SharePoint.............................................................................................................................................................................................. 1339 Features Missing from Access 2007...................................................................................................................................................................................... 1340 SQL Server 2005 Express Edition SP2 Setup........................................................................................................................................................................ 1341 Appendix B. Glossary................................................................................................................................................................................................................. 1353 Microsoft Access 2010 In Depth. Microsoft Access 2010 In Depth, ISBN: 9780768695229 Prepared for dwhite@nofearzx10.com, david white Copyright © 2010 by Que Publishing. This download file is made available for personal use only and is subject to the Terms of Service. Any other use requires prior written consent from the copyright owner. Unauthorized use, reproduction and/or distribution are strictly prohibited and violate applicable laws. All rights reserved.

Microsoft Access 2010 In Depth. Microsoft Access 2010 In Depth, ISBN: 9780768695229 Prepared for dwhite@nofearzx10.com, david white Copyright © 2010 by Que Publishing. This download file is made available for personal use only and is subject to the Terms of Service. Any other use requires prior written consent from the copyright owner. Unauthorized use, reproduction and/or distribution are strictly prohibited and violate applicable laws. All rights reserved. xvCopyrightCopyright © 2011 by Que Publishing

Licensed by david white 2369869 Microsoft Access 2010 In Depth. Microsoft Access 2010 In Depth, ISBN: 9780768695229 Prepared for dwhite@nofearzx10.com, david white Copyright © 2010 by Que Publishing. This download file is made available for personal use only and is subject to the Terms of Service. Any other use requires prior written consent from the copyright owner. Unauthorized use, reproduction and/or distribution are strictly prohibited and violate applicable laws. All rights reserved. 1Introduction [This content is currentlyin development.]This content is currently in development.

分享到：

赞收藏

资料库

深入 access 2010.pdf

相关推荐

数据库

热门标签

最新资料