Microsoft Access 2013 Inside Out

850 Pages·2013·30.95 MB·English

by  Jeff Conrad

Microsoft Access 2013
Inside Out
Jeff Conrad
Published with the authorization of Microsoft Corporation by:
O’Reilly Media, Inc.
1005 Gravenstein Highway North
Sebastopol, California 95472
Copyright © 2013 by Jeff Conrad
All rights reserved. No part of the contents of this book may be reproduced or transmitted
in any form or by any means without the written permission of the publisher.
ISBN: 978-0-7356-7123-2
1 2 3 4 5 6 7 8 9 LSI 8 7 6 5 4 3
Printed and bound in the United States of America.
Microsoft Press books are available through booksellers and distributors worldwide. If you
need support related to this book, email Microsoft Press Book Support at mspinput@micro-
soft.com. Please tell us what you think of this book at http://www.microsoft.com/learning/
booksurvey.
Microsoft and the trademarks listed at http://www.microsoft.com/about/legal/en/us/Intellec-
tualProperty/Trademarks/EN-US.aspx are trademarks of the Microsoft group of companies.
All other marks are property of their respective owners.
The example companies, organizations, products, domain names, email addresses, logos,
people, places, and events depicted herein are fictitious. No association with any real com-
pany, organization, product, domain name, email address, logo, person, place, or event is
intended or should be inferred.
This book expresses the author’s views and opinions. The information contained in this
book is provided without any express, statutory, or implied warranties. Neither the authors,
O’Reilly Media, Inc., Microsoft Corporation, nor its resellers, or distributors will be held liable
for any damages caused or alleged to be caused either directly or indirectly by this book.
Acquisitions and Developmental Editor: Kenyon Brown
Production Editor: Christopher Hearse
Technical Reviewer: Andrew Couch
Copyeditor: Richard Carey
Indexer: BIM Publishing Services
Cover Design: Twist Creative • Seattle
Cover Composition: Ellie Volckhausen
Illustrator: Rebecca Demarest
For my wonderful wife, Cheryl, and for Amy, Aaron, and Arica.
Thank you for your love, support, and encouragement.
—Jeff Conrad

Contents at a glance
Part 1: Working with Access Services Part 2: Creating tables in a desktop
web apps database
Chapter 1 Chapter 9
What is Access? . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Exploring the Access 2013 desktop
database interface . . . . . . . . . . . . . . . . . . . . . . . 613
Chapter 2
Exploring the Access 2013 web app interface 21 Chapter 10
Designing tables in a desktop database . . . . 679
Chapter 3
Designing tables in a web app . . . . . . . . . . . . . . 83 Chapter 11
Modifying your table design . . . . . . . . . . . . . . 741
Chapter 4
Creating data macros in web apps . . . . . . . . . 173 Appendix
Installing your software . . . . . . . . . . . . . . . . . . 783
Chapter 5
Working with queries in web apps . . . . . . . . . 261
Chapter 6
Working with views and the web
browser experience . . . . . . . . . . . . . . . . . . . . . . 337
Chapter 7
Advanced view design . . . . . . . . . . . . . . . . . . . 453
Chapter 8
Automating a web app using macros . . . . . . . 541
v

Table of contents
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii
Part 1: Working with Access Services web apps
Chapter 1: What is Access? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
What is a database?...............................................................3
Relational databases.........................................................4
The architecture of Access....................................................5
Database capabilities ........................................................7
Access as an RDBMS ..............................................................7
Data definition and storage ..................................................8
Data manipulation ........................................................ 10
Data control.............................................................. 12
Access as an application development system...................................... 13
Deciding to move to database software . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
Extending the power of Access to the web......................................... 17
Chapter 2: Exploring the Access 2013 web app interface . . . . . . . . . . . . . . . . . . . . . . 21
Working with web apps ......................................................... 21
Opening Access for the first time . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
Getting started with Access 2013................................................. 27
Opening a web app template............................................... 28
Exploring the Microsoft Office Backstage view................................ 32
Taking Advantage of the Quick Access Toolbar................................ 43
Understanding the Office Fluent ribbon........................................... 45
Working with the Navigation pane ............................................... 46
Searching for web app objects ................................................... 49
Working in the web app design environment ...................................... 50
Add Tables screen......................................................... 50
Table Selector ............................................................ 52
App Home View .......................................................... 52
View Selector............................................................. 53
View preview window...................................................... 54
Viewing your web app in a web browser........................................... 54
Saving a web app as an app package ............................................. 55
Installing app packages ......................................................... 58
Uploading an app package to a SharePoint corporate catalog.................. 59
vii
viii Table of Contents
Installing app packages from a SharePoint corporate catalog................... 62
Installing apps from the SharePoint Store .................................... 66
Installing apps directly into a SharePoint site ................................. 72
Creating a blank Access web app............................................ 77
Downloading a web app into Access......................................... 79
Chapter 3: Designing tables in a web app . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83
Creating a new blank web app ................................................... 84
Creating tables using table templates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87
Starting with a blank table....................................................... 92
Defining fields in web apps................................................. 94
Understanding field data types in web apps.................................. 98
Setting field properties ................................................... 101
Completing the fields in the Vendors table.................................. 104
Creating calculated fields ................................................. 106
Defining field validation rules for web apps.................................. 113
Defining a table validation rule for web apps...................................... 117
Defining a primary key for web apps............................................. 120
Adding indexes................................................................ 121
Single-field indexes....................................................... 121
Multiple-field indexes..................................................... 123
Creating value list lookup fields in web apps...................................... 124
Working with data in preview datasheets......................................... 127
Creating relationships using lookup fields ........................................ 130
Defining a restrict delete relationship....................................... 132
Defining a cascade delete relationship...................................... 137
Importing and linking data into web apps ........................................ 139
Considerations for importing lookups ...................................... 140
Importing Access desktop database tables .................................. 142
Importing a spreadsheet.................................................. 150
Importing SQL tables..................................................... 155
Importing a text file ...................................................... 158
Importing a list from a SharePoint site...................................... 163
Linking a SharePoint list into a web app..................................... 167
Chapter 4: Creating data macros in web apps . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
Uses of data macros ........................................................... 174
Touring the Logic Designer ..................................................... 175
Working with table events...................................................... 178
Using On Insert events.................................................... 179
Using On Update events .................................................. 209
Using On Delete events................................................... 215
Deleting table events..................................................... 219
Working with named data macros............................................... 220
Creating named data macros.............................................. 220
Using parameters ........................................................ 223
Saving named data macros................................................ 230