Microsoft Office Access 2007 All-in-One Desk Reference For Dummies E-Book

Alan Simpson

• Herausgeber: John Wiley & Sons
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch

Seitenzahl: 1020

Veröffentlichungsjahr: 2011

Beschreibung

* Updated to cover all the latest features and capabilities of Access 2007, this resource provides new and inexperienced Access users with eight task-oriented minibooks that cover begininning to advanced-level material * Each minibook covers a specific aspect of Access, such as database design, tables, queries, forms, reports, and macros * Shows how to accomplish specific tasks such as database housekeeping, security data, and using Access with the Web * Access is the world's leading desktop database solution and is used by millions of people to store, organize, view, analyze, and share data, as well as to build powerful, custom database solutions that integrate with the Web and enterprise data sources

Leseprobe

Microsoft® Office Access™ 2007 All-in-One Desk Reference For Dummies®

by Alan Simpson, Margaret Levine Young, Alison Barrows, April Wells, Jim McCarter

Microsoft® Office Access™ 2007 All-in-One Desk Reference For Dummies®

Published byWiley Publishing, Inc.111 River St.Hoboken, NJ 07030-5774www.wiley.com

Copyright © 2007 by Wiley Publishing, Inc., Indianapolis, Indiana

Published by Wiley Publishing, Inc., Indianapolis, Indiana

Published simultaneously in Canada

No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, [phone], fax [phone]. Requests to the Publisher for permission should be addressed to the Legal Department, Wiley Publishing, Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, [phone], fax [phone], or online at http://www.wiley.com/go/permissions.

Trademarks: Wiley, the Wiley Publishing logo, For Dummies, the Dummies Man logo, A Reference for the Rest of Us!, The Dummies Way, Dummies Daily, The Fun and Easy Way, Dummies.com, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates in the United States and other countries, and may not be used without written permission. All other trademarks are the property of their respective owners. Wiley Publishing, Inc., is not associated with any product or vendor mentioned in this book.

LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: The publisher and the author make no representations or warranties with respect to the accuracy or completeness of the contents of this work and specifically disclaim all warranties, including without limitation warranties of fitness for a particular purpose. No warranty may be created or extended by sales or promotional materials. The advice and strategies contained herein may not be suitable for every situation. This work is sold with the understanding that the publisher is not engaged in rendering legal, accounting, or other professional services. If professional assistance is required, the services of a competent professional person should be sought. Neither the publisher nor the author shall be liable for damages arising herefrom. The fact that an organization or Website is referred to in this work as a citation and/or a potential source of further information does not mean that the author or the publisher endorses the information the organization or Website may provide or recommendations it may make. Further, readers should be aware that Internet Websites listed in this work may have changed or disappeared between when this work was written and when it is read.

For general information on our other products and services, please contact our Customer Care Department within the U.S. at 800-762-2974, outside the U.S. at 317-572-3993, or fax [phone].

For technical support, please visit www.wiley.com/techsupport.

Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic books.

Library of Congress Control Number: 2006925890

ISBN-13: 978-0-470-03649-5

ISBN-10: 0-470-03649-4

Manufactured in the United States of America

10 9 8 7 6 5 4 3 2 1

1B/QW/RS/QW/IN

About the Authors

Alan Simpson is the author of over 115 computer books on all sorts of topics: Windows, databases, Web-site design and development, programming, and network administration. His books are published throughout the world, in over a dozen languages, and have sold millions of copies. Though definitely in the techno-geek category, we let him contribute anyway because sometimes people like that come in handy.

Margaret Levine Young has co-authored several dozen computer books about the Internet, UNIX, WordPerfect, Access, and (stab from the past) PC-File and Javelin, including The Internet For Dummies (Wiley) and Windows XP Home Edition: The Complete Reference (Osborne/McGraw-Hill). She met her future husband Jordan in the R.E.S.I.S.T.O.R.S., a high-school computer club before there were high-school computer clubs. Her other passions are her children, music, Unitarian Universalism (www.uua.org), reading, and anything to do with cooking or eating.

Alison Barrows has authored or co-authored books on Windows, the Internet, Microsoft Access, WordPerfect, Lotus 1-2-3, and other topics. In addition to writing books, Alison writes and edits technical documentation and training material. In real life she hangs out with her “guys” — Parker, 6, and Mason, 4, and Evan 2 — and tries to carve out some time to practice yoga. Alison lives with her family in central Massachusetts.

April Wells is a graduate of the University of Pittsburgh and holds an MBA from West Texas A&M. She is a database administrator with expertise in a wide variety of enterprise database software programs, including Oracle, DB2, MySQL, and Access. She is the author of several books and white papers on database software and is a frequent public speaker, trainer, and consultant.

Dedication

To Susan, Ashley, and Alec, as always. (AS)

To Matt, Parker, Mason, and Evan. (AB)

To the three people who are always there for me, always support me, and never let me down — my family, Larry, Adam, and Amandya. (AW)

Authors’ Acknowledgments

We would like to acknowledge the care of Kyle Looper, Chris Morris, and Barry Childs-Helton, and all the others who shepherded this book through the editing and production process, as well as all the folks listed on the Publisher’s Acknowledgements page who worked on this book.

Alison thanks Dotty, Annie, and Matt for taking great care of my little guys so I can get work done. Matt (also known as Honey) gets special thanks as my hardware guru.

Publisher’s Acknowledgments

We’re proud of this book; please send us your comments through our online registration form located at www.dummies.com/register/.

Some of the people who helped bring this book to market include the following:

Acquisitions, Editorial, and Media Development

Senior Project Editor: Christopher Morris

Acquisitions Editor: Kyle Looper

Senior Copy Editor: Barry Childs-Helton

Technical Editor: Dan DiNicolo

Editorial Manager: Kevin Kirschner

Media Development Manager: Laura VanWinkle

Editorial Assistant: Amanda Foxworth

Senior Editorial Assistant: Cherie Case

Cartoons: Rich Tennant (www.the5thwave.com)

Composition Services

Project Coordinator: Kristie Rees

Layout and Graphics: Claudia Bell, Carl Byers, Stephanie D. Jumper, Barbara Moore, Barry Offringa, Lynsey Osborn, Heather Ryan, Rashell Smith, Erin Zeltner

Proofreaders: John Greenough, Susan Moritz, Jennifer Stanley

Indexer: Steve Rath

Anniversary Logo Design: Richard Pacifico

Special Help Linda Morris

Publishing and Editorial for Technology Dummies

Richard Swadley, Vice President and Executive Group Publisher

Andy Cummings, Vice President and Publisher

Mary Bednarek, Executive Acquisitions Director

Mary C. Corder, Editorial Director

Publishing for Consumer Dummies

Diane Graves Steele, Vice President and Publisher

Joyce Pepple, Acquisitions Director

Composition Services

Gerry Fahey, Vice President of Production Services

Debbie Stailey, Director of Composition Services

Contents

Title

Introduction

About Access 2007 All-in-One Desk Reference For Dummies

Conventions

Foolish Assumptions

What You Don’t Have to Read

Icons

Organization

Where to Go from Here

Book I : Essential Concepts

Chapter 1: Introducing Access 2007

The Six Types of Access Objects

Essential Database Concepts

Chapter 2: Getting Started, Getting Around

Running Access

Opening a Database

Playing with the Access Sample Databases

The Access Navigation Bar, Ribbon, and File menu

Mission Control: The Navigation Pane

Creating, Deleting, Renaming, Copying, and Printing Objects

Using Wizards

Getting Help

Saving Time with Keyboard Shortcuts

Chapter 3: Creating a Database from Templates

Finding Templates

Exploring a Template

Modifying Objects

Using a Template Database

Chapter 4: Designing Your Database the Relational Way

What Are Tables, Fields, and Keys?

What Are Relationships?

Designing a Database

Tips for Choosing Field Types

Storing Single Facts

Creating a Database

Book II : Tables

Chapter 1: Creating and Modifying Tables

About Table Views

Saving Your Table

Creating Tables for Your Data

Refining Your Table Using Design View

Formatting Fields with Field Properties

Defining the Primary Key

Indexing Fields

Printing Table Designs

Chapter 2: Entering and Editing Data in Datasheets

Looking at a Datasheet

Navigating the Data

Adding and Editing Records

Entering and Editing Hyperlinks

Using the Attachment Data Type

Checking Your Spelling

Using AutoCorrect for Faster Data Entry

Formatting a Datasheet

Taking Advantage of Subdatasheets

Adding a Totals Row to the Datasheet

Chapter 3: Sorting, Finding, and Filtering Data

Sorting the Rows of a Datasheet

Finding (and Replacing) Data

Filtering a Datasheet

Filtering Using Advanced Filter/Sort

Chapter 4: Importing and Exporting Data

Cutting, Copying, and Pasting

Importing or Linking to Data

Getting Data from Another Access Database

Getting Data Out of Access

Collecting Data with Outlook

Chapter 5: Avoiding “Garbage In, Garbage Out”

Finding the Right Tool to Keep Garbage Out

Using Input Masks to Validate and Format Data

Creating a Lookup Field

Validating Data As It’s Entered

Chapter 6: Relating Your Tables and Protecting Your Data

Creating Relationships and Protecting Your Data with Referential Integrity

Referential Integrity with Many-to-Many Relationships

Printing the Relationships Window

Book III : Queries

Chapter 1: Creating Select Queries

Types of Queries

Creating a Query in Design View

Creating a Query with the Simple Query Wizard

Viewing Your Query

Understanding Design View

Tips for Creating a Query

Editing a Query

Limiting Records with Criteria Expressions

Working with Multiple Related Tables

Working with Query Datasheets

Saving Queries

Chapter 2: Letting Queries Do the Math

Doing Math in Queries

Writing Expressions in Access

Using the Expression Builder

Going Beyond Basic Arithmetic

Date and Time Calculations

Manipulating Text with Expressions

Writing Decision-Making Expressions

Testing for Empty Fields

Creating Flexible Parameter Queries

Totals, Subtotals, Averages, and Such

Chapter 3: Doing Neat Things with Action Queries and Query Wizards

Creating Action Queries

Changing Data with Update Queries

Creating New Tables with Make-Table Queries

Moving Data from One Table to Another with Append Queries

Deleting Lots of Records with Delete Queries

Finding Unmatched Records with a Wizard

Finding Duplicate Records

Chapter 4: Viewing Your Data from All Angles Using Crosstabs and PivotTables

Aggregating Data in a Crosstab Query

Analyzing Data with PivotTables

Book IV : Forms

Chapter 1: Designing and Using Forms (and Reports)

Forms and Reports Are Secretly Related

Creating Forms with AutoForms and Wizards

Viewing a Form

Creating Forms (and Reports) in Design View

Configuring the Whole Form or Report

Sizing Forms

Storing Your Forms and Reports

Editing Data Using Forms

Chapter 2: Jazzing Up Your Forms (and Reports)

Taking Control of Your Form or Report

Making Controls That Display Text

Displaying Number, Currency, and Date Fields

Moving, Renaming, Resizing, Deleting, and Copying Controls

Formatting Your Text

Creating Check Boxes for Yes/No Fields

Neatening Up Your Controls

Adding Lines, Boxes, and Backgrounds

Controlling Cursor Movement in Your Form

Chapter 3: Creating Smarter Forms

Creating and Configuring Combo and List Boxes

Cool Looks for Yes/No Fields

Creating Option Groups

Creating Command Buttons

Making a Close Button

Adding and Linking Subforms

Adding Form Headers and Footers

Creating Tabbed Forms

You Can’t Type That Here!

Making Switchboards — A Friendly Face for Your Database

Chapter 4: Doing Calculations in Forms (and Reports)

Doing Elementary Calculations

Calculating and Formatting Numbers

Calculating and Formatting Dates

Calculating and Formatting Text

Displaying Values That Depend on Conditions

Adding Subtotals and Totals from Subforms

Formatting Calculated Controls

Book V : Reports

Chapter 1: Creating and Spiffing Up Reports

If You Know Forms, You Already Know Reports

Creating Reports Automagically

Editing Reports in Design View

Report Sections and How They Work

Formatting Tips and Tricks

Copying Forms to Reports

Adding and Formatting Subreports

Displaying Empty or Long Fields

Displaying Fields That May Be Empty

Creating Mailing Labels

Chapter 2: Printing Beautiful Reports

Viewing Your Report

Formatting the Page

Printing the Report

Sending a Report to Another Application

Chapter 3: Creating Charts and Graphs from Your Data

Displaying Information with Charts and Graphs

Analyzing Your Data Graphically with PivotCharts

Book VI : Macros: Automating Stuff in Access

Chapter 1: Making Macros Do the Work

What Is a Macro?

Creating and Editing Macros

Running Macros

Opening Databases That Contain Macros

Attaching Macros to Forms

Chapter 2: Making Macros Smarter

Only Run This If I Say So

Changing the Way Your Form Looks Dynamically

Displaying Forms and Datasheets

Setting Up Your Own Main Menu Form

Book VII : Database Administration

Chapter 1: Database Housekeeping

Compacting and Repairing Your Database

Making Backups

Converting Databases

Analyzing and Documenting Your Database

Loading and Managing Add-Ins

Locking Up Your Database as an ACCDE File

Chapter 2: Sharing the Fun — and the Database: Managing Multiuser Access

Putting Your Database Where They Can See It

Splitting Your Database into a Front End and a Back End

Putting Your Favorite Objects into Groups

Editing with Multiple Users

Chapter 3: Securing Your Access Database

Windows Security

Setting Startup Options

Password-Protecting Your Database

Granting Database Access to Specific Users

Book VIII : Programming in VBA

Chapter 1: What the Heck Is VBA?

Finding VBA Code

Enabling VBA Code

Using the Visual Basic Editor

Discovering Code as You Go

Chapter 2: Writing Code

How VBA Works

VBA Syntax

Declaring Module Options

Writing Your Own VBA Procedures

Typing and Editing in the Code Window

Testing and Running Your Code

Chapter 3: Writing Smarter Code

Creating Variables and Constants

Making Decisions in VBA Code

Executing the Same Code Repeatedly

Using Custom Functions

Chapter 4: Controlling Forms with VBA

Displaying Custom Messages

Opening Forms with DoCmd

Changing Form Controls with VBA

Understanding Objects and Collections

Chapter 5: Using SQL and Recordsets

Recordsets and Object Models

SQL and Recordsets

Running Action Queries from VBA

Chapter 6: Debugging Your Code

Considering Types of Program Errors

Fixing Compiler Errors

Trapping Runtime Errors

Dealing with Logical Errors

Appendix: Installing Microsoft Access

Activating Access

Repair, Reinstall, or Uninstall Access

: Further Reading

Book I

Essential Concepts

Chapter 1: Introducing Access 2007

In This Chapter

Getting a handle on Microsoft Access

Listing the six types of Access objects

Laying out some essential database concepts

Access is the Microsoft database-management program, part of the Microsoft Office suite, that enables you to maintain databases — collections of data arranged according to a fixed structure. Its structure makes the information easy to select, sort, display, and print in a variety of formats. With Access, you can create and maintain as many databases as you need — you can even share them with other people over a local area network or the Internet.

Access is a general-purpose program that works with almost any kind of information. A database can be as simple as a list of addresses to replace your card file. Or you can create a wine-cellar database with information about each bottle in your cellar, or a bookstore-inventory database with information about books, publishers, customers, and special orders. Access can also handle complex databases that contain lots of types of information and lots of customized programming.

An Access database can contain lists of records about almost anything, from sales to sports scores. Unlike a spreadsheet program, Access makes information in lots of different formats easy to display — including alphabetical listings, formatted reports, mailing labels, and fill-in-the-blank forms.

Access 2007 comes as a part of the Microsoft Office 2007 Professional suite of programs, and is also available as a separate, stand-alone product. Previous versions of Access have also been part of previous Office editions — Access 2003 in Office 2003, Access 2002 in Office XP, Access 2000 in Office 2000, and so forth. Because Access is part of Microsoft Office, sharing information with Word documents and Excel spreadsheets is easy.

The Six Types of Access Objects

Access databases are made up of objects — things you can create, edit, and delete, each with its own name and settings. Object-oriented systems allow you to create these things one piece at a time, using pieces that fit together.

Access contains various kinds of objects, including objects for storing, displaying, and printing your data, as well as objects that contain programs you write. At first, you’ll probably use only a few types of objects, but as you customize your database, you may end up using them all. You start with tables for storing data, forms for editing data on-screen, reports for printing data, and queries for selecting and combining data. Later, you may create macros and modules, which contain programs that you write.

In this section, we cover each of the main types of Access objects: tables, queries, forms, reports, macros, and modules.

Tables for storing your data

Tables are where you put your data. A table is an Access object that is made up of a series of records — the electronic equivalent of the index cards that make up an address list. Each record contains information in the same format. In an address list, each record contains information about one person: name, address, and other facts. Each individual piece of information — such as first name, last name, or street address — is called a field.

Figure 1-1: A table contains records (rows) and fields (columns).

After you set up tables in your database and type in (or import) information, you can sort the records, select records that match a criterion, and then display and print the records.

Proper design of your tables — choosing how many tables to create and which fields are stored in which table — is key to creating a usable and flexible database. Chapter 3 of this book includes a step-by-step procedure for designing your database, and Book II explains how to create tables and fill them with data.

Is a spreadsheet a database?

In two words, not really. Many people use spreadsheet programs, such as Microsoft Excel or Lotus 1-2-3, to store lists of records. Some spreadsheet programs have limited database capabilities, but they aren’t designed to do as much as a database program. You can use a spreadsheet to store an address list — and you can enter, edit, delete, and sort the addresses (one per row on the spreadsheet) — but printing mailing labels or form letters is a major chore. Spreadsheets don’t (and can’t) think of your data in terms of tables, records, and fields, but rather in terms of cells (the basic unit of a spreadsheet) arranged in rows and columns. That’s too limited a model for sophisticated information management — as you’ve probably suspected if you’re using Access for your database work. It’s the right tool for the job!

Queries for selecting your data

Queries are operations that slice and dice your data to answer specific data needs. The most commonly used type of query helps you select data from a table, perhaps to select which records you want to include in a report. You can create a query that shows you all the people in your address book who live in (say) Vermont, or all those for whom you don’t have a phone number. To create this type of query, you enter criteria that specify what values you want to match in specific fields in the tables (for example, VT in the State field to find Vermonters, or nothing in the Phone Number field to find the phoneless, or both).

You can also use queries to combine information from several tables. A bookstore database may store book author names in the Books table and book ordering information in the Purchase Orders table. A query can pull information from both these tables — to show (for example) all the Terry Pratchett novels you ordered for the last month. Queries can also create calculated fields, including totals, counts, and averages.

Another type of query is the action query, which does something to the records you select — copy records from one table to another, make a change to all the records you select, delete records you select, that sort of thing. Crosstab queries help you analyze the information in your tables by summarizing how many records contain specific combinations of values.

Queries are the way you get useful information out of your tables — and you’ll probably create zillions of them as you play with your database. Book III explains how to create and use queries of all kinds.

Forms for editing and displaying your data

An easy way to enter data, especially into more than one related table, is to use a form — a standard database document that displays information from one or more tables on-screen. You can have all kinds of fun with forms; for example, you can

edit your data or type in new records

choose the layout of the table’s information on the form

specify the order in which your items appear

group items together with lines and boxes

use pull-down lists, radio buttons, and other types of on-screen controls for entering and editing data

Figure 1-2 shows a form for entering names and addresses for the Address Book table shown back in Figure 1-1.

Figure 1-2: A form shows information from one table record at a time.

But why stop there? You can build intelligence into forms, too — program some smart boxes that automatically capitalize what you type in, or check your entry against a table of valid values.

After your database goes into production — that is, you use it for its intended purpose — forms become the most-used Access object. As go the forms, so goes the database — so Book IV explains how to design, create, modify, and use forms.

Reports for printing your data

Forms are primarily designed to appear on-screen; reports (on the other hand) are designed to be printed out, as shown in Figure 1-3. Like forms, reports display information from tables; you get to choose the layout of the information. Most reports are based on queries; you use a query to choose the information that appears in the report. The report design defines the order in which records appear, which fields appear where, and which fonts, font sizes, lines, and spacing to use. (Control freaks, rejoice!)

Figure 1-3: A report lets you put Access data on paper.

In addition to reports on normal paper, you can create reports for printing on envelopes, labels, or other printed forms. Access comes with report wizards that make creating fancy reports easy. It can also print charts and cross-tabulations (crosstabs) based on the data in your database.

Book V covers how to create and print reports, charts, and crosstabs.

Macros for saving keystrokes

Access includes two separate programming languages: one for macros and a separate one (VBA) for larger programs. Macros are programs that automate the commands you give when you use Access — you “write” them by telling Access to record your keystrokes while you do something on-screen. For example, you can write a macro that moves the cursor to the last record in the Orders table whenever you open the Order Entry form. (What are the chances that you’d want to edit your very first order? Most of us would be likelier to want to edit the last order or enter a new order.) Or you can write a macro that moves your cursor to the next applicable blank in a form, based on the entries you made so far.

After you get some practice at creating macros, you can create buttons on your forms that run the macros with a quick click. You can also tell your form to run a macro automatically whenever you move to a field on the form, or enter data into the field — handy!

You don’t have to be a programmer to create macros. Access helps you write them by providing menus of commands. Book VI explains how to create nifty and useful macros to clean up data entry — and a number of other items — automatically.

Modules for writing your own programs

Okay, now we come to the serious programming stuff: modules — another term for Visual Basic programs. VBA (Visual Basic for Applications) is a programming language based on the age-old BASIC language; it’s specifically geared for working in Access and other Office programs. Macros are fine for saving a few keystrokes or cleaning up the data you enter in a field, but when the going gets complex, you can use VBA.

Programming isn’t for the technologically faint of heart. Fortunately, it’s rarely necessary. But when everything else is done in your database, take a look at Book VIII for an introduction to VBA programming. Writing small programs isn’t all that hard — and if you acquire a taste for programming, who knows what you’ll end up creating!

Essential Database Concepts

Here are the Four Commandments of databases. (Aren’t you relieved there aren’t 10?). You’ll find lots more important rules and guidelines throughout this book as you discover how to work with various Access objects, but these four apply right from the start, no matter what kind of database you are using:

Store information where it belongs, not where it appears. Where you store information has nothing to do with where it appears. In a database, you store information in tables based on the structure of the information. (Don’t worry — Chapter 3 of this book explains how to figure out the structure of your data.) Each piece of information likely appears in lots of different places. For example, in a database for an online bookstore, book titles and authors’ names appear on your invoices, purchase orders, and sales receipts. But the right place to store those book titles and author names is in the Books table, not in the Sales table or the Purchase Orders table.

Garbage in, garbage out (GIGO). If you don’t bother to create a good, sensible design for your database — and if you aren’t careful to enter correct, clean data — your database will end up full of garbage. A well-designed database is easier to maintain than a badly designed one, because each piece of information is stored only once, in a clearly named field in a clearly named table, with the proper validation rules in place. Yes, it sounds like a lot of work, but cleaning up a database of 10,000 incorrect records is (pardon the understatement) even more work. See Book II, Chapter 5 for ways to avoid GIGO.

Separate your data from your programs. If you create a database to be shared with (or distributed to) other people, store all the tables in one database (the back end) and all the other objects in another database (the front end). Then you link these two databases together to make everything work. Separating the tables from everything else streamlines the whole rigmarole of updating queries, forms, reports, or other stuff later without disturbing the data in the tables. (See Book VII, Chapter 1 for how to separate a database into a front end and back end.)

Back up early and often. Make a backup of your database every day. With luck, your office already has a system of regular (probably nightly) backups that includes your database. If not, make a backup copy of your database at regular intervals, and certainly before making any major changes. (See Book VII, Chapter 1 for how to make backups.)