Access 2003

John Wiley & Sons

Chapter One

Unlike other Office applications, such as Word and Excel, where you can easily open the application and start using it without much training, Access requires that you have some background information to be able to use the software effectively. For example, with Microsoft Word, you can open the application and start typing. At worst, you may have to retype or reformat some documents after learning more about the software. However, with database software such as Access, the way in which you create or "architect" your database determines how usable the information will be when you go to retrieve that information.

In order to create your database correctly, you must understand two things:

1. What a database is, and

2. What is meant by the term "relational databases"

RELATIONAL DATABASES IN THE REAL WORLD

You deal with databases in the real world all the time. When you deal with customers and do business with them, you record all the information and activities in real-world databases, or an element of one. For example, you track customer information such as company name and contact information. This information is related to invoice information. Along with tracking information on the invoice such as invoice date, invoice number,line item details, and total due, we must also track the customer to whom the invoice belongs. By putting the customer name or number on the invoice, you are relating the invoice to the customer. At your desk, you track information using manila files, with each file containing all, or part of, the business you do with a client. You may have one file that contains contacts made with a client, and another file that contains invoices. These folders are analogous to tables used in an Access database. There are many similarities between how you track information in the real world and how you track it on the computer. Now, take a look at how real-world information is stored in an Access database.

RELATIONAL DATABASES IN MICROSOFT ACCESS

When starting to work in Microsoft Access, you begin with a single file, called a database file. Within this database file are all the items, or objects, you need to track real-world information.

Tables

The main object you use in a database is a table. Tables store information about a topic or activity. Topics include subjects such as customers, invoices, or students. Activities include meetings, schedules, and other actions you may need to track.

Tables have structures, and those structures are made up of fields. Fields track the individual pieces of data for your tables. For a customer's table, called tblCustomers to use the correct naming convention, you can include fields such as CompanyName, ContactName, Address, City, State, and Zip. For more information on naming conventions, see Appendix A.

When you create a table, and add the fields, you can then add the data. You enter individual customers into the table, field by field. Access stores the individual customer data in records, also called rows, in the table.

Tables in the Database

You can store data in the database in which you also keep other objects, such as queries and forms. When working with small- to medium-size databases, with just a few people accessing the database, you can store all the tables in the same database.

Linked Tables

When you have a number of people accessing your database over a network, you may want to split your database so that the tables are in a separate database from other objects, such as queries and forms. You can use linked tables to access data owned by somebody else, either in a separate Access database, or other file types that Access supports, such as Excel worksheets, text files, and SQL Server tables. More about working with linked tables can be found in the last two sections of this chapter.

Primary Keys

Each table should have a Primary Key field. A primary key makes each row of data you enter into a table unique. This means that when you need to refer to a record within a table, you can do so by using the Primary Key field. You should use a field that is commonly used to individualize records from one another, such as CustomerID, SSN, or MemberID.

Note that while you can use multiple fields that make up a unique value for a primary key, it is not recommended for performance reasons and maintainability of the database. This type of primary key field is called a composite key.

Access provides a method for creating a type of field that automatically increments itself to make it unique for each record. This type of field is called AutoNumber. You should use an AutoNumber for the Primary Key field rather than creating a field you need to maintain yourself. When using an AutoNumber type field for a primary key, you are using a Surrogate Key.

Relationships

Access uses relationships between relational databases to link tables to each other. You can create relationships by telling Access which tables relate to others, and which fields are related. There are three types of relationships that you can use: One-to-Many, One-to-One, or Many-to-Many. Creating the actual relationships in Access can be found in the section "Establish Relationships," later in this chapter.

You create relationships using the Primary Key fields in tables, and relate them to other tables using Foreign Keys. The next few sections discuss the different types of relationships.

One-to-Many

This is the most common type of relationship. An example of a one-to-many relationship would be customers to invoices. One customer can have many invoices. The field on which you would base the relationship is the CustomerID field in both tables. In the invoices table, the CustomerID field is a foreign key to the CustomerID field in the tblCustomers table, where it is the primary key.

One-to-One

Probably the least used, this relationship type relates two tables on a one-to-one basis. An example of this type would be a tblCustomers table that contains common information such as name and address, and a tblCustomersFinancial table that contains more sensitive information such as credit and income information for each customer.

Many-to-Many

This relationship is more complicated in that it is made up of two one-to-many relationships. An example of this would be a student database. In a student database you would have tblStudents, tblClasses, and tblSchedules. Each student, represented by a StudentID field, could have many classes, and each class, represented by a ClassID field, could have many students. The schedule - StudentID, ClassID - would join the other two tables to make a many-to-many relationship.

OVERVIEW OF ACCESS

Access is made up of various "objects" that build on each other. When you open the database window, the object groups appear in a list. The objects are Tables, Queries, Forms, Reports, Pages, Macros, and Modules. While you can use some of these objects by themselves, such as tables, when you use them together they help you to create a much more robust and user-friendly database.

OBJECTS FOR DATA INPUT AND OUTPUT

Pages

Also known as Data Access Pages, or DAPs, pages present your Access data on the Web. Pages are a blend of both forms and reports: You can use them for both data input and information presentation. They are created in HTML, with a link in your Access database.

Pages and other Internet features are discussed in Chapter 15.

Forms

While you can enter data directly into a query or table using Datasheet view, you do not have much control over the way the data is entered. It is preferable to create forms that you base on queries or tables. You can use forms to help the user enter the data entered, and make the user's job much easier. In addition to table fields that you can place in forms, Access also provides an enormous number of controls that you can add to your forms to make entering data a logical and pleasant experience.

Among the controls used for entering data are text boxes, the most common. Text boxes allow you to enter data just as you would in a datasheet, only with more control. Other input controls include list boxes and combo boxes, which present the user with a list of possible data choices for a field. You also have command-button controls that allow you to lead the user through your application and allow the user to perform tasks.

Forms are discussed in many chapters, but the ways to create them are introduced in Chapter 4.

Tables

Discussed in the last sections, a table is the base object in which you store your data. You can enter data directly into a table using the Datasheet view, or use the table as a base for other Access objects.

Working with tables is discussed in this chapter starting in the section "Create a New Table Using Data."

Queries

Queries are used to retrieve information from your tables. With a query, you can limit the data the user can obtain, or update bulk information, such as when you want to update all costs of a sales item by 5%. You can also use a query to delete or add multiple records. Forms, reports, and pages can all be based off a query or a table directly.

Queries are covered in Chapter 2 and Chapter 3.

Reports

Just as forms can help users enter data into your database, reports are used to present information from your database. Access has a powerful report designer that allows you to create professional-looking reports as simple as a mailing list, or as complicated as complete loan documentation.

Reports are discussed in Chapter 8.

AUTOMATING AND DEVELOPMENT

Macros

Access uses macros differently than in other Office applications such as Word and Excel. In the other Office applications, macros are Visual Basic for Applications, or VBA, routines that you can create manually, or by using the Macro Recorder. In Access, macros consist of individual macro actions, created using a menu-driven editor. However, the macros in Access do not allow you to trap any kind of errors that may occur, and have other limitations. Also, you can use the DoCmd object that can be used in VBA modules for performing macro actions, yet control errors. Methods of the DoCmd object used in VBA reflect most macro actions you can use in macros.

Information about converting your macros and the DoCmd object can be found in Chapter 6.

Modules (VBA)

Modules store the VBA code you can create to handle automating your application. While this may seem intimidating if you do not come from a development background, it should not be. Access does supply some tools that create routines for various tasks. For example, if you add a Command Button control to a form, with wizards turned on, a wizard walks you through specifying what you want to accomplish with the control, and creates the code to do it, including error handling.

Using modules and VBA begins in Chapter 6, and continues through the rest of this book.

OTHER FEATURES

Importing and Exporting Data

One of the main features that make Access so popular is the ability to import and export data to or from other data formats such as text files, XML, Excel worksheets, SQL Server data, and others. You can import and export using the menus, or even automate the tasks using code.

Importing and exporting other data formats is discussed in Chapter 10.

Security

While this is a very important part of your database, due to the need for high security for sensitive data, handling security can sometimes be a real pain. Access gives you a couple of ways to secure your database. The easiest way is to use a single password. The more complete, but more difficult way, is to use user, group, and object permissions.

Both ways to secure your database are discussed in Chapter 13.

Automation of Other Office Applications

As with all of the Office products, you can control different Office applications from within Access, increasing the power of the Office platform. This enables you to take data from a table in Access and create a worksheet in Excel, for example.

Automation using VBA is discussed in Chapter 12.

Replication

Replication lets you create copies of your database, have users update data in the copies of the data, and then merge the changes back into your main copy of the database. As with most of the features in Access, this can be performed either using the menus or through code.

You can see how to replicate, or copy, your database by reading Chapter 14.

USING THE ACCESS DATABASE WIZARD

One of the features that make Access such a popular application is its wizards: They do so much to get you started. You can use the Access Database Wizard to create complete database applications. Another term for the various types of databases that can be used is template. In Access, a template guides you in creating database applications for a specific topic. You may be familiar with templates in other applications such as Word and Excel.

In Access you find templates for database applications such as Asset Tracking, Contact Management, and Expenses, to name a few.

After the wizard creates the database file, you can specify where you want to store it. In addition, it creates the various tables, queries, forms, reports, and other objects that are necessary for the template that you have chosen. With the wizard, you can choose what columns you want to include for each table, so that you can customize it to your needs. You can also specify formats for forms and reports. The wizard also generates a main switchboard, which allows you to pick various data entry forms you want to access, as well as reports that the database application generates.

The Access Database Wizard is a great way to start using Access because it can give you an idea of what you can create on your own. When you examine the finished product, you can see how to create your own application. You can do this by pressing F11 and walking through the various objects in the database window, after the Database Wizard has completed.

USING THE ACCESS DATABASE WIZARD

1 Start Access, and then click File [right arrow] New.

2 Click On my computer.

* The Templates dialog box appears.

3 Click the Database tab.

4 Click a template.

* This task uses the Expenses template.

5 Click OK.

6 In the File New Database dialog box that appears, click Create to accept the default name.

* The Database Wizard appears.

* The introduction gives you an idea of what features the new database will include.

7 Click Next.

* The tables the wizard will include in your database appear along with the fields they will contain.

Continues...

---

Excerpted from Access 2003 by F. Scott Barker 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.

---