Table of Contents
| Introduction | 1 |
| Ch. 1 | Designing Applications | 11 |
| Ch. 2 | Introduction to Event-Driven Programming | 51 |
| Ch. 3 | Creating Code | 93 |
| Ch. 4 | Controlling the Program | 145 |
| Ch. 5 | Using Access Objects | 175 |
| Ch. 6 | Using DAO | 211 |
| Ch. 7 | Data Management Techniques | 269 |
| Ch. 8 | Working with Tables | 315 |
| Ch. 9 | External Data | 339 |
| Ch. 10 | Reports | 375 |
| Ch. 11 | Advanced Programming Techniques | 411 |
| Ch. 12 | Finding and Dealing with Errors | 455 |
| Ch. 13 | Using Classes | 517 |
| Ch. 14 | WithEvents and RaiseEvent | 579 |
| Ch. 15 | Libraries and Add-Ins | 603 |
| Ch. 16 | Automation | 649 |
| Ch. 17 | Multi-User | 687 |
| Ch. 18 | The Internet | 749 |
| Ch. 19 | Optimizing Your Application | 787 |
| Ch. 20 | Moving to Client-Server | 839 |
| Ch. 21 | ActiveX Data Objects - ADO | 893 |
| Index | 933 |
Read a Sample Chapter
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!
Prototyping
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.
Continues...
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.
loading...