Pro Access 2010 Development

659 Pages·2011·23.1 MB·English

by  Mark Collins

Pro Access 2010 Development Copyright © 2011 by Mark Collins All rights
reserved. No part of this work may be reproduced or transmitted in any form or
by any means, electronic or mechanical, including photocopying, recording, or
by any information storage or retrieval system, without the prior written
permission of the copyright owner and the publisher.
ISBN-13 (pbk): 978-1-43023578-1
ISBN-13 (electronic): 978-1-4302-3579-8
Trademarked names, logos, and images may appear in this book. Rather than use
a trademark symbol with every occurrence of a trademarked name, logo, or
image we use the names, logos, and images only in an editorial fashion and to
the benefit of the trademark owner, with no intention of infringement of the
trademark.
The use in this publication of trade names, trademarks, service marks, and
similar terms, even if they are not identified as such, is not to be taken as an
expression of opinion as to whether or not they are subject to proprietary rights.
President and Publisher: Paul Manning Lead Editor: Matthew Moodie
Technical Reviewer: Michael Mayberry Editorial Board: Steve Anglin, Mark
Beckner, Ewan Buckingham, Gary Cornell, Jonathan Gennick, Jonathan Hassell,
Michelle Lowman, James Markham, Matthew Moodie, Jeff Olson, Jeffrey
Pepper, Frank Pohlmann, Douglas Pundick, Ben Renow-Clarke, Dominic
Shakeshaft, Matt Wade, Tom Welsh Coordinating Editor: Corbin Collins Copy
Editor: Tracy Brown
Compositor: name?
Indexer: name?
Artist: name?
Cover Designer: Anna Ishchenko Distributed to the book trade worldwide by
Springer Science+Business Media, LLC., 233 Spring Street, 6th Floor, New
York, NY 10013. Phone 1-800-SPRINGER, fax [phone], e-mail
orders-
, or visit .
[email protected] www.springeronline.com
For information on translations, please e-mail , or visit
[email protected]
.
www.apress.com
Apress and friends of ED books may be purchased in bulk for academic,
corporate, or promotional use. eBook versions and licenses are also available for
most titles. For more information, reference our Special Bulk Sales–eBook
Licensing web page at
www.apress.com/bulk-sales.
The information in this book is distributed on an “as is” basis, without warranty.
Although every precaution has been taken in the preparation of this work,
neither the author(s) nor Apress shall have any liability to any person or entity
with respect to any loss or damage caused or alleged to be caused directly or
indirectly by the information contained in this work.
The source code for this book is available to readers at You will
www.apress.com.
need to answer questions pertaining to this book in order to successfully
download the code.
To my beautiful wife, Donna. You're the best thing this side of heaven.
Contents at a Glance
About the Author
About the Technical Reviewer
Acknowledgments
Preface
Part 1 Introduction
Chapter 1: Introduction
Part 2 Defining the Database Schema
Chapter 2: Defining and Relating Tables
Chapter 3: Using Data Macros
Chapter 4: Designing Queries
Chapter 5: Creating PivotTables
Part 3 Creating Forms and Reports
Chapter 6: Standard Forms
Chapter 7: Creating a CheckOut Form
Chapter 8: Creating a Customer Admin Form
Chapter 9: Enhancing Product Administration
Chapter 10: Enhancing the User Experience
Chapter 11: Branding with Themes and Styles
Chapter 12: Reports
Part 4 Multiuser Considerations
Chapter 13: Upsizing
Chapter 14: Distributing the Application
Chapter 15: Publishing to the Web
Part 5 Advanced Topics
Chapter 16: Integrating Outlook
Chapter 17: Using External Data
Chapter 18: Miscellaneous Features
Chapter 19: Security
Appendix A: Northwind Web Database
Index
Contents
About the Author
About the Technical Reviewer
Acknowledgments
Preface
Part 1 Introduction
Chapter 1: Introduction
What Is Access?
Defining the User Interface
Choosing the Application Architecture
Using This Book
The Sample Application
Getting Started
Part 2 Defining the Database Schema
Chapter 2: Defining and Relating Tables
Considering Design Practices
Using Primary Keys
Normalizing a Database
Database Constraints
Designing the Tables
Creating the Customer Table
Creating the Category Table
Creating the Media Table
Creating the Item Table
Creating the ItemInventory Table
Creating the Loan Table
Creating the Request Table
Viewing the Relationships
Summary
Chapter 3: Using Data Macros
Understanding Data Macros
Identifying Data Macro Limitations
Creating Your First Data Macro
Testing Your Macro
Exploring the Macro Editor
Understanding Data Blocks
Using Aliases
Using Data Actions
Navigating the Macro Editor
Implementing the Loan Before Change Event
Making Sure the Item is Available
Calculating the Due Date
Calculating the Late Fee
Validating Renewals
Adding the Current Loan Reference
Adding the Lookup Field
Modifying the Loan After Insert Event
Modifying the Loan After Update Event
Handling Requested Items
Implementing the Before Change Event
Creating a Named Data Macro
Calling a Named Macro
Computing Overdue Fees
Creating a Named Data Macro to Compute Overdue Fees
Creating a User-Executed Macro
Debugging Data Macros
Using the LogEvent Action
Viewing the Application Log
Testing the Application
Summary
Chapter 4: Designing Queries
Creating Select Queries
Creating a Simple Query
Adding Tables
Using Joins
Making Additional Changes
Using Queries as Views
Creating Action Queries
Creating the AddInventoryItem Query
Enhancing the Request Feature
Creating a Crosstab Query
Building the AllLoans Query
Designing the LoanSummary Query
Summary
Chapter 5: Creating PivotTables
Slicing, Dicing, and Drilling
Creating a PivotTable View
Understanding the PivotTable Layout
Using the PivotTable Fields
Defining the Column Field
Using Date Fields
Adding a Filter Field
Refreshing the PivotTable
Adding Other Fields
Including Multiple Values
Using Calculated Columns
Adding Additional Column Fields
Creating a Field Hierarchy
Using the PivotChart View
Configuring a PivotChart View
Changing the Chart Type
Exporting a PivotTable View to Excel
Summary
Part 3 Creating Forms and Reports
Chapter 6: Standard Forms
Creating a Single Form
Using the Form Wizard
Using the Available Views
Sorting the Records