What is this book about?
If you are developing databases for your own use, the process need not be complicated. But when you build databases for clients, many of whom may not be familiar with Access or comfortable with databases in general, you have a lot more work to do. Expert One-on-One Microsoft Access Application Development makes that process easier.
In these pages, you discover how to set up tables and relationships to ensure that the database is properly normalized. Then you write VBA code to create the connective tissue that turns a bunch of tables, queries, forms, and reports into a complete and coherent application. You also find out about the important but often inadequately documented area of Automation code, which is used to communicate with other Office applications.
Over years of working with Access, the author has created some add-ins to save time when developing applications. She shows you how to use them to create a main menu for an application, automatically apply a naming convention to database objects, and ensure a consistent and professional appearance of the application’s forms.
This book helps you write VBA code that unites database components into an application that works.
What does this book cover?
Here's what you'll discover in this book:
Who is this book for?
This book is for experienced Access users who are familiar with creating Access objects and writing VBA code, but who need help transitioning from competent users/programmers to full-fledged Access developers.
Acknowledgments.
About the Author.
Introduction.
Part One: Creating an Access Application.
Chapter 1: Creating a Database for an Application.
Chapter 2: Using Forms to Work with Data.
Chapter 3: Selecting the Right Controls for Forms.
Chapter 4: Sorting and Filtering Data with Queries.
Chapter 5: Using PivotTables and PivotCharts to Interact with Data.
Chapter 6: Printing Data with Reports.
Chapter 7: Writing VBA Code in Modules.
Part Two: Modifying, Updating, and Maintaining Access Applications.
Chapter 8: Managing the Application Life Cycle.
Chapter 9: Reworking an Existing Application.
Chapter 10: Moving Old Data into a New Database.
Part Three: Working with Other Office Components (and More).
Chapter 11: Working with Word.
Chapter 12: Working with Outlook.
Chapter 13: Working with Excel.
Chapter 14: Working Outside of Office.
Index.
An application is more than just a database. Anybody with Access can create a database, but a database with a bunch of disconnected tables, queries, forms, and reports is not an application. An application consists of a database-or possibly several databases-containing normalized tables with appropriate relationships between them; queries that filter and sort data; forms to add and edit data; reports to display the data; and possibly PivotTables or PivotCharts to analyze the data, with all of these components connected into an efficiently functioning and coherent whole by Visual Basic for Applications (VBA) code. This chapter covers preparation for creating an application (getting and analyzing the information you need from the client), and creating tables to hold the application's data.
Most Access books give you lots of information about Access database tables (and other database objects), but don't necessarily tell you the stuff you really need to know: how to divide up the raw data you receive from a client into separate tables, how to decide what field type to use for each field in a table, and what relationships to set up between tables to create an efficient and well-integrated application. Through a series of developer-client Q&A sessions, I'll show you how to extract the information you need to create the right tables for your application and link them into appropriate relationships.
I have always found it easier to understand a process by watching somebody do it, as opposed to reading abstract technical information about it, so in this chapter, I will explain what I am doing as I walk you through the preparation for creating an application and then the creation of its tables. Succeeding chapters will deal with creating the application's forms, queries, and reports. Some technical information is necessary, of course, but it will be interspersed with demonstrations of what you need to do, and the explanations will tend to follow the actions, rather than precede them. Sometimes, if you can see how something is done correctly, that is all you need to know in order to do it right yourself, while an abstract technical explanation by itself is rarely adequate to teach you how to do something correctly.
There won't be lots of step-by-step walk-throughs illustrating how to create database objects in this book, or long lists of properties and other attributes. I am assuming that you already know how to create tables, forms, and other database objects (and if you need detailed information, you know how to get it from Help), and that instead, what you need is help in making decisions on what kind of data goes into which table, how the tables should be related, and what types of forms, queries, and reports are best for working with the data in your application.
Although code is crucial to binding an application into a coherent whole, there won't be any code in this chapter, because in Access, code runs from event procedures, and tables don't have event procedures. Before writing event procedures, we need to create tables to hold the data, and that is what this chapter covers.
Gathering Data
To start creating an Access application, you need two things: a clear idea of what tasks the application should perform and the output it should produce, and an adequate quantity of realistic data. Rather than just asking the client for a list of the tasks the application needs to perform, I usually ask a series of questions designed to elicit the required information. A typical Q&A session is presented in the next section of this chapter. However, if there is already a functioning database, printouts of its reports and screen shots of its forms can be helpful as an indication of what tasks are currently being done.
For best results, there is no substitute for large quantities of real data, such as the ebook data in the sample EBook Companion database used in Chapter 9, Reworking an Existing Application. But if you have a reasonable quantity of representative data and a client who is willing to answer your questions, that should be sufficient to set up tables with the correct fields. With this information, you can create tables with the necessary fields; set up relationships between them; and proceed to create the queries, forms, reports, and VBA code that will let you create an application that does what the client wants.
Curiously, I'm often asked to start working on an application for a client without any data at all. It may be difficult to convey this concept to a client, but it is important to get real-life data (either in electronic or paper form) in order to set up tables and fields correctly. If you (the developer) have to create dummy data to have something to work with when creating tables and other database objects, you will probably end up having to make changes-possibly major changes-to tables later on, and find that further changes are needed to other database components; so, it really helps to have a substantial amount of representative data to work with.
However, realistically there are cases where you won't be able to get data from the client. There are two cases where data isn't available: a brand-new business (or other enterprise) that doesn't have any data yet, and a business whose data is confidential. In these cases, you just have to do the best you can, creating dummy data after questioning the client about what data needs to be stored in the application's tables.
Once you have obtained the data in electronic or paper form, it's best to just use it as raw material to help you determine what fields you need when designing tables and other components, rather than as readymade components to plug into your application. Looking at real data, you can see (for example) whether or not there are unique IDs for products. If there is a unique Product ID, that field should be the key field of the Products table; otherwise, you will need to create an AutoNumber field. If you see multiple addresses for customers or clients, you will need to create linked tables for address data, for purposes of normalization; if there is only one address per customer or client, address data can be stored directly in the Customers or Clients table. In most cases, even if you are given a database with Access tables, you will need to make some modifications for purposes of normalization, and create a number of supporting tables as well as lookup tables to use as the row sources of comboboxes used to select values or records.
Figuring Out Business Tasks and Objects
When designing an application for a client, after obtaining a reasonable quantity of representative data, you need to discuss the processes to be modeled in the application-not just how they are done currently, but how they could be done better and more efficiently. For example, users may have been typing data into textboxes; if the data is limited in nature (for example, sales regions or phone number types) a combobox with a lookup table as its row source will ensure that users don't mistype an entry, which would cause problems when sorting or filtering data later on.
A client may give you piles of paper documentation, or descriptions of business processes, which again may or may not be helpful, depending on how well thought out these processes are. Often, real-world business practices develop bit by bit over the years, with new procedures not being integrated with older processes as well as they might be. When designing an application, it's a good idea to review the existing procedures and consider whether they should be streamlined for greater efficiency when setting up the database.
Don't just attempt to duplicate existing business processes in your database-at least not without examining them closely. Upon examination, you will often find that there are serious gaps in procedures that need to be remedied in the database. Just because users have been manually typing customer letters in Word and typing the customer address off the screen from a database record doesn't mean that you shouldn't generate Word letters automatically. (See Chapter 11, Working with Word, for information about generating Word letters from an Access database.)
You may also see that your application could do some tasks that aren't being done at all, but that would be very useful, such as generating email to clients, or analyzing data in PivotTables or PivotCharts. See Chapter 12, Working with Outlook, for information on sending email messages from Access. But the application first needs tables to store data, so the initial task is to set up the database's tables.
Determining Your Entities
The first task in setting up a database is determining what things it works with and how they work with each other. (The technical term often used in database literature is entity, but as far as I am concerned, thing works just as well.) If you are developing an application for a client, there may be an existing database. Depending on the skills of the person who created the database, this may be more of a problem than a helpful first step.
As an example of how to figure out the things your application needs to work with, following is a hypothetical example of a client who wants an application to manage his business, called the Toy Workshop. Let's start by asking the client some basic questions:
Q: What does the business do?
A: We sell toys.
We need a Toys table.
Q: Do you have an ID or product number for each toy?
A: Yes, a combination of letters and numbers.
We need a text ToyID field as a key field in tblToys.
Q: Do you make the toys or purchase them from vendors and resell them?
A: Both.
We need a table of materials used in manufacturing toys. We might need two tables for toys-one for toys purchased for resale and one for toys manufactured in-house. We need to determine whether the two types of toys are different enough to require different tables or whether they can be stored in one table, with different values in a few fields, and a Materials table, for toy-making materials.
Q: What are the differences between manufactured and purchased toys?
A: For purchased toys we need to record the vendor name, vendor product number, purchase price and purchase date; for manufactured toys we need to know how much of each component is used, the labor costs, and when they were manufactured.
Sounds like we could use a single Toys table, with a Yes/No Purchased field to indicate whether the toy is purchased or manufactured; that field could then be used to enable or disable various controls on forms. We also need a Vendors table, to use when selecting a value for the VendorName field, and a Materials table, for toy-making materials.
Q: Are raw materials purchased from different vendors than toys to resell, or could one vendor sell you both materials and finished toys?
A: Most of the vendors we use sell only finished toys; some sell only materials, and just a few sell both materials and finished toys.
Then all the vendors could be stored in one table, with Yes/No fields to indicate whether they sell finished toys, materials, or both.
Q: When you make the toys, is this done in your own workshop or factory, or contracted out?
A: Done in our workshop.
No need for a Contractors table.
Q: Do you have just one workshop, or several?
A: Just one.
Don't need a lookup table for workshops.
Q: Do you do anything else other than selling toys?
A: Yes, we also repair broken toys.
Need a Repairs table.
Q: Just the ones you sell, or others too?
A: Our own and other similar toys.
We can't just identify the repaired toys by ToyID; we'll need an AutoNumber field to uniquely identify toys made or purchased elsewhere that come in for repair.
Q: Are the repairs done in-house, or contracted out?
A: In-house only.
We need a table of employees, with a field to identify those who do the repairs.
Q: Do you send out catalogs or other promotional materials?
A: Yes.
We need a table of customers, and also a table of potential customers or leads.
Q: By mail, email, or both?
A: Both.
The Mailing List table(s) should have both the mailing address and email address.
Q: Do you sell toys in a store, by mail, or over the Internet?
A: From a factory store and by mail or phone. No Internet sales yet, but maybe in the future that will be added.
We need an Orders table, with a field for sale type. Customers should be selected from a Customers table, with a provision for entering a new customer on the fly when taking an order. Since mail or phone orders will require both a shipping and a billing address, we need a linked table of shipping addresses.
From these answers, we know that the application needs primary tables for the following things (these are the application's entities):
Toys
Categories
Vendors
Customers
Shipping addresses
Mailing list
Materials
Repairs
Employees
Orders
Additionally, a number of linked tables will be needed, to store data linked to records in the main tables, and some lookup tables will be needed to store data for selection from comboboxes, to ensure data accuracy.
Creating Tables for an Application
Now that you have some information from the client, you can start creating tables and setting up relationships between them, turning a mass of inchoate data into a set of normalized tables representing the things (entities) the database works with. Using the list of tables obtained from the Q&A session with the client, let's start creating tables for the Toy Workshop application. First, though, a note on naming objects: applying a naming convention right from the start when creating a database will make it much easier to work with. I use the Leszynski Naming Convention (LNC), which is described more fully in Chapter 9, Reworking an Existing Application. For tables, the LNC tag is tbl, so all table names will start with that tag.
Table Creation Methods
To create a new table, click the New button in the Database window with the Tables object selected in the object bar. You have several choices in the New Table dialog, as shown in Figure 1.1.
Continues...
Excerpted from Expert One-on-One Microsoft Access Application Development by Helen Feddema 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.
Overview
What is this book about?
If you are developing databases for your own use, the process need not be complicated. But when you build databases for clients, many of whom may not be familiar with Access or comfortable with databases in general, you have a lot more work to do. Expert One-on-One Microsoft Access Application Development makes that process easier.
In these pages, you discover how to set up tables and relationships to ensure ...