Microsoft Access is a Relational Database Management System (RDBMS) designed to run in Microsoft Windows. As yet, Access does not have versions for DOS or for Macintosh. Unlike older Database Management Systems for IBM-compatibles, Access takes advantage of Windows' graphical interface to simplify complex database operations and does not require you to learn sophisticated programming languages. The benefit of a DBMS lies in its ability to employ your data successfully, and Access contains powerful querying and connective capabilities that allow you to easily manipulate the data in your database. Accessí graphical interface also makes designing sophisticated forms and reports easy, which facilitates effective data presentation both on-screen and in publication-quality documents.
Access 97 vs. Access 2000 The biggest difference between these programs is not readily visible. Microsoft designed Access 2000 to be international, building the database in a character-set called UNICODE. Access 97 uses ASCII, a character subset of UNICODE that contains characters mostly useful for American English words. (ASCII doesn¹t have much accentuation, etc.) This fundamental change in the underlying database construction has two results: 1) your database is bigger because UNICODE takes up more space; and 2) Access 97 cannot read full-fledged Access 2000 databases because Access 97 doesn¹t understand UNICODE. Solution? Either migrate entirely to Access 2000; keep a copy of Access 97 on your machine for shared databases; or use Access 2000 to open your database "read-only," meaning that you can edit data in the tables of the shared database, but you cannot change the structure of the database (add/edit tables, fields, queries, relationships, etc.).
Data are always stored in tables, and thoughtful design of the tables in your database can save you hours or even days of work later on. Each table contains information about one subject, such as employees, members of the class of 2000, or addresses. Most relational databases have many tables, each storing different information about a related subject. One table might store customer names and addresses while another stores customer orders. Tables are two-dimensional grids, containing two axes: fields, which are logical and discrete divisions of data; and records, which contain an actual instance of each field you have defined in your table. Each record contains information about a particular item among your data, such as employee or address; and within each record there will be a value for every field in your table (even if this value is NULL or empty). Therefore, it is always a good idea to make the fields as precise as you can in the beginning. If you were designing an address book, it would be better to include three fields for First Name, Middle Name, and Last Name, rather than to include only one field for a personís name. (This division makes for easier searching as well.)Viewing Options for Tables
Access Rules to Remember Against common sense, it is easier to create than to destroy. That is, you can always put the information in multiple fields back together later on, with relative ease. It is much more difficult to divide up a single field later on.
Since your viewing options differ depending on where you are in the program and what kind of object you wish to view, look at the View menu to see what your options are. In the Database window, for example, your viewing options are limited to choosing what kind of object you wish to examine. When you are working with Tables, however, you can select either Datasheet View or Design View. Inside each object, you will have different views of that object. The most useful ones are listed below and can be found either through the View menu or on the toolbars inside of each object.
- Design View allows you to see the way in which an object has been put together and to modify its structure or layout. This view can be accessed for any Form, Report, Table, or Query.
- Datasheet View allows you to look at the data in a table or query in a spreadsheet-like format. It is available for any Form, Table, or Query.
When you create a table, you should think carefully about the kind of data it will contain and the way you will want to use that data later on. Access makes designing certain kinds of tables easier for you by providing Wizards that automatically create tables for things as diverse as personal exercise logs, cataloging record and book collections, medical records, contact lists, and student/class rolls. The Wizards work fine for many tables, but you will find that creating your own tables offers much more flexibility for your database.
- In the Database window, click the Table button (or choose Tables from the View menu).
- Choose the New button.
- Choose the Table Wizard button.
- Follow the directions in the Wizard dialog boxes for adding fields to your table.
- In the Database window, click the Table Object (on the top in 97, on the left in 2000).
- Choose the New option (on the right in 97, on the top in 2000).
- Choose the Design View button or the Datasheet View button.
- The tableís Design window or Datasheet will appear.
Access offers you two different ways to retrieve data from tables created in other programs: importing the table into Access and creating a link to the table. If you wish change the structure of the data you are connecting to, you should choose Import Table. But while this option will allow you to add, remove, or resize fields in your table, once you have copied the table into your database, you will not see any subsequent changes to the original data source. If you would like to ìshareî data between the original database and your new database, you should choose Link Table. This option will allow you to see and propagate your own changes between the databases. As you might expect, though, the Link Table option will not allow you to change the structure of your table. It comes down to your purposes with Access: if youíre transferring all your data to Access, use the import function; if youíre somewhere in-between Access and some legacy system, use the link function. And itís important to note that even if youíre not somewhere between Access and another database, you may still find yourself debating the import/link options. When sharing data between various Access databases, you may find that you need to decide whether to copy the other data or just connect to it.
While you can choose to create a table in either Datasheet or Design View, the Design Window offers much more flexibility in creating a database. The first noticeable difference between the design window and the datasheet window is that the standard toolbar has been replaced by the Table Design toolbar. This toolbar offers several shortcuts to make designing tables easier, such as selecting a Primary Key, inserting and deleting rows, and listing properties. The Table Design window itself is divided into two principal parts: the area for creating field names and descriptions, and the Field Properties area below it.
The Field Name is a descriptive identifier for a field that can be up to 64 characters (letters or numbers) including spaces. The names should be descriptive enough that anyone can easily identify them when viewing or editing records. For example, LastName, FirstName, StreetAddress, or HomePhone. To create a new field, simply position your cursor in a blank cell and enter a new name.
Access Rules to Remember Think "beyond the box" when you name your fields. That is, you should think about other databases when you name fields. Some databases donít like spaces in their field names. If you think might ever use your database outside Access (including Internet connections with ODBC), youíll save yourself some headaches by naming your fields without spaces from the beginning. Solution? Either capitalize the first letter of each word in your field name or replace the spaces with underscores (_).
A Field Description, an even more descriptive identifier for the field, helps you make your tables easier to understand and update. For example, if you are making a database of all the records you own and have a Name field, you might clarify that entry by describing it as ìThe title of the album, CD, or tape.î This somewhat optional field is actually much more important than you think. As you or your users traverse the fields in your tables (or later in your forms), this description will appear in the bottom left-hand corner of the screen, giving those who may not understand your field names a better idea of what information should be entered into the field. Accordingly, you might want to flesh out your field descriptions, even giving them an example to work from (ìThe year of release (e.g., 1999).î).
After you name a field, you choose a data type for the data to be contained in the field. When you choose a fieldís data type, you are deciding:The following table summarizes each data type.
- What kind of values to allow in the field. You cannot store text in field with the Number data type.
- How much storage space Access is to set aside for the data in that field.
- What types of operations can be performed on the values in that field. Access can find the sum of Number or Currency values but not of Text values.
- Whether Access can index or sort data in the field. Access cannot sort or create an index for Memo or OLE Object fields.
Data Type |
Stores |
Size |
Text |
Alphanumeric characters |
Up to 255 characters (255 bytes) |
Memo |
Alphanumeric characters that are usually several sentences in length |
Up to 64,000 bytes (large text fields). NOTE: Memo field is not searchable! |
Number |
Numeric values (integer or fractional) |
1, 2, 4, or 8 bytes |
Date/Time |
Dates and Times (various formats) |
8 bytes |
Currency |
Monetary values |
8 bytes |
AutoNumber |
A numeric value that is automatically incremented for each record added |
4 bytes |
Yes/No |
Simple yes or no, true or false |
1 bit (8 bits = 1 byte) |
OLE object |
OLE objects, graphics, or other binary data |
Up to 1 gigabyte |
Hyperlink |
Addresses for documents that you want to link to that are stored outside your database |
Up to 2048 characters |
Lookup Column |
Creates a drop-down list of values to choose from in Datasheet View |
Up to 255 characters |
The Field Properties section of the Design window is the box at the bottom of left-hand corner of the screen (see graphic on page 3). It allows you to control the appearance of data, prevent improper entries, and specify default values. Access uses the field property settings when displaying data to be viewed or edited. The Format, InputMask, and Caption properties you set will appear in table and query datasheets, as well as any controls on forms or reports that are based on these fields. Other properties can be used to establish validation rules for data entry or to require data to be entered in a field.
The FieldSize property allows you to adjust the maximum size of fields with either Text or Number data types. Setting the FieldSize property ensures that the field can store any data you want to enter, and that numbers entered are not larger than you intend. For a text field, the FieldSize can range from 1 to 255 characters or bytes (50 is the default). You would use this to limit the field entries to a fixed number of characters. Unlike older databases, Access does not use any characters beyond what is needed for the characters of your entry, so this value is not as crucial as it once was. For Number fields, the FieldSize property determines the range of values that can be stored in the field and whether the field can contain fractional values. It is always a good idea to use the smallest setting you can because Access works faster with smaller field sizes.
Access can display values like numbers and dates in many formats. The format affects only the data display, not the value stored in the table. If you do not set the Format property, Access defaults to the General format, and all data will appear exactly as you enter it. Like Excel, Access will let you choose a date's display from international data formats, such as Currency, Long Date, and Short Time. If your data is not displayed in the correct form for a country you are working with, then change your Regional Settings (by choosing Settings from the Start menu, going to the Control Panel, and choosing Regional Settings).
The DecimalPlaces property lets you set the default number of decimal places to appear in a Number field.
If all of the values that you will be entering into a field will have the same format, then you can simplify the data entry by specifying an input mask. InputMask automatically shows literal display characters in a field. For example, if you are entering in phone numbers, then you can create a mask that contains the parentheses for the area code and dash between the 2 sets of digits in the phone number. This ensures that you only have to enter numbers. With an input mask, Access makes certain that information you enter fits the mask that you have defined, and only values of the type you specify can be entered. Another use of InputMask is to protect confidential information by using the ìPasswordî InputMask.
The caption property allows you to specify a label other than the default label (field name) to be used in new forms and reports.
If you have a value that occurs often for a particular field, then the DefaultValue property allows you to automatically enter that value in new records. If you enter another value, then the default will not appear. The default value will also appear on forms, unless you enter a different value.
Access automatically validates entered values based on whether they fit a fieldís data type. For example, you cannot enter a text value into a numeric field. You can also establish more specific rules for data using ValidationRules. For example, you can specify that numbers entered into a data field be greater than 100 by typing ">100." Once you set these new boundaries, you will want a message to tell the user why an entry cannot be used. This message is the ValidationText.
If you want a field to contain a value for each record, set the Required property to Yes. Once set to Yes, Access does not allow a null value to be stored in the field; you must enter a value or the record will be deleted.
Access works most efficiently if you identify a primary key in each table. The primary key consists of one or more fields that uniquely identify each record you store in the table, such as the call number for a book in a library card catalog. Often, the best primary key is an ID number or code, since this value is always different for each record. A good way to set a primary key is to insert an Autonumber field in a table and set that as an ID number for each record. To do this, you specify the data type as Autonumber, and Access will add the numbers automatically. You have to establish a primary key if you wish to create default relationships between tables or if you want to update two or more related tables at the same time.
- Select the field that you want to use as the primary key by clicking in the field selector box to the left of the field. To select multiple fields, hold down the <Ctrl> key and click the field selector to the left of each field that you wish to include.
- From the Edit menu, choose Set Primary Key (or click the toolbar's Set Primary Key button).
Note: Access will add a key indicator to the left of the field or fields that you specify.
After creating a table, you need to save it, either by selecting Save from the File menu, or by clicking on the Save button on the toolbar. When you save a table, you are saving the tableís format and design features along with any records that are stored in the table.
In Design view, you can easily review and edit the fields that make up any table. Deleting, rearranging, and inserting fields into a table can be accomplished with a minimum of effort. You can also rearrange the field order in Datasheet view; however, this will have effect only for the current editing session. The only place to make permanent changes is in Design View.
- Select the row that defines the field you want to delete by clicking in its row selector.
- From the Edit menu, select Delete Row (or press the Delete Row button on the toolbar).
- Select the row that defines the field you want to move by clicking in its row selector.
- Click the row selector again, hold the left mouse button down, and drag the row to where you want it placed.
- Click on the row above which you want the new row to appear.
- From the Edit menu, select Insert Row (or press the Insert Row button on the toolbar).
The other way to look at your table is in the Datasheet view. Generally, you will want to use the Design view to work on layout and other formatting options. The Datasheet view is most helpful for entering new records and editing old records in a table. To switch to Datasheet view, choose Datasheet from the View menu or click the Datasheet View button on the toolbar.
An Access 2000 Note Access 2000 offers you a way to view data from other tables directly in your current table. After you have created the relationships between your tables (see below), you can click on the plus sign (+) on the far left of each record in your table to see related records in other tables.
- Make sure that your cursor is situated in the first blank cell on the table, then enter the desired data. If you are using an existing database, the "first blank cell" is at the bottom of the table. Watch the graphics symbol in the record selectors box just to the left of your record. It will change from the arrowhead, which indicates that it is the current record, to a pencil, which indicates that you are editing this record. Note: It will still be a pencil even if it is the first time that you have entered the record. If the locked record symbol appears, then the record has been locked by another user, and you do not have access to it.
- To get to the next field of the record, press <Tab> or use the mouse and click on the next cell. If you want to return to an earlier field, then press <Shift> + <Tab>.
- To save a record, you do not need to do anything more than move to the next record after entering data in the recordís last field. You can also save the record by closing the table.
Warning: If you reboot your machine or exit Access while editing a record, you will lose the record.
- Select the record that you wish to edit by clicking in the specific field you want to change.
- Type in the new data for that field.
- When you move to another record, the new data will overwrite the previous data.
Note: Access will not remind you that old information will be lost when you edit data.
You can also add and delete fields in Datasheet view, but you do not have the same control over specifying parameters that you do in Design view. To create a new field in Datasheet view, either go to the Insert menu and choose Insert Column, or right-click where you want to add the new column and choose Insert Column from the menu displayed. To delete a field in Datasheet view, select the field by clicking in the field heading, go to the Edit menu and select Delete Column. You can also right-click on the field heading and choose Delete Column from the displayed menu.
When you initially create a table, it is not related to any other object in your database. In other words, all of the data must be entered into that table independently of all other tables. It is not desirable, however, to have to enter thousands of records into two separate tables. In order to cut down on this kind of work, relational databases allow you to link a field in one table to a field in another table. Usually, you will want to make sure that the fields share the same name to ensure that the data entered in each is the same (and to make it easier for you to remember that the fields are related). After you have established a relationship, then you will only need to enter the data in one table for it to be updated in more than one table.
- In the Database window, choose Relationships from the Edit menu.
- Choose Add Table from the Relationships menu.
- In the Table/Query box, select the table you want to create a relationship for and press the Add button. To select more than one table or query at one time, hold down the <Ctrl> key and click on each object you want to add.
- Drag the field(s) that you want to relate from the Field List of a table to the matching field in another table or query. After you have finished selecting the options in the Relationships dialog box, a line will appear between the two related fields. The original table that you selected is known as the Primary Table and the second table that you selected is known as the Related Table.
When you establish relationships between objects, there are three variables that you should be thinking about: Referential Integrity, Types of Relationships, and Cascading Delete/Update Operations.
Referential integrity helps to ensure that relationships between records are valid and that you do not accidentally delete related data. Access can enforce referential integrity when the matching field from the primary table is a primary key or has an unique index, the fields have the same data type, and both tables belong to the same Access database. When referential integrity is enforced, there must already be a record in the primary table to add a value to the matching field. Also, you cannot delete a record from a primary table if matching records exist in the related table. Access will adhere to these rules for a relationship if you select the Enforce Referential Integrity option when you create the relationship.
For relationships in which referential integrity is enforced, you can specify whether the tables you are relating have a one-to-many relationship or a one-to-one relationship. In a one-to-many relationship, the most common type, each record in the primary table can correspond to several records in the related table. For example, an author # field in a library database could be linked to any number of works that author has written. In a one-to-one relationship, each record in the primary table can be associated with only one record in the related table. In many cases, the matching fields on both sides of the relationships will be primary keys or have an unique index.The most powerful relationship in a database, however, must be created indirectly in Access. As you examine your data, youíll realize that most real-world relationships are not one-to-one or even one-to many, but many-to-many. In order to create this relationship in Access 97 or 2000, you must create two one-to-many relationships, placing an intermediate table between your two related tables. So, for example, if you want to relate names to addresses based on two separate tables, you must first ask yourself what type of relationship you are talking about. The questions to ask in this case are: Can a person (from the Names table) have more than one address? and, Can an address contain more than one person? If the answer to both of these questions is ìyes,î then you need to create a many-to-many relationship by creating an intermediate table whose two required fields are both pointers to the primary keys of each table (here, NameID and AddressID). These duplicates of primary keys in other tables are called foreign keys, because they are native to another table, but necessary here in creating the relationship. Then you can place relate any number of names to any number of addresses.
For relationships that have referential integrity, you can specify whether you would like Access to automatically ìcascadeî delete or update operations for related records. If this option is selected, Access will make necessary changes in related tables when records are deleted or primary key values are changed in a table. Access automatically removes related records in the related table anytime you delete records in the primary table if you select the Cascade Delete option. The Cascade Update option follows the same pattern and updates all related records in a related table when the primary key is changed in the primary table.
