Cover image for Pro Access 2010 Development

Pro Access 2010 Development

Author Mark Collins
Release Date: 2011/06/01
ISBN: 9781430235781
Topic:
0%
30
Chapters
0-1
Hours read
0k
Total Words
    Start Reading Now    
       Add to Wishlist       
View table of contents

Book Description

Pro Access 2010 Development is a fundamental resource for developing business applications that take advantage of the features of Access 2010 and the many sources of data available to your business. In this book, you'll learn how to build database applications, create Web-based databases, develop macros and VBA tools for Access applications, integrate Access with SharePoint and other business systems, and much more.

Using a practical, hands-on approach, this book will take you through all the facets of developing Access-based solutions, such as data modeling, complex form development, and UI customizations. You'll then deploy your solution to the web and integrate it with other external data sources. This book is full of handy tricks to help you get the most out of what Access has to offer, including its comprehensive set of features and tools for collecting, using, and acting on business data, whether your data is in Access or stored on another platform. You'll also see how to smoothly integrate your applications with SQL Server databases and other Office programs, such as Outlook.

What you'll learn

  • How to build business applications that integrate local data, web-generated data, and data available from SQL Server and other sources

  • How to develop VBA routines and macros to enhance the automation of your database

  • How to deploy databases to your users and "web-ify" your application for access from anywhere

  • How to secure access to your data and maintain its integrity

  • How to integrate Access with SharePoint and other business applications

Who this book is for

This book is for intermediate to advanced Access developers who want to get up to speed with the new features available in Access 2010. Experienced Access users who want to begin developing code-based applications and project solutions in Access will also find this book useful.

Table of Contents

  1. Copyright
  2. About the Author
  3. About the Technical Reviewer
  4. Acknowledgments
  5. Preface
  6. I. Introduction
    1. 1. Introduction
      1. 1.1. What Is Access?
        1. 1.1.1. Defining the User Interface
        2. 1.1.2. Choosing the Application Architecture
      2. 1.2. Using This Book
      3. 1.3. The Sample Application
      4. 1.4. Getting Started
  7. II. Defining the Database Schema
    1. 2. Defining and Relating Tables
      1. 2.1. Considering Design Practices
        1. 2.1.1. Using Primary Keys
        2. 2.1.2. Normalizing a Database
        3. 2.1.3. Database Constraints
      2. 2.2. Designing the Tables
        1. 2.2.1. Creating the Customer Table
          1. 2.2.1.1. Using Quick Start Fields
          2. 2.2.1.2. Adding Search Fields
          3. 2.2.1.3. Using an Input Mask
          4. 2.2.1.4. Adding Customers
        2. 2.2.2. Creating the Category Table
          1. 2.2.2.1. Making a Field Required
          2. 2.2.2.2. Adding a Unique Constraint
          3. 2.2.2.3. Defining Categories
        3. 2.2.3. Creating the Media Table
          1. 2.2.3.1. Adding Field Validation
          2. 2.2.3.2. Entering a Field Caption
          3. 2.2.3.3. Using Default Values
          4. 2.2.3.4. Defining Media Types
        4. 2.2.4. Creating the Item Table
          1. 2.2.4.1. Creating Lookup Columns
          2. 2.2.4.2. Adding the MediaID Column
          3. 2.2.4.3. Viewing the Relationships
          4. 2.2.4.4. Adding the Remaining Columns
          5. 2.2.4.5. Adding a Calculated Field
          6. 2.2.4.6. Adding Items to the Database
        5. 2.2.5. Creating the ItemInventory Table
          1. 2.2.5.1. Adding a Lookup to the Item Table
          2. 2.2.5.2. Adding a Lookup with Fixed Options
          3. 2.2.5.3. Specifying the Item's Condition
          4. 2.2.5.4. Adding InventoryItem Records
        6. 2.2.6. Creating the Loan Table
          1. 2.2.6.1. Referencing the Customer Table
          2. 2.2.6.2. Referencing the InventoryItem table
          3. 2.2.6.3. Adding the Remaining Fields
        7. 2.2.7. Creating the Request Table
          1. 2.2.7.1. Defining the Status Field
          2. 2.2.7.2. Adding the InventoryItemID Field
      3. 2.3. Viewing the Relationships
      4. 2.4. Summary
    2. 3. Using Data Macros
      1. 3.1. Understanding Data Macros
        1. 3.1.1. Identifying Data Macro Limitations
        2. 3.1.2. Creating Your First Data Macro
        3. 3.1.3. Testing Your Macro
      2. 3.2. Exploring the Macro Editor
        1. 3.2.1. Understanding Data Blocks
        2. 3.2.2. Using Aliases
          1. 3.2.2.1. Using Default Aliases
          2. 3.2.2.2. Using the EditRecord Action
          3. 3.2.2.3. When to Use an Alias
        3. 3.2.3. Using Data Actions
        4. 3.2.4. Navigating the Macro Editor
      3. 3.3. Implementing the Loan Before Change Event
        1. 3.3.1. Making Sure the Item is Available
        2. 3.3.2. Calculating the Due Date
        3. 3.3.3. Calculating the Late Fee
        4. 3.3.4. Validating Renewals
      4. 3.4. Adding the Current Loan Reference
        1. 3.4.1. Adding the Lookup Field
        2. 3.4.2. Modifying the Loan After Insert Event
        3. 3.4.3. Modifying the Loan After Update Event
      5. 3.5. Handling Requested Items
        1. 3.5.1. Implementing the Before Change Event
        2. 3.5.2. Creating a Named Data Macro
        3. 3.5.3. Calling a Named Macro
      6. 3.6. Computing Overdue Fees
        1. 3.6.1. Creating a Named Data Macro to Compute Overdue Fees
        2. 3.6.2. Creating a User-Executed Macro
      7. 3.7. Debugging Data Macros
        1. 3.7.1. Using the LogEvent Action
        2. 3.7.2. Viewing the Application Log
      8. 3.8. Testing the Application
      9. 3.9. Summary
    3. 4. Designing Queries
      1. 4.1. Creating Select Queries
        1. 4.1.1. Creating a Simple Query
        2. 4.1.2. Adding Tables
        3. 4.1.3. Using Joins
          1. 4.1.3.1. Understanding Multiplicity
          2. 4.1.3.2. Using the Join Properties
          3. 4.1.3.3. Adding Columns
        4. 4.1.4. Making Additional Changes
          1. 4.1.4.1. Adding Calculated Columns
          2. 4.1.4.2. Sorting the Results
          3. 4.1.4.3. Using the SQL View
        5. 4.1.5. Using Queries as Views
      2. 4.2. Creating Action Queries
        1. 4.2.1. Creating the AddInventoryItem Query
          1. 4.2.1.1. Changing the Query Type
          2. 4.2.1.2. Adding a Parameter
          3. 4.2.1.3. Running the Query
        2. 4.2.2. Enhancing the Request Feature
          1. 4.2.2.1. Modifying the Request Table
          2. 4.2.2.2. Creating the CancelOldRequests Query
          3. 4.2.2.3. Adding a Data Macro
          4. 4.2.2.4. Running the Query
      3. 4.3. Creating a Crosstab Query
        1. 4.3.1. Building the AllLoans Query
        2. 4.3.2. Designing the LoanSummary Query
      4. 4.4. Summary
    4. 5. Creating PivotTables
      1. 5.1. Slicing, Dicing, and Drilling
      2. 5.2. Creating a PivotTable View
        1. 5.2.1. Understanding the PivotTable Layout
        2. 5.2.2. Using the PivotTable Fields
        3. 5.2.3. Defining the Column Field
        4. 5.2.4. Using Date Fields
        5. 5.2.5. Adding a Filter Field
        6. 5.2.6. Refreshing the PivotTable
      3. 5.3. Adding Other Fields
        1. 5.3.1. Including Multiple Values
        2. 5.3.2. Using Calculated Columns
        3. 5.3.3. Adding Additional Column Fields
        4. 5.3.4. Creating a Field Hierarchy
      4. 5.4. Using the PivotChart View
        1. 5.4.1. Configuring a PivotChart View
        2. 5.4.2. Changing the Chart Type
      5. 5.5. Exporting a PivotTable View to Excel
      6. 5.6. Summary
  8. III. Creating Forms and Reports
    1. 6. Standard Forms
      1. 6.1. Creating a Single Form
        1. 6.1.1. Using the Form Wizard
        2. 6.1.2. Using the Available Views
        3. 6.1.3. Sorting the Records
      2. 6.2. Using Split Forms
        1. 6.2.1. Generating the Media Form
        2. 6.2.2. Modifying the Form Fields
      3. 6.3. Using Continuous Forms
        1. 6.3.1. Generating the InventoryItem Form
        2. 6.3.2. Designing the InventoryItem Form
        3. 6.3.3. Modifying the Form Fields
      4. 6.4. Understanding the Layout Options
        1. 6.4.1. Using the Tabular Layout Option
        2. 6.4.2. Using the Datasheet Layout Option
        3. 6.4.3. Using the Columnar Layout Option
        4. 6.4.4. Using the Justified Layout Option
        5. 6.4.5. Using a Layout
      5. 6.5. Creating the Item Form
        1. 6.5.1. Using the Standard Form Template
        2. 6.5.2. Arranging the Form's Layout
        3. 6.5.3. Adding a Subform
      6. 6.6. Summary
    2. 7. Creating a CheckOut Form
      1. 7.1. Implementing a Customer Search Feature
        1. 7.1.1. Populating the Customer Table
        2. 7.1.2. Creating the CustomerSearch Dialog Box
          1. 7.1.2.1. Adding the Detail Fields
          2. 7.1.2.2. Adding the Search Fields
          3. 7.1.2.3. Implementing the Search Logic
        3. 7.1.3. Building a CustomerDisplay Form
        4. 7.1.4. Creating the CheckOut Form
          1. 7.1.4.1. Adding the Customer Controls
          2. 7.1.4.2. Connecting the Controls
          3. 7.1.4.3. Invoking the Search Dialog
      2. 7.2. Providing a CheckOut Feature
        1. 7.2.1. Building an InventoryItemLookup Form
          1. 7.2.1.1. Creating an Ad-Hoc Query
          2. 7.2.1.2. Adding the Fields to the Form
          3. 7.2.1.3. Modifying the Form Design
          4. 7.2.1.4. Finalizing the Form Details
        2. 7.2.2. Linking the InventoryItemLookup Subform
        3. 7.2.3. Designing the CheckOut Details
          1. 7.2.3.1. Altering the Loan Table
          2. 7.2.3.2. Creating a LoanDetail Query
          3. 7.2.3.3. Designing the Detail Section
          4. 7.2.3.4. Adding the Header and Footer Controls
        4. 7.2.4. Implementing the CheckOut Logic
          1. 7.2.4.1. Enabling the CheckOut Button
          2. 7.2.4.2. Implementing the CheckOut Button
          3. 7.2.4.3. Implementing the Complete Button
      3. 7.3. Testing the Application
      4. 7.4. Summary
    3. 8. Creating a Customer Admin Form
      1. 8.1. Building the Customer Profile Tab
        1. 8.1.1. Creating a CustomerUpdate Form
        2. 8.1.2. Configuring the Form Controls
        3. 8.1.3. Creating the CustomerAdmin Form
          1. 8.1.3.1. Designing the Form Header
          2. 8.1.3.2. Using a Tab Control
          3. 8.1.3.3. Designing the Profile Page
          4. 8.1.3.4. Implementing the Connecting Code
        4. 8.1.4. Testing the Profile Page
      2. 8.2. Building the Items on Loan Tab
        1. 8.2.1. Enhancing the LoanDetails Query
        2. 8.2.2. Designing a CustomerLoan Form
          1. 8.2.2.1. Creating the Initial CustomerLoans Form
          2. 8.2.2.2. Arranging the Controls
          3. 8.2.2.3. Modifying the Form Layout
        3. 8.2.3. Configuring the Datasheet View
        4. 8.2.4. Designing the Items on Loan Page
        5. 8.2.5. Connecting the Pieces
        6. 8.2.6. Testing the Page
      3. 8.3. Building the Loan History Tab
      4. 8.4. Summary
    4. 9. Enhancing Product Administration
      1. 9.1. Using Data-Bound Images
        1. 9.1.1. Image Support in Access
          1. 9.1.1.1. Storing Bitmaps in Access
          2. 9.1.1.2. Storing OLE Objects
          3. 9.1.1.3. Storing Images in the File System
        2. 9.1.2. Adding a Picture to the Item Table
        3. 9.1.3. Modifying the Item Form
        4. 9.1.4. Displaying an Image
          1. 9.1.4.1. Adding the Code to Display an Image
          2. 9.1.4.2. Loading the Image Files
      2. 9.2. Implementing Item Search
        1. 9.2.1. Importing Item Data
        2. 9.2.2. Designing the Search Form
          1. 9.2.2.1. Designing the Basic Search Page
          2. 9.2.2.2. Designing the Detail Section
          3. 9.2.2.3. Adding the VBA Code
        3. 9.2.3. Testing the Search Function
        4. 9.2.4. Using Conditional Formatting
        5. 9.2.5. Invoking the ItemSearch Form
      3. 9.3. Enhancing the Item Form
        1. 9.3.1. Adding an Inventory Item
        2. 9.3.2. Adding a Web Browser
        3. 9.3.3. Using Page Breaks
        4. 9.3.4. Modifying the CustomerLoan Form
      4. 9.4. Summary
    5. 10. Enhancing the User Experience
      1. 10.1. Form Navigation
        1. 10.1.1. Creating the Menu Form
        2. 10.1.2. Auto-Loading the Menu Form
      2. 10.2. Ribbon Navigation
        1. 10.2.1. Implementing a Sample Ribbon Tab
        2. 10.2.2. Displaying the System Objects
      3. 10.3. Building a Custom Ribbon
        1. 10.3.1. Designing the Tables
        2. 10.3.2. Creating the Menu Forms
        3. 10.3.3. Populating the Menu Tables
        4. 10.3.4. Using MSO Images
        5. 10.3.5. Implementing the Custom Ribbon
          1. 10.3.5.1. Designing the mnuCommands Query
          2. 10.3.5.2. Generating the Ribbon XML
          3. 10.3.5.3. Implementing the Callback Methods
          4. 10.3.5.4. Using the Autoexec Macro
          5. 10.3.5.5. Selecting the Library Ribbon
      4. 10.4. Locking Down the Database
        1. 10.4.1. Removing Navigation
        2. 10.4.2. Compiling the Database
      5. 10.5. Summary
    6. 11. Branding with Themes and Styles
      1. 11.1. Using Office Themes
        1. 11.1.1. Understanding Office Themes
          1. 11.1.1.1. Creating a Custom Color Theme
          2. 11.1.1.2. Defining the Font Scheme
          3. 11.1.1.3. Creating an Office Theme
        2. 11.1.2. Applying an Office Theme
        3. 11.1.3. Making Other Visual Adjustments
      2. 11.2. Using Graphics
        1. 11.2.1. Adding a Banner Graphic
        2. 11.2.2. Using a Background Image
      3. 11.3. Summary
    7. 12. Reports
      1. 12.1. Exploring Access Reports
        1. 12.1.1. Understanding Report Sections
          1. 12.1.1.1. Using Page Sections
          2. 12.1.1.2. Using Group Sections
        2. 12.1.2. Options for Creating Reports
      2. 12.2. Creating a Simple Report
        1. 12.2.1. Modifying the Page Setup
        2. 12.2.2. Modifying the Report Layout
      3. 12.3. Creating the AllLoans Report
        1. 12.3.1. Using the Report Wizard
          1. 12.3.1.1. Selecting the Data Source
          2. 12.3.1.2. Grouping Records
          3. 12.3.1.3. Sorting and Summarizing the Details
          4. 12.3.1.4. Selecting the Format
        2. 12.3.2. Exploring the Design View
        3. 12.3.3. Using the Layout View
        4. 12.3.4. Configuring Grouping and Sorting
        5. 12.3.5. Using Unbound Controls
          1. 12.3.5.1. Formatting Page Numbers
          2. 12.3.5.2. Displaying the Current Date and Time
          3. 12.3.5.3. Using Totals and Subtotals
          4. 12.3.5.4. Fixing the Percentage Formula
      4. 12.4. Creating a CheckOut Report
        1. 12.4.1. Adding the Data-Bound Controls
        2. 12.4.2. Formatting the Report Header
        3. 12.4.3. Adding a Subreport
        4. 12.4.4. Modifying the CheckOut Form
      5. 12.5. Generating InventoryItem Labels
        1. 12.5.1. Creating an InventoryItemDetail Query
        2. 12.5.2. Using the Label Wizard
          1. 12.5.2.1. Selecting the Label Stock Template
          2. 12.5.2.2. Formatting the Labels
        3. 12.5.3. Modifying the Color Scheme
      6. 12.6. Auto-Generating a DailyLoans Report
        1. 12.6.1. Creating the DailyLoans Report
        2. 12.6.2. Creating a DailyReport Macro
        3. 12.6.3. Creating a Scheduled Task
        4. 12.6.4. Modifying the Daily Report Macro
      7. 12.7. Summary
  9. IV. Multiuser Considerations
    1. 13. Upsizing
      1. 13.1. Understanding Upsizing
        1. 13.1.1. Using Linked Tables
        2. 13.1.2. Project Overview
      2. 13.2. Upsizing with Access
      3. 13.3. Upsizing with SQL Server
        1. 13.3.1. Using the Upsizing Wizard
        2. 13.3.2. Viewing the Upsizing Results
        3. 13.3.3. Adjusting the OpenRecordset Code
      4. 13.4. Upsizing with SQL Azure
        1. 13.4.1. Installing SQL Server Migration Assistant
        2. 13.4.2. Configuring a SQL Azure Database
          1. 13.4.2.1. Setting Up a SQL Azure Database Server
          2. 13.4.2.2. Creating a User Account
        3. 13.4.3. Migrating the Data to SQL Azure
        4. 13.4.4. Viewing the Migration Results
        5. 13.4.5. Linking the Views
      5. 13.5. Data Macros with Linked Tables
        1. 13.5.1. Data Macros in a Split Access Database
        2. 13.5.2. Data Macros in Linked SQL Server Tables
      6. 13.6. Summary
    2. 14. Distributing the Application
      1. 14.1. Using the Access Runtime
        1. 14.1.1. Simulating the Access Runtime
        2. 14.1.2. Understanding the Filename Extensions
        3. 14.1.3. Downloading the Access Runtime
      2. 14.2. Modifying the Application
        1. 14.2.1. Supporting Images in a Distributed Environment
        2. 14.2.2. Modifying the Application
        3. 14.2.3. Relinking the Linked Tables
        4. 14.2.4. Creating an Executable File
      3. 14.3. Creating an Installation Package
        1. 14.3.1. Installing the Package Solution Wizard
        2. 14.3.2. Using the Package Solution Wizard
        3. 14.3.3. Deploying the Installation File
      4. 14.4. Summary
    3. 15. Publishing to the Web
      1. 15.1. Preparing to Publish
        1. 15.1.1. Updating the Data Macros
        2. 15.1.2. Using the Compatibility Checker
      2. 15.2. Publishing the Access Database
        1. 15.2.1. Publishing to Access Services
        2. 15.2.2. Exploring the SharePoint Site
        3. 15.2.3. Exploring the Access Database
        4. 15.2.4. Restoring the VBA Code
        5. 15.2.5. Fixing the Default Value
        6. 15.2.6. Using Web Databases
      3. 15.3. Creating Web Forms
        1. 15.3.1. Creating a Category Web Form
        2. 15.3.2. Creating a Media Web Form
        3. 15.3.3. Creating a Customer List Web Form
        4. 15.3.4. Creating a Navigation Form
        5. 15.3.5. Setting the Default Web Form
      4. 15.4. Adding an Item Web Form
        1. 15.4.1. Creating the Initial Form
        2. 15.4.2. Creating the InventoryItem Subform
        3. 15.4.3. Adding the Picture and Web Browser
          1. 15.4.3.1. Storing Images in SharePoint
          2. 15.4.3.2. Displaying the Item Picture
          3. 15.4.3.3. Adding the Web Browser Control
        4. 15.4.4. Adding an InventoryItem Record
          1. 15.4.4.1. Creating the Data Macro
          2. 15.4.4.2. Adding a Command Button
        5. 15.4.5. Providing a Search Feature
        6. 15.4.6. Modifying the Navigation Form
      5. 15.5. Summary
  10. V. Advanced Topics
    1. 16. Integrating Outlook
      1. 16.1. Sending E-mails
        1. 16.1.1. Creating the OverdueItems Query
        2. 16.1.2. Creating a Data Macro
        3. 16.1.3. Adding a UI Macro
        4. 16.1.4. Sending the E-mails
      2. 16.2. Sending E-mails with VBA
        1. 16.2.1. Creating the OverdueItemDetails Query
        2. 16.2.2. Implementing the VBA Code
        3. 16.2.3. Sending the E-mails
      3. 16.3. Using Data Collection
        1. 16.3.1. Understanding Data Collection
        2. 16.3.2. Defining a Data Collection E-mail
          1. 16.3.2.1. Creating the Results Table
          2. 16.3.2.2. Installing the Data Collection Add-In
          3. 16.3.2.3. Setting up Data Collection
        3. 16.3.3. Processing the Replies
        4. 16.3.4. Resending the E-mail
      4. 16.4. Summary
    2. 17. Using External Data
      1. 17.1. Linking and Importing
      2. 17.2. Linking a SharePoint List
        1. 17.2.1. Creating a Team SharePoint Site
        2. 17.2.2. Creating a Linked Table
        3. 17.2.3. Using the Linked Tables
      3. 17.3. Linking an Outlook Folder
        1. 17.3.1. Modifying the Data File Name
        2. 17.3.2. Creating the Linked Table
        3. 17.3.3. Using the Linked Table
      4. 17.4. Importing an XML File
        1. 17.4.1. Understanding the XML Import Process
        2. 17.4.2. Importing the Exchange Rates
      5. 17.5. Summary
    3. 18. Miscellaneous Features
      1. 18.1. Using Timers
        1. 18.1.1. Adding a Digital Clock
        2. 18.1.2. Creating Simple Animation
      2. 18.2. Calling the Windows API
      3. 18.3. Using TempVars
        1. 18.3.1. Creating the UserName TempVar
        2. 18.3.2. Storing the CheckOut User
      4. 18.4. Displaying HTML Content
        1. 18.4.1. Modifying the Item Form
        2. 18.4.2. Passing HTML Content to a Web Browser Control
      5. 18.5. Adding a Chart
      6. 18.6. Summary
    4. 19. Security
      1. 19.1. Using Trusted Documents
        1. 19.1.1. Understanding Disabled Content
        2. 19.1.2. Trusting a Document
          1. 19.1.2.1. Using Trusted Locations
          2. 19.1.2.2. Configuring Trusted Documents
      2. 19.2. Signing a Database
        1. 19.2.1. Creating a Certificate
        2. 19.2.2. Package an Access Database
        3. 19.2.3. Installing a Signed Database
      3. 19.3. Configuring Sandbox Mode
      4. 19.4. Encrypting an Access Database
      5. 19.5. Summary
    5. A. Northwind Web Database
      1. A.1. Installing the Northwind Web Template
        1. A.1.1. Downloading the Template
        2. A.1.2. Publishing the Database
      2. A.2. Understanding the Sample Database
        1. A.2.1. Setting Up Your Employee Record
        2. A.2.2. Exploring the Access Client Application
3.147.104.120