How This Book Is Organized
This book is organized into 18 chapters, each of which focuses on a
particular Access programming topic.
- Chapter 1
This chapter covers the many types of queries and the power you have
over the Access environment through the use of queries. From simple
select queries through parameter, crosstab, totals, and Data
Definition Language (DDL) queries, this chapter will show many
different ways to use queries in your applications. Queries are the
real heart of Access, and learning to use them intelligently will
make your work in Access go much more smoothly.
- Chapter 2
Most database applications require some sort of user interface, and
in Access, that user interface is almost always centered around
forms. This chapter demonstrates some useful ways to make forms do
your bidding, whether in terms of controlling data or making forms do
things you didn't think were possible. We
demonstrate how to create multipaged forms and how to create an
incremental search list box. We also show how to create your own
pop-up forms, with a technique you can use in many situations. Forms
can do much more than you might have imagined, and this chapter is a
good place to look for some new ideas.
- Chapter 3
It seems as though reports ought to be simple: just place some data
on the design surface and "let her
rip!" That's true for simple
reports, but Access's report writer is incredibly
flexible and allows a great deal of customization. In addition, the
report writer is quite subtle in its use of properties and events.
The topics in this chapter will advance your understanding of
Access's report writer, from creating snaking column
reports to printing alternating gray bars. Some of the solutions in
the chapter will require programming, but many
don't. If you need to create attractive reports (and
everyone working with Access does, sooner or later), the topics in
this chapter will make your work a lot easier.
- Chapter 4
This chapter is a compendium of tips and suggestions for making your
application development go more smoothly, more professionally, and
more internationally. Rather than focusing on specific topics, this
chapter brings up a number of issues that many developers run across
as they ready their applications for distribution. How do you build a
list of objects? How do you make sure all your
objects' settings are similar? How do you translate
text in your application? How do you use the common Windows dialogs?
All these questions, and more, make up this group of tips for the
- Chapter 5
Many developers need to gain tight control over printed output, but
earlier versions of Access made this quite difficult. Starting with
Access 2002, you'll find direct support for
selecting a specific printer device, changing print layout settings,
and more. This chapter introduces the Printer object and its
properties, allowing you to perform tricks that were difficult, if
not impossible, in earlier versions. (Although many of the chapter
databases will work in Access 2000, this chapter's
examples will not. Because the functionality presented here was new
in Access 2002, the samples simply won't do anything
useful in Access 2000.)
- Chapter 6
This chapter concentrates on working with data in ways that
traditional database operations don't support.
You'll learn how to filter your data, back it up,
locate it on the filesystem, calculate a median, perform sound-alike
searches, save housekeeping information, and more. Most examples in
this chapter use some form of VBA, but they are clearly explained,
and "testbed" applications are
supplied to show you how each technique works.
- Chapter 7
The solutions in this chapter cover some of the details of VBA that
you might not find in the Access online help. We've
included topics on several issues that plague many Access developers,
from handling embedded quotes in strings and creating procedure
stacks and code profilers, to programmatically filling list boxes, to
working with objects and properties. We've included
code to sort an array and solutions that combine several of the
previous topics, such as filling a list box with a sorted list of
filenames. If you're an intermediate VBA programmer,
this chapter is a good place to expand your skills. If
you're already an expert, this chapter can add some
new tools to your toolbox.
- Chapter 8
Access is a big application, and when designing applications you have
a number of choices to make, each of which can affect the
application's performance. Unless
you're creating only the most trivial of
applications, you'll have to spend some time
optimizing your applications. This chapter's topics
work through several different areas of optimization—steps you
can take to make your databases work as smoothly as possible. The
topics range from optimizing queries, forms, and VBA, to testing the
speed of various optimization techniques, to accelerating
client/server applications. If you want your applications to run as
quickly as possible, this chapter is a good place to look for tips.
- Chapter 9
This chapter presents a compendium of user interface tips and
techniques. By implementing the ideas and techniques in this chapter,
you'll be able to create a user interface that
stands out and works well. You'll find some simple,
but not obvious, techniques for controlling the Access environment,
such as altering your global keyboard mappings as you move from one
component of your application to another and creating forms that hide
the menus and toolbars when they're active. The
chapter shows how to create combo boxes that accept new entries and
how to provide animated images on buttons. You'll
also find useful tips on working with data on your forms, using an
ActiveX control to improve your interface.
- Chapter 10
Few modern database applications run on standalone machines; most
must be able to coordinate with multiple users. This chapter offers
solutions to some of the common problems of networking and
coordinating multiple simultaneous users. The most important issues
are security and locking, and this chapter has examples that cover
each. In addition, the topics in this chapter focus on replication,
transaction logging, password control, and keeping users from holding
locks on data. If you're working in a shared
environment, you won't want to miss this chapter!
- Chapter 11
No matter how much you've avoided using the Windows
API in Access applications, in this chapter you'll
discover that it's really not a major hurdle.
We'll present some interesting uses of the Windows
API, with example forms and modules for each solution. In most cases,
using these examples in your own applications takes little more work
than importing a module or two and calling some functions.
You'll learn how to restrict the mouse movement to a
specific area on the screen, how to run another program from your VBA
code, and how to wait until that program is done before continuing.
We'll demonstrate a method for exiting Windows under
program control and how to retrieve information about your Access
installation and the current Windows environment. The possibilities
are endless once you start diving into the Windows API, and this
chapter is an excellent place to start.
- Chapter 12
This chapter gives you examples of using Automation to interact with
most of the Microsoft Office applications. One solution uses the
statistical, analytical, and financial prowess of the Excel function
libraries, directly from Access; another shows how to
programmatically create an Excel chart. You'll learn
how to retrieve document summary information for any selected Word
document and how to perform mail merges using Access data. Other
examples demonstrate how to use Access to control PowerPoint and how
to add contacts in Outlook.
- Chapter 13
Distributing Access applications normally means that your users have
to install Access (or the Access runtime version, available as part
of Microsoft Office XP Developer) on their local machines. What if
users could run your applications over a corporate intranet, without
requiring Access to be installed? That's the goal of
DAPs. This chapter introduces some of the concepts
you'll need to understand in order to take advantage
of this feature, which was added in Access 2000 and significantly
improved in Access 2002. You'll learn how to
customize the navigation controls and how to use your own controls
for navigation. You'll find tips on creating pages
that allow users to update data and valuable techniques for managing
your data connections, and you'll learn how to
adjust the default settings for the different sections of new DAPs to
give your applications a consistent look.
- Chapter 14
This chapter shows you how to take advantage of the new data options
available in Access Data Projects, which connect directly to a SQL
Server database, and provides solutions that address traditional MDB
databases linked to SQL Server data. You'll learn
how to dynamically connect to SQL Server at runtime, whether you are
using an ADP or an MDB, and you'll learn how to
allow multiple users to share a single ADP. You'll
see how to make the most of the Server Filter By Form feature in ADPs
and how to pass parameters to stored procedures in both ADPs and
MDBs. You'll also discover how you can use an ADP to
connect to multiple SQL Server databases at once, even though the ADP
seems to force you to select a single one.
- Chapter 15
As powerful as Data Access Pages are, they only represent one way to
gain access to your Access data from a browser. This chapter
introduces you to other Microsoft Office web technologies you can use
to "webify" your Access databases.
You'll learn how to use Microsoft FrontPage to
create a web form that posts its data to an Access database.
You'll also learn how to use the FrontPage Database
Interface Wizard to create an ASP or ASP.NET front end to an Access
database. You'll learn how to use Windows SharePoint
Services along with FrontPage to create web pages that draw data from
Access databases without writing any code. You will also learn how to
use Access as a frontend for managing SharePoint lists.
- Chapter 16
Smart Tags were introduced in Office XP, but they
weren't available in Access until now. This chapter
shows you how to use the built-in smart tags in your applications,
attaching them to form controls or to fields in a table.
You'll learn to configure smart tags interactively
or by writing code. You'll also learn how to extend
smart tag functionality by creating your own custom smart tags.
- Chapter 17
Microsoft .NET and Access live in two different programming worlds,
but you can use a set of interoperability tools to bridge the two
worlds. This chapter shows you how to take advantage of these tools
to call a .NET component from an Access application.
You'll also learn how to call a .NET web service
from Access, and how to manipulate the .NET objects returned by some
web services. You'll learn how to retrieve data from
an Access database using ADO.NET. And you'll learn
how to automate an Access report from a .NET application.
- Chapter 18
One of the strengths of Access is its ability to work with data from
many disparate sources. XML has emerged as a dominant standard for
exchanging data between applications, and Access now enables you to
work with this data. In this chapter you'll learn
how to import and export XML data and schema, and how you can use
XSLT to reformat XML data. For example, you'll see
how to use XML technologies to export a report to an HTML or ASP Web
page, preserving the look and feel of the original Access report.