Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training,
learning paths, books, tutorials, and more.

Introduction

People have tried a variety of techniques to organize information.
They’ve used Rolodexes, punch cards, cardboard boxes, vertical files,
Post-it notes, 10,000-page indexes, and (when all else failed) large piles
on top of flat surfaces. But after much suffering, people discovered that
computers were far better at dealing with information, especially when
that information is large, complex, or changes frequently.

That’s where Microsoft Access comes into the picture. Access is a
tool for managing databases—carefully structured
catalogs of information (or data). Databases can
store just about any type of information, including numbers, pages of
text, and pictures. Databases also range wildly in size—they can handle
everything from your list of family phone numbers to a ginormous product
catalog for Aunt Ethel’s Discount Button Boutique.

In this book, you’ll learn how to design complete databases,
maintain them, search for valuable nuggets of information, and build
attractive forms for quick and easy data entry. You’ll delve into the
black art of Access programming, where you’ll pick up
valuable tricks and techniques that you can use to automate common tasks,
even if you’ve never touched a line of code before. And you’ll even
explore the new web database feature that lets you
put your database online so anyone can use it—provided you have the right
hosting company to help you out.

What You Can Do with Access

The modern world is filled with information. A web search for a
ho-hum topic like “canned carrots” nets more than a million web pages.
As a result, it’s no surprise that people from all walks of life need
great tools to store and manage information.

It’s impossible to describe even a fraction of the different
databases that Access fans create every day. But just to get you
thinking like a database maven, here are some common types of
information that you can store handily in an Access database:

Mailing lists that let you keep in touch with friends, family,
and coworkers.

Business information, like customer lists, product catalogs,
order records, and invoices.

Lists of guests and gifts for weddings and other
celebrations.

Lists of expenses, investments, and other financial planning
details.

Think of Access as a personal assistant that can help you
organize, update, and find any type of information. This help isn’t just
a convenience—it also lets you do things you could never accomplish on
your own.

Up To Speed: The Benefits of a Good Database

Many people use an address book to keep track of close friends,
distant relatives, or annoying coworkers. For the most part, the
low-tech address book works great. But consider what happens if you
decide to store the same information in an Access database. Even
though your contact list isn’t storing Google-sized volumes of
information, it still offers a few features that you wouldn’t have
without Access:

Backup. If you’ve ever tried
to decipher a phone number through a coffee stain, you know that
sometimes it helps to have things in electronic form. Once you
place all your contact information into a database, you’ll be able
to preserve it in case of disaster, and print as many copies as
you need (each with some or all of the information showing). You
can even share your list with a friend who needs the same
numbers.

Space. Although most people
can fit all the contacts they need into a small address book, a
database ensures you’ll never fill up that “M” section. Not to
mention that you can cross out and rewrite the address for your
itinerant Uncle Sid only so many times before you run out of
room.

Searching. An address book
organizes contacts in one way—by name. But what happens once
you’ve entered everyone in alphabetical order by last name, and
you need to look up a contact you vaguely remember as Joe? Access
can effortlessly handle this search. It can also find a matching
entry by phone number, which is great if your phone gives you a
log of missed calls, and you want to figure out who’s been
pestering you.

Sharing. Only one person at a
time can edit most ordinary files like Microsoft Word documents
and spreadsheets. This limitation causes a problem if you need
your entire office team to collaborate on a potluck menu. But
Access lets multiple people review and change your data at the
same time, on different computers. Chapter 19 has the full
story.

Integration with other
applications. Access introduces you to a realm of
timesaving possibilities like mail merge. You can feed a list of
contacts into a form letter you create in Word, and automatically
generate dozens of individually addressed letters. You’ll see how
to export Access data in Chapter 20.

All these examples demonstrate solid reasons to go electronic
with almost any type of information.

Imagine you’ve just finished compiling a database for your
collection of 10,000 rare comic books. On a whim, you decide to take a
look at all the books written in 1987. Or just those that feature
Aquaman. Or those that contain the words “special edition” in the title.
Performing these searches with a paper catalog would take days. On an
average computer, Access can perform all three searches in under a
second.

Access is also the king of small businesses because of its
legendary powers of customization. Though you can use virtually any
database product to create a list of customer orders, only Access makes
it easy to build a full user interface for that
database (as shown in Figure 1).

Figure 1. This sales database includes handy forms that salespeople can
use to place new orders (shown here), customer service representatives
can use to sign up new customers, and warehouse staff can use to
review outgoing shipments. Best of all, the people who are using the
forms in the database don’t need to know anything about Access. As
long as a database pro (like your future self, once you’ve finished
this book) has designed these forms, anyone can use them to enter,
edit, and review data.

The Two Sides of Access

Designing your database. This
task involves creating tables to hold data,
queries that can ferret out important pieces
of information, forms that make it easy to
enter information, and reports that produce
attractive printouts.

Dealing with data. This task
involves adding new information to the database, updating what’s
there, or just searching for the details you need. To do this
work, you use the tables, queries, forms, and reports that you’ve
already built.

Most of this book is dedicated to task #1—creating and
perfecting your database. This job is the heart of Access, and it’s
the part that initially seems the most daunting. It’s also what
separates the Access masters from the neophytes.

Once you’ve finished task #1, you’re ready to move on to task
#2—actually using the database in your day-to-day
life. Although task #1 is more challenging, you’ll (eventually) spend
more time on task #2. For example, you might spend a couple of hours
creating a database to keep track of your favorite recipes, but you’ll
wind up entering new information and looking up recipes for
years (say, every time you need to cook up
dinner).

Access vs. Excel

Access isn’t the only Office product that can deal with lists
and tables of information. Microsoft Excel also includes features for
creating and managing lists. So what’s the difference?

Although Excel’s perfectly good for small, simple amounts of
information, it just can’t handle the same
quantity and complexity of
information as Access. Excel also falters if you need to maintain
multiple lists with related information (for example, if you want to
track a list of your business customers and a list of the orders
they’ve made). Excel forces you to completely separate these lists,
which makes it harder to analyze your data and introduces the
possibility of inconsistent information. Access lets you set up strict
links between tables, which prevents these
problems.

Access also provides all sorts of features that don’t have any
parallel in the spreadsheet world, such as the ability to create
customized search routines, design fine-tuned forms for data entry,
and print a variety of snazzy reports.

Of course, all this isn’t to say that Access is
better than Excel. In fact, in many cases you
might want Excel to partner up with Access. Excel shines when
crunching reams of numbers to create graphs, generate statistics, or
predict trends. Many organizations use Access to store and manage
information, and then export a portion of that information to an Excel
spreadsheet whenever they need to analyze it. You’ll learn how to take
this step in Chapter 20.

One of the most important differences between Access and
database products like SQL Server is that Access is a
client-side database. In non-techie terms, that
means that Access runs right on your personal computer. Database
engines like SQL Server are server-based: They
store the data on a high-powered server computer, which you access
from a garden variety PC. (This interaction happens over a local
network.)

Server-based databases are much more complex to set up and
maintain, but they provide enhanced performance and rock-solid
stability, even when thousands of people use them at once. However,
the only people that require high-end databases like SQL Server are
large organizations. Amazon.com wouldn’t last 5 minutes if it had to
rely on an Access database. But Access works just fine for most small
and mid-sized businesses. It’s also perfect for personal use. (If you
still have lingering doubts about whether Access can meet your needs,
check out the box on When Access Isn’t Enough.)

Another important difference between Access and server-side
database products is that Access is an all-in-one solution for storing
and interacting with data. Server-side database
engines like SQL Server focus exclusively on storing data (and sending
that data to other computers when they request it). However, this
single-minded design has a sizable price. An ordinary person can’t
directly edit a database that’s stored by SQL Server. Instead, you
need to use yet another program that can talk to
SQL Server and ask for the information it needs. In most cases, this
program needs to be hand-built by a savvy programmer. In other words,
if you’re using SQL Server, you need to write a whole application
before you can effectively use your database.

Sometimes, Access fans do turn into SQL Server gurus. You can
start with a modest Access database and then step up to SQL Server
when your needs exceed what Access provides. The process isn’t always
seamless, but it’s possible. You can even keep using Access as a front
end to manage your SQL Server database. You can learn about this trick
in Chapter 21.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training,
learning paths, books, interactive tutorials, and more.