Uh-oh, it looks like your Internet Explorer is out of date.

For a better shopping experience, please upgrade now.

Beginning Access 2002 VBA

Beginning Access 2002 VBA

by Apress, Robert Smith, Paul Turley, Martin Reid, Mark Horner

What is this book about?

Access 2002 is the core database application within the Office XP suite. Using VBA (Visual Basic for Applications), the user can create his or her own programs in what is essentially a subset of the Visual Basic programming language.

Using VBA with Access is a tremendously powerful technique, as it allows you to create great


What is this book about?

Access 2002 is the core database application within the Office XP suite. Using VBA (Visual Basic for Applications), the user can create his or her own programs in what is essentially a subset of the Visual Basic programming language.

Using VBA with Access is a tremendously powerful technique, as it allows you to create great user interfaces (like forms or reports) as a front end to actual data storage and manipulation within the database itself.

What does this book cover?

This book is a revision of the best-selling Beginning Access 2000 VBA, reworked to provide a rich tutorial to programming Access 2002 with VBA. New material covers the enhanced options in Access 2002 for publishing data to the Web, handling XML, integrating with SQL Server Desktop Engine, and so on.

Who is this book for?

This book is for the Access user who already has a knowledge of databases and the basic objects of an Access database, and who now wants to learn how to program with VBA. No prior knowledge of programming is required.

Product Details

Publication date:
Edition description:
Product dimensions:
(w) x (h) x 0.08(d)

Read an Excerpt

Beginning Access 2002 VBA

By Robert Smith Dave Sussman Ian Blackburn John Colby Mark Horner Martin Reid Paul Turley Helmut Watson

John Wiley & Sons

ISBN: 0-7645-4402-0

Chapter One

Designing Applications

Access 2002 is mostly a very intuitive and easy to use application. From the early days of Access, usability has always been one of the primary development focuses behind Access. In fact it was this ease of use that was a major factor in the incredible speed with which Access came to be accepted as the definitive desktop database development tool.

But Access has always appealed to a wider audience than simply end users and inexperienced developers. Behind its ease of use, Access has always provided a very powerful database and application development tool - more recent releases of Access have extended this power even further with the introduction of features such as Access Database Projects (ADPs), Data Access Pages (DAPs) and the adoption of VBA (Visual Basic for Applications) version 6 as its programming language.

In this chapter, we introduce the application we will be developing throughout the course of this book. After that, we'll contrast the differences between macros and VBA, and highlight one of the limitations you will encounter when using macros.

But before any of this, let's begin by defining what an Access Application is, and take you through the design processes you ought to consider before you even begin coding.

What Is an Access Application?

An Access application is just the same as any other kind of application, but one built using Access tools. It is a collection of interrelated objects working together to achieve a specific objective, usually business-orientated.

That didn't tell us that much, did it? Maybe we should have asked a different question:

What makes an Access application different from any other kind of application?

The main difference is that Access is designed from the ground up to handle data, quickly, efficiently, and lots of it. Access applications therefore tend to be data intensive. Using Access to create such applications can be easier and faster and can produce better results than using anything else. Obviously today's applications also need to be able to present a modern and efficient graphical user interface and to allow for all sorts of additional functionality, like connections to other applications or to the Internet - Access provides for this as well, but this is not its primary goal. It is, for example, entirely possible to create an Access application that simulates shooting missiles at alien spacecraft as they move across a Martian landscape but this would certainly not be the best way to do it!

When you create a new database file (.mdb file) in Microsoft Access 2002, the first thing that you see is the database window. This is a container that will eventually hold a wide variety of different objects. Tables will be used to store data; queries will be designed to retrieve data in meaningful ways; forms, reports, and data access pages will all be used to display the results of those queries in ways that users can understand; and macros and VBA modules will provide the program logic which 'glues' the whole application together.

If you use Access 2002 to create a project (.adp file) instead, then the database window will also show additional server-side objects (such as database diagrams and stored procedures), which may also be included to make up the application.

All of these objects can play an important role in providing the functionality of the end product - whether it is hosted solely in an Access database or uses a client-server database project.

The aim of this book is to illustrate the important role played by VBA in orchestrating these objects, in binding them together through the use of logic to control workflow and to implement specific business rules, and in turning a collection of individual objects into a coherent and effective application. Yes, we want to teach you how to use VBA, but to do that without first telling you how to design an application would be irresponsible.

The Development Process

There are many skills involved in the development and delivery of successful Microsoft Access 2002 applications. The database designers need to be able to understand the principles of relational database design, so that they can design the tables that will hold the data and the relationships between those tables. The application developers need to have a feel for the graphical user interface (GUI) design, so that the forms they design for users to interact with will be intuitive and easy to use. They will also need to understand both SQL (Structured Query Language) and VBA so that they can write queries and procedures that not only return the correct data or perform the required task, but also do so quickly and efficiently.

There are other less technical (but no less complex) skills to master. Analysts need to be able to understand the business requirements of the users for whom the application is being designed, and to translate these requirements into a design specification from which the developers can work. Technical documenters need to be able to articulate how the application works, to anticipate confusions that users might experience and to clearly express their thoughts in documentation that is both accessible and informative. Test engineers need to be rigorous in their approach, perhaps using formal methodologies to check for errors, and must not take anything for granted when evaluating the application. Last, but certainly not least, project managers need to know how to monitor progress and track resource usage to ensure that the application is delivered on time and within budget.

Sometimes, if the application being developed is large-scale or complex, then there will be many different people involved in the application development lifecycle. Some will be responsible purely for analysis or design, others will work solely on designing queries or developing forms, and yet others will be responsible for other tasks, such as migrating legacy data into the Access database or producing user documentation. But at other times, particularly if the application is less complex, or if resources (such as money or people) are scarcer, then it is not uncommon for many of these tasks to be undertaken by individuals. Indeed, in many situations, a single person can be responsible for the entire analysis and development process.

Irrespective of the number of people involved, or the development methodology employed, the development lifecycle for an Access application will typically involve the following steps:

Analysis [right arrow] Design [right arrow] Coding [right arrow] Testing [right arrow] Documentation [right arrow] Acceptance [right arrow] Review

In practice, however, these steps do not rigidly follow one after another. There can be significant overlaps and the project can iterate through some of these steps before progressing on to others. It is beyond the scope of this book to enter into a detailed discussion of different project lifecycle plans. However, it is undoubtedly true that the speed with which Access forms and reports can be produced makes Access an excellent tool for using in a more iterative lifecycle model. In such a situation, the lifecycle would look more like this:

The Analysis Phase

Irrespective of the type of project lifecycle mode, the first stage, and one of the most important to get right, is inevitably one of analysis. Without adequate analysis you will not be able to determine what the user wants from the application, the technical infrastructure within which the application will be implemented, and the constraints imposed by the data with which you will be working. Repairing the damage done by inadequate analysis, at a later date, can prove very costly or even kill a project completely.

Requirements Analysis

The starting point for creating any successful Access application is to have a clear understanding of what the users of an application want out of it. You need to know this before you can even start to think about how to design any solution. The sorts of questions you will need to ask in this stage include, among others:

What is the business process we are trying to automate?

What benefit is the new application designed to achieve? How will we measure the benefit?

Do we simply want to automate the existing process, or restructure the process and automate it?

Will the application have to interoperate with other existing or planned systems or processes?

What volume of data will the application be expected to handle?

How many users will be using the system at the same time (concurrently)? How many in total?

What is the anticipated mix of insert and update activity compared to query and reporting activity?

The problem is that the only people who can answer these questions are the customers who will use the finished application, and sometimes it can prove difficult to get answers out of them. It might be that the demands of their current business are so pressing that they have little time to answer questions about some future application. It might be that the sensitivities of internal office politics will make them unwilling to be too helpful in designing an application in which they feel they have too little ownership. Or it may be that they are trying to be helpful, but just don't know the answers to these questions, because it is something they have never thought about. Don't feel bashful about asking questions, however stupid some of them might sound. What might seem illogical and "obviously wrong" to an outsider might turn out to be a vital, but unspoken, business practice that simply must be implemented in order for the application to be acceptable. Once you think you understand a process it is often useful to run it past the client again for confirmation. Use these discussions to prompt further questioning to fill in any gaps. In any case, it is vital to try to approach this phase of the project with as few preconceptions as possible.

Requirements analysis is a skilled art and many organizations fail to appreciate the fact that good developers do not necessarily make good analysts. In fact, in many ways, developers make the worst analysts. By their very nature, good developers are constantly looking for detailed solutions to problems. Someone mentions a business requirement and you can almost hear the cogs whirring in their brains as their eyes glaze over and they start working out how they will produce a solution to that requirement; without stopping to ask what the value of satisfying that requirement is, or even if the requirement truly exists. That is not what you want from an analyst. You want an analyst to be able to take an objective look at the requirement expressed by the user, to check that they understand it correctly, to ask what the relevance of this requirement is to the business, to determine the metrics (ways of measuring) by which the successful implementation of the requirement can be judged and to express that requirement in a way that other parties involved in the project will understand.

A variety of tools and methods are available to assist the requirements analysis process. For example, JAD (Joint Application Development) is a technique that assists requirements definition by bringing all of the various parties who are interested in the development together in intense off-site meetings to focus on the business problem to be solved rather than worrying about specific technical issues.

Whether you use such techniques is up to you. What is important is that you value the requirements analysis process. This phase of a project is so important because it is the fundamental mechanism for defining both the scope of the project and the critical success factors that show when the project has achieved its requirements. It forms the basis for the contract between the users and the developers, and is the touchstone to be used when resolving conflict or confusion later on in the project lifecycle.

Ironically, the importance of sound requirements analysis is most clearly seen in its absence. When requirements are not properly defined or documented, one of two consequences almost inevitably follows. Either the requirements remain unmodified, with the result that the application fails to achieve its client's objectives; or the requirements are modified later in the development cycle. Late changes such as these can have a huge impact on project costs as their effects 'ripple' out and affect other areas such as documentation, design, coding, testing, personnel assignments, subcontractor requirements, and so on. Indeed, some studies indicate that such changes can be 50 to 200 times more expensive than they would have been if they had been made at the appropriate time!


Prototypes, pre-development versions, proof of concepts, nailed-up versions - it doesn't matter what name you give them; they are all attempts to further refine the analysis phase of the project. Access used to be seen as being "only" a prototyping tool with the "real" development given over to something more "industrial strength". This is no longer the case, however (if indeed it ever really was). Access is perfectly up to the job of all but the most demanding projects. It still makes a marvellous tool for prototyping though.

A prototype is a scaled-down version of the final application, which can be achieved at low cost and within a short timescale. It may have certain functionality incomplete or missing entirely. It may even only implement a tiny part of the whole project. The whole point of the prototype is to test those areas of the design that are currently uncertain. This may include a number of different methods to achieve the desired functionality, or perhaps alternative GUI designs to see which is easiest to use, or maybe sample queries with test data to determine what kind of hardware platforms will be required to attain the desired performance.

One thing you should never see is a prototype as v1.0 of the application. This is invariably a recipe for disaster. The temptation is to take the prototype and keep developing it without first going through all the other formal processes described below. You should always see the prototype as part of the analysis phase of the project and not the end of it; it exists to ask questions and to get them answered. If certain parts of the prototype later make their way into the final application (the GUI design would be a good example) then all well and good, but ideally you should plan and cost the work separately.

Technical Analysis

As well as determining the nature of the solution required by the users of the application, it is also necessary to determine the technical infrastructure that will support this solution.


Excerpted from Beginning Access 2002 VBA by Robert Smith Dave Sussman Ian Blackburn John Colby Mark Horner Martin Reid Paul Turley Helmut Watson Excerpted by permission.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.

Meet the Author

Robert Smith has been developing solutions with Access for over 10 years, in fact, ever since its original launch in November 1992. He currently works for EH3 Consulting, a software consultancy with offices in Edinburgh and Bath, where most of his time is spent designing Access or SQL Server - based solutions for a variety of companies throughout the UK and Europe.
When not working, Rob takes a keen interest in the progress (or otherwise) of Crystal Palace Football Club, as well as indulging his other passion for collecting (and occasionally sampling) fine wines. In practice, the varying fortunes of the former normally mean that he spends a lot more time sampling than collecting.

Dave Sussman has spent the majority of his professional life as a developer, using both Unix and Microsoft-based products. After writing his first two books while in full time employment, he realized that being an author sounded more glamorous than being a programmer. The reality is somewhat different. He now spends most of his time writing books for Wrox Press, speaking at conferences, and playing with most be ta products that Microsoft ships.

Ian Blackburn is director of Blackburn IT Services Ltd (http://www.bbits.co.uk) – an IT firm based in Kent, UK, offering technical training, development, and consultancy. He has long experience in many areas including Microsoft Office development, ASP and ADO, SQL Server, Site Server, Visual InterDev, Visual Studio .NET, ASP.NET, and e-commerce. He is an MCSE and has been a MCP since 1993.

John Colby is an independent consultant who has specialized in Access development since 1994, designing databases forcompanies in the U.S., Canada, Mexico, and Ireland. John is past president and current board member of Database Advisors Inc., (www.databaseadvisors.com), a not-for-profit organization dedicated to providing fellow Access and Visual Basic developers with a place to discuss what we do, why we do it, what works, and what doesn't. Database Advisors Inc. also allows developers to showcase their talents by sharing databases, wizards, and various code packages.
John lives in Connecticut with his family. He enjoys music, travel, and all things computers, and dreams of getting back to Europe someday soon.

Mark Horner is Senior Architect and Development Consultant with Torville Software, which specializes in decision support and web-centric knowledge systems. He has worked in a variety of roles with Australian, UK, and US corporations including: ANZ Banking Group, Aspect Computing, British Aerospace, Citibank, Hewlett Packard, and Tenix Defence Systems.
Mark holds a bachelor of business degree from Swinburne University of Technology and an MBA from Monash University, Australia. He is a member of the Internet Society (www.isoc.org) and a Dilbert devotee (www.dilbert.com).
I would like to thank the Wrox team for their determination “to make the books that make the difference” and the engineers at Redmond for more great development tools. Special thanks to Sarah Bowers for getting me into technical reviewing and to Beckie Stones for getting me into writing.

Martin W. P. Reid is an Analyst at The Queen's University of Belfast. Martin has been working with Microsoft Access since version 1 and his main interest is working with Access Data Projects. He has contributed several articles to Smart Access, Inside SQL Server, and Inside Microsoft Access, and is a contributor to TechRepublic (www.techrepublic.com) and www.builder.com. He is also co-author of SQL: Access to SQL Server, published by Apress, and has been the technical editor on books involving technologies from VB.NET database programming to Oracle 9i PL/SQL and Macromedia Dreamweaver MX.

Paul Turley, his wife Sherri, and their four children live in the small community of Port Orchard, Washington, on the shores of the Puget Sound. He works for Netdesk, Corp in downtown Seattle as a Developer Instructor and Project Consultant. He began his IT career in 1988, installing and supporting medical billing systems and obtained his MCSD certification in 1996. Paul began using Access version 1.0, SQL Server 4.21, and Visual Basic 3.0. Since then, he has built custom database systems for several businesses including Hewlett-Packard, Nike, Microsoft, and Boise Cascade. He has worked with Microsoft Consulting Services on large scale, multi-tier solutions employing new Microsoft technologies. As an independent trainer/consultant, he traveled for Microsoft and various training providers to teach project design and management, application development, and database design.
Paul currently maintains www.scout-master.com, a web-based service that enables Boy Scout units around the world to maintain their membership and advancement records on-line using ASP.NET and SQL Server. He has published course materials and has been a contributing author on books and articles, including Professional Access 2000 Programming and SQL Server Data Warehousing with Analysis Services from Wrox Press.

Helmut Watson started his IT career nearly twenty years ago writing games for the BBC micro. Soon after that he had to get a proper job so he moved into databases, initially using PC-Oracle v1.0. He quickly decided to change to DBMS that actually worked – Dbase, Clipper, Paradox, Informix, SQL Server, etc. After twenty years, there aren't many on the list left to try now.
Helmut specializes in database analysis and GUI design and runs a consultancy called "Nearly Everything" from his home in Essex, UK.
Known as Woof! to his friends (or anyone else who buys him a beer), he is a keen cyclist and a finalist in the 2000 British Marbles-on-Sand championships. Most people think he's a bit odd until they meet him – then they're sure!

Customer Reviews

Average Review:

Post to your social network


Most Helpful Customer Reviews

See all customer reviews