Creating Tables

Tables are the backbone of any database. Because they store all of the data, designing them correctly the first time is crucial. The type of data you need to store in any given table is dictated by its purpose in the application. For example, if you need to store the date on which some event occurred, you would use a Date/Time field data type. You could use a Text field type to store a date and there may be cases where that makes sense, but most of the time, the Date/Time type will be more beneficial because it enables you to leverage the comparison operations provided by the ACE database engine, which you could not do with the Text field type.

Creating tables through the Access 2007 UI is quite easy. When a database is open, the Access Ribbon has four tabs—Home, Create, External Data, and Database Tools—by default. In previous versions of Access, you could create new tables through the UI via the Insert menu or the Database Container window. In Access 2007, you create all database objects through the UI via the Ribbon's Create tab.

Click the Create tab. The Ribbon changes to show all of the various entry points for creating Access database objects. There are four options for creating tables: Table, Table Templates, SharePoint Lists, and Table Design. Figure 1-4 shows these options.

Click the Table Templates Ribbon button and the template fly-out menu appears. Click the Asset table template and a new table opens in Datasheet View mode, complete with all of the fields found in the Assets table. This is a great starting point for a new table because much of the work of setting up the table structure has already been done for you—and all it took was a few clicks of the mouse.

Now right-click the new table's Document tab and choose Design View to open the new Assets table in design mode. Because the table has not yet been saved, you are prompted for a table name to Save As. In the Save As dialog box, type in the name Assets and click the OK button. The Assets table is saved and opened in design mode for editing. Figure 1-5 shows the Assets table in the Table Designer.

Table Tools Issues: Database (Access 2007) - Microsoft Access

External Data Database Tools

View Views


Builder Test Validatioi Rules Tools

Insert Rows Delete Rows ^Lookup Column

Property Indexes Sheet

^ Security Warning Certain content in the database has been disabled Options...

All Access Objects


^ Assets

S Contacts

H Issues



¡¡p Contacts Extended

[p Open Issues



¡H Add Related Issues m Contact Details

H Contact List

IH Issue Details

^f] Issue List

Issues by Status Chart

HI] Issues Datasheet Subforn

¡H Add Related Issues m Contact Details

H Contact List

IH Issue Details

^f] Issue List

Issues by Status Chart

HI] Issues Datasheet Subforn

Reports it

HU Closed Issues

Contact Address Book Contact Phone Book Issue Details Open Issues lJ Open Issues by Assigned To HU Open Issues by Category

|ff] Issue List ^eTI Assets


Description Category Condition Acquired Date Purchase Price Current Value Location Manufacturer Model I Comments Attachments

Data Type AutoNumber Text Memo Text Text









Description a

Field Properties

General ¡Lookup]

Field Size

Long Integer

New Values





Yes (Mo Duplicates)

Smart Tags

Afield name can be opto 64 characters long,

Text Align


including spaces, Press F1 for help on field


Num Lock JE) e

Figure 1-5

In Access 2007, ten different basic field data types are supported by the ACE database engine: Attachment, AutoNumber, Currency, Date/Time, Hyperlink, Memo, Number, OLE Object, Text, and Yes/No. In addition to these field types, ACE, and Jet databases support table field lookups to other tables through either queries or system relationships. Another new feature in Access 2007 is the capability to create complex data from certain data types (but not every data type). Complex data give the user the option to select multiple values from a value list or query for a single record. Additionally, when examining the data in the Complex Data field, all selected options can be taken as the value or each of the individual items (the scalar values) can be examined for the field. The following table provides a brief description of each data type's purpose and whether it supports complex data.

Was this article helpful?

0 0

Post a comment