There are three ways of entering data into a table: entering one field at a time using Datasheet view, by using a form, and importing data from an external source. We have previously used the Datasheet view to enter data, so we will explore the other two methods in this part of the lesson.

A form is one of the simplest ways to enter data. You can only enter data for one record at a time, and each field is easily laid out to avoid confusion when entering data. To understand the difference of entering data using a form and entering data using Datasheet view is best shown by example. Here’s a reminder of what a datasheet looks like:

 

 

The top row lists the name of each field. Although completely functional, it might not be very practical for someone who is just entering data. Therefore, you can make use of a form for data entry. A good example of a form can be seen by opening the Northwind sample database provided by Access:

 

 

From the Main Switchboard (which is actually a form!), clicking the Products button opens the Products table by way of a form. Here you can easily add a new product and let Access do the hard work for you. Let’s examine the form example above:

Product ID	This is the primary key, and is of AutoNumber format. Since the box itself is greyed out, you cannot modify this field.
Product Name	Type the name of the new product here.
Supplier	Click the pull-down arrow to see all available suppliers. If the product is from a new supplier, you will first need to enter the supplier information using the Suppliers form (activated by clicking the ‘Suppliers’ button on the Main Switchboard.
Category	Click the pull-down arrow to see all available categories. Again, if you need a new category, click the ‘Categories’ button in the Main Switchboard and enter a new category before filling in the product information.
Quantity Per Unit	Enter the item description here.
Unit Price	Enter a price for the item.
Units In Stock	How many available units are ready for sale
Units On Order	How many units are coming from the supplier.
Reorder Level	The maximum on-hand units allowed before a new order must be placed.
Discontinued	This field is a special type of field: a Yes/No option (or, more properly, a Boolean field). If unchecked (or ‘false’), the item is active. If checked (or ‘true’), the item is discontinued.

 

We will explore forms more in the coming sections of this manual.

The third method of adding data to a database is by importing data from an external source. For example, if your database is being constructed from other lists already in electronic form, Access likely has the ability to import the existing data. This will save a lot of time and resources by NOT re-typing the entire database! But before you import anything, it is important to keep things as consistent as possible. Consider the following Microsoft Excel spreadsheet:

 

 

You have a list of employees and want to import this list into a table in Access. The data in the picture above is nice and neat, but perhaps not all that meaningful. To aid in the importing process, it is better (though not necessary) to have column titles that will correspond to field names in your new database table:

 

 

To import data from an external source, click File à Get External Data à Import.

 

 

When the Import dialog box appears, locate the folder containing the source data by clicking the pull-down arrow beside the Look in: field. Next, select the file type by clicking the pull-down arrow beside the Files of Type field:

 

 

Highlight the name of the file you want to use (‘employees’ in this case) and click the Import button. The Import Spreadsheet Wizard dialog box will appear:

 

 

Access examines the file structure of the source file and extracts some information from the file. The source file in this case is fairly small, but Access has the functionality to read and utilize files that are very large in size, containing thousands of pieces of data. For the purpose of this example, we will let Access choose the best route. Click the Next button:

 

 

The next page of the Wizard indicates that it can use the column headings as the field names in the new table. In most cases, this is a good idea and in our case it is, so click the Next button. Access asks where you would like to store the new data:

 

 

If you have an existing table containing data similar to the data you are importing, you can directly import the new data to an existing table. Click the In an Existing Table radio button and then select a table from the pull-down listing. For our purposes, we want to make a new table. Click Next:

 

 

Here you can specify how Access will actually import the data. For the purposes of this manual, we will leave the default settings as they are. Click Next:

 

 

Here you can modify how Access will use a primary key. The default setting for this step is to let Access add a primary key, which would add a new AutoNumber field to the left of the current Employee ID field. But since we already have a field that can be used as a primary key, click the radio button marked Choose My Own Primary Key and make sure that Employee ID is selected from the drop down list. Click Next to advance to the final step:

 

 

Here you can name the table. By default, the Wizard names the new table the same name that the source data came from. In our example, the default name was Sheet1 because all of the data was imported from Sheet1 of the Excel spreadsheet named employees. However, Employees is a much more meaningful name for our table, so enter the name in the Import to Table field. Click Finish to complete the Wizard.

Access will import the data from the external source and alert you when the operation is finished:

 

 

You can view the data that Access imported by double-clicking the Employees object from the Tables page of the Database window:

 

 

This Datasheet view of the table should already be familiar to you. Here you can enter more employees into your database or modify the information already present.

Access 2003 has the ability to import data from many different sources: text files, HTML documents, other database programs, Microsoft Outlook, XML documents, and from external data sources not stored on your machine, such as a database contained on your business network.