Microsoft Access 2010 Bible

Chapter 2: Creating Access Tables

39

Creating a New Table

Creating database tables is as much art as it is science. Acquiring a good working knowledge of the

user’s requirements is a fundamental step for any new database project.

Cross-Reference
Chapter 3 covers the details of applying database design rules to the creation of Access tables.

In this chapter, I show you the steps required to create basic Access tables. In the following sec-

tions, you’ll study the process of adding tables to an Access database, including the relatively com-

plex subject of choosing the proper data type to assign to each field in a table.

It’s always a good idea to plan tables on paper first, before you use the Access tools to add tables to

the database. Many tables, especially small ones, really don’t require a lot of forethought before

adding them to the database. After all, not much planning is required to design a table holding

lookup information, such as the names of cities and states. However, more complex entities, such

as customers and products, usually require considerable thought and effort to implement properly.

Although you can create the table interactively without any forethought, carefully planning a data-

base system is a good idea. You can make changes later, but doing so wastes time; generally, the

result is a system that’s harder to maintain than one that you’ve planned well from the beginning.

In the following sections, I explore the new, blank table added to the Chapter02.accdb data-

base. It’s important to understand the steps required to add new tables to an Access database.

Because the steps required to add tables have changed so dramatically from earlier versions of

Access, even experienced Access developers will want to read the following sections.

Most Access developers eventually adopt a naming convention to help identify database objects. Most

naming conventions are relatively simple and involve nothing more than adding a prefix indicating an

object’s type to the object’s name. For example, an employees form might be named frmEmployees.

As your databases grow in size and complexity, the need to establish a naming convention for the

objects in your databases increases. Even with the Name AutoCorrect option turned on (click the File

button and choose Options ➪ Current Database ➪ Name AutoCorrect), Access only corrects the

most obvious name changes. Changing the name of a table breaks virtually every query, form, and

report that uses the information from that table. Your best defense is to adopt reasonable object names,

use a naming convention early on as you begin building Access databases, and stick with the naming

convention throughout the project.

Access imposes very few restrictions on the names assigned to database objects. Therefore, it’s entirely

possible to have two distinctly different objects (for example, a form and a report, or a table and a

macro) with the same name. (You can’t, however, have a table and a query with the same name,

because tables and queries occupy the same namespace in the database.)

continued

The importance of naming conventions