Ms Access databases are limited to number of concurrent users the database can handle. ODBCs can be used to access the database to view records by multiple users. However, as the rows are locked during updates, updating the database by different users simultaneously can slow down the database. Although there is no direct limit to the number of records an MS Access database can handle, Importing and exporting huge amounts of data can be a problem for Ms Access. VB scripts and Macros can be used on Access databases to enhance database usability, for example the auto exec macro can be used to display the main database menu each time a specific database is opened.
Each time you run Microsoft Access, you are prompted either to open an existing database or to create a new one, select the Blank Database option, and then click OK.
You then are prompted to name your database. Microsoft Access databases have an .MDB suffix. You don't need to supply this suffix; it is provided for you. Type MyData in the File Name text box, and then choose Create. Microsoft Access creates the new database and then displays the Database window.
CAUTION: With some operating systems, such as Windows 95, you can create file names with spaces in them, like "My data.MDB." Although it is a legal operating system file name, it could create problems when you're accessing the database via ODBC. As a rule, never use spaces in database names.
You now have a created a new empty database. The database, you may recall, does not do very much itself. It is like a filing cabinet that you can use to create new folders, in which you can then store information.
Now that you have created your database, you need to create tables to store your different data sets. To create a table, select the Tables tab in the Microsoft Access Database window, and then choose New.
You then are prompted to select a view or wizard to create the new table. Microsoft Access provides several views and wizards to assist you in creating tables.
TIP: The Microsoft Access Table Wizard is an automated feature for creating tables. If you select the Table Wizard option from the New Table dialog box, Microsoft Access prompts you to specify the type of data you will store in this table, and then it suggests columns that you may need.
The Table Wizard is a useful tool that you will probably use extensively when creating your own tables. For the example, choose Design View instead so that you can learn more about the table creation process.
You then see the Microsoft Access Table design view. The view is divided into three columns. In the first column, Field Name, you specify the name of this column. In the second column, Data Type, you select the column's data type. At the bottom of the view window is the Field Properties section, where you can specify field length, default values, index requirements, and more.
NOTE: Microsoft Access also provides a third column, Description, where you can save descriptive notes about a particular column. Data you enter into this column appears on the Microsoft Access status bar each time the field is accessed. Because this column is not used when accessing the database via ODBC, you can ignore it for these examples.
CAUTION: Microsoft Access uses the term field to describe an SQL table column and record to describe a row.
TIP: Many database systems, including Microsoft Access and Microsoft SQL Server, allow table names and column names to contain spaces. Although these are valid names, some ODBC drivers have difficulty using names with spaces in them.
As a rule, never use spaces in table or column names.
You now can specify all the columns that make up the first table, the employee list table. Fill in the first two columns in the database design view with the values in the Field Name and Data Type columns in Table 1.1. The Field Properties list any properties that you need to set for a specific column. For example, in the EmployeeID column, you should set the New Values option to Increment.
| Field Name | Data Type | Field Properties |
| EmployeeID | AutoNumber | Select Increment from the drop-down list in the New Values field. |
| FirstName | Text | Type 30 in the Field Size field. |
| MiddleInit | Text | Type 1 in the Field Size field. |
| LastName | Text | Type 30 in the Field Size field. |
| Address1 | Text | The default size is 50. |
| Address2 | Text | The default size is 50. |
| City | Text | Type 40 in the Field Size field. |
| State | Text | Type 5 in the Field Size field. |
| Zip | Text | Type 10 in the Field Size field. |
| PhoneHome | Text | Type 20 in the Field Size field. |
| PhoneCellular | Text | Type 20 in the Field Size field. |
| PhonePager | Text | Type 20 in the Field Size field. |
| SocialSecurity | Text | Type 11 in the Field Size field. |
| DateOfBirth | Date/Time | |
| DateOfHire | Date/Time | |
| Title | Text | Type 20 in the Field Size field. |
| DepartmentID | Number | Select Long Integer from the drop-down list in the Field Size field. |
| PhoneExtension | Text | Type 4 in the Field Size field. |
| Text | Type 30 in the Field Size field. |
The EmployeeID column has a data type of AutoNumber. AutoNumber is a special Microsoft Access data type that is automatically filled in for you each time a new row is added to your table. The value in an AutoNumber column is guaranteed to be unique for every row, and by specifying Increment as the New Value option, you instruct that each time a new employee is added to the table, the EmployeeID should be set to one higher than the last value used. Because the EmployeeID column is going to be used as a primary key, every employee must have an EmployeeID, and all IDs must be unique. The AutoNumber data type provides a simple way of accomplishing this task.
NOTE: AutoNumber is a Microsoft Access-specific feature. Not all database systems have this feature, although many do. Those systems that do, however, usually have a different name for it. With Microsoft SQL Server, for example, you can specify a column as an IDENTITY column and specify how the identity value should be generated.
Almost all the text columns have a specified field size. The default field size in Microsoft Access is 50 characters. This number is more than is needed for name or ZIP code fields, so for each column, you specify a size that makes more sense. If you leave the field size at its default value, you waste lots of storage space.
The two date columns, date of birth and date of hire, are specified as Date/Time columns. You do so to preserve disk space, as well as to ensure correct data sorting, as explained in detail in Chapter 6.
You might wonder why you specify the DepartmentID column as a number instead of text. This is a good example of data normalization. Remember, the rule in data normalization is to never repeat data. If you were to enter the complete department name for every employee into this table, you would have many occurrences of each department name. Every salesperson would have the word Sales in the department column. So you define the department column as a number. Each department is assigned a number, a department ID, and that number is stored in the employee record. If Sales is department 4, then every salesperson has the value 4 instead of the word Sales in the DepartmentID column.
The DepartmentID column has a size of Long Integer specified. With Microsoft Access, like many database systems, you can specify the width of numeric columns. A single-byte column can store 256 values (0-255). A two-byte column can store 65,536 values. As it is unlikely that you'll have 60,000 departments in your company, a single byte is probably sufficient. However, because this column is used to relate this table to the Departments table, it must have the same data type. The Microsoft Access AutoNumber data type is always a Long Integer, so you must use a Long Integer here.
The Employee Table's Primary Key. You have now defined all the columns in
your employee list table. Before you save and name the table, however, you need
to specify the primary key and any additional index you want to use.
To specify that the EmployeeID column should be used as the primary key, follow these steps:
Microsoft Access then displays a Primary key symbol in the row selector to the left of the EmployeeID field name.
A primary key is automatically indexed when it is created, you can see that the Field Properties for the EmployeeID column has been changed to show that this column is now indexed and does not allow duplicates.
Next, you can create the indexes you need to work with this table. One of the sort orders you anticipate using regularly is sorting by phone extensions. The phone extension column is therefore a good candidate for indexing.
To create an index for a single column, follow these steps:
The other frequently used sort order is last name plus first name. To create an index for multiple columns, follow these steps:
Next From the File menu, choose Save to save the table. Then you are prompted for a table name. Type Employees in the Table Name field, and then click OK to save the table.
You have now created your first table!
The Departments Table. The Department field in the Employee table
contains a single value that identifies an employee's department. Your next task
then is to create the Departments table.
To create this new table, select the Tables tab in the Microsoft Access Database window, and then choose New. As before, you are prompted to select a wizard or a view. Select Design view, and then click OK.
The simple Departments table contains only two columns, as listed in Table 1.2. Enter the column information into the Design View window, and then select the ID field as the primary key.
| Field Name | Data Type | Field Properties |
| ID | AutoNumber | Select Increment from the drop-down list in the New Values field. |
| Department | Text | Type 30 in the Field Size field. |
Because the Departments table will never have more than 256 rows, you don't need to create an index to sort by the Description column. Microsoft Access can sort that many rows quickly without having an index, and adding an index creates additional overhead.
Now that your table design is complete, you can save the table with the name Departments.
The Vacations Table. One last table is needed to complete the employee
data set as defined in Chapter 6. The Vacations table also is a simple table.
Again, you create this new table using the design view and enter the column
information, as shown in Table 1.3.
| Field Name | Data Type | Field Properties |
| EmployeeID | Number | Field Size must be Long Integer to match the EmployeeID field in the Employees table. |
| VacationStart | Date/Time | |
| VacationEnd | Date/Time |
The table has no primary key to allow duplicates, but it does have one index. The EmployeeID column must relate back to the Employee table and therefore should be indexed. Set the EmployeeID Indexed property to Yes (Duplicates OK). You must select the Duplicates OK option; otherwise, no one can book more than one vacation. And that prospect is likely to make you very unpopular.
The Employee table and all its supporting tables are now ready to use. Next, you need to create the Inventory table. Like the department information in the Employee table, the Inventory table uses a category ID to identify title categories. This way, you can prevent the category information from being duplicated. Create the Inventory table with the information listed in Table 1.4.
| Field Name | Data Type | Field Properties |
| BookID | AutoNumber | Select Increment from the drop-down list in the New Values field. |
| CategoryID | Number | Select Long Integer from the drop-down list in the Field Size field. |
| ISBN | Text | Type 13 in the Field Size field. |
| Title | Text | Type 50 in the Field Size field. |
| Publisher | Text | Type 50 in the Field Size field. |
| PublicationDate | Date/Time | |
| AuthorFirstName | Text | Type 30 in the Field Size field. |
| AuthorLastName | Text | Type 30 in the Field Size field. |
| Pages | Number | Select Integer from the drop-down list in the Field Size field. |
| Description | Memo | |
| NumberInStock | Number | Select Integer from the drop-down list in the Field Size field. |
| DueDate | Date/Time | |
| Location | Text | Type 80 in the Field Size field. |
Again, every book needs a way to uniquely identify it, so you create a BookID field with a data type of AutoNumber.
The CategoryID column contains the ID number that identifies a book category. This number relates this book to its appropriate category in the Categories table.
The ISBN column has a data type of text, even though ISBN numbers are all digits. You make the ISBN value a text field for two reasons. First, ISBN numbers are usually formatted with hyphens in them, like 0-7897-0970-8 (this book's ISBN number). If you save the ISBN number as a number, formatting it correctly for display would be difficult. Second, if you treat it as a real number, ISBN numbers that start with 0, as this book's does, lose that 0 when the value is saved. The number 0789709708 is saved as 789709708. Obviously, you don't want this result, so sometimes you need to save numbers as text.
The Pages column is also defined as an Integer. Once again, the range of values of a byte is not enough; most books have more then 256 pages. Therefore, Integer, which can store numbers greater than 60,000, is a more realistic choice.
The Description column uses a data type you have not seen yet. Memo is a variable-length data type. This means that you do not define a field size; instead, space is allocated as needed. If you enter 3K (about 3,000 bytes) of data into the field, then 3K of space is used. If you enter 64K (about 64,000 bytes), then 64K of space is used. Memo is often used for notes that might vary dramatically in size from one row to the next.
CAUTION: Variable-length columns have one very important limitation. They cannot be indexed. Only columns with a fixed, known length can be indexed. If you're going to need indexed access to a column, do not use a variable-length data type.
NOTE: Different database systems use different terms to refer to variable-length columns. Microsoft Access and Borland dBASE use the term memo, whereas Microsoft SQL Server uses the term text.
In addition, the maximum size of data that you can store in a variable-length field varies from one database application to the next. Microsoft Access can store up to 64K (about 64,000 bytes) in a memo field. Microsoft SQL Server can store up to 2G (over 2 billion bytes) in a text field.
The NumberInStock column is also defined as a Number with an Integer for its field size. Again, 256 might not be a large enough range of values.
The Inventory Table's Indexes. The Inventory table has six indexes
that you should now create as follows:
Create a primary key on the BookID column.
Create a Duplicates OK index on the CategoryID column.
Create a Duplicates OK index on the ISBN column.
Create a Duplicates OK index on the Title column.
Create a Duplicates OK index on the Publisher column.
Create a Duplicates OK index called Author that indexes the AuthorLastName column plus the AuthorFirstName column.
Finally, save the table as Inventory.
The Category Table. The Category table stores category IDs instead of
categories, just like the department IDs in the Employee table. Now create the
Category table as detailed in Table 1.5.
| Field Name | Data Type | Field Properties |
| ID | AutoNumber | Select Increment from the drop-down list in the New Values field. |
| Category | Text | Type 30 in the Field Size field. |
Next, create a primary key on the ID column.
The Customer table is similar to the Employee table. Create this new table using the columns listed in Table 1.6.
| Field Name | Data Type | Field Properties |
| CustomerID | AutoNumber | Select Increment from the drop-down list in the New Values field. |
| Company | Text | Type 40 in the Field Size field. |
| FirstName | Text | Type 30 in the Field Size field. |
| MiddleInit | Text | Type 1 in the Field Size field. |
| LastName | Text | Type 30 in the Field Size field. |
| Address1 | Text | |
| Address2 | Text | |
| City | Text | Type 40 in the Field Size field. |
| State | Text | Type 5 in the Field Size field. |
| Zip | Text | Type 10 in the Field Size field. |
| Phone | Text | Type 20 in the Field Size field. |
| Text | Type 30 in the Field Size field. | |
| CustomerSince | Date/Time |
The Customer table will likely be searched in many different ways. And when a customer is waiting on the phone, you want rapid responses to your searches. As you learned in Chapter 6, using indexes creates some trade-offs. More indexes can improve the performance of searches and queries but slow down data inserts and updates, so you must make the decision of how many indexes to create for each table individually. Because of your need to respond quickly to the customer on the phone, the Customer table is a prime candidate for extra indexes.
Create the Customer table indexes as listed here:
Create a primary key on the CustomerID column.
Create a Duplicates OK index on the Company column.
Create a Duplicates OK index on the Phone column.
Create a Duplicates OK index called Name that indexes the LastName column plus the FirstName column plus the MiddleInit column.
Create a Duplicates OK index called Location that indexes the City column plus State column.
With all these indexes, you and your sales people should have no trouble locating customer information. If a customer's name was misspelled, for example, your staff can search by company name, phone number, or even city and state to locate the required record.
After you verify that the indexes are correct, save the table as Customers.
Order entry is a good example of a type of data that must be normalized. Some order information pertains to the entire order, and other information pertains to individual line items within the order.
To process order entry correctly, you need to create two new tables. The first, the Order table, will store information that is relevant to the entire order. The second, the OrderItems table, will list the individual line items that make up the order. Figure 7.14 shows the relationship between these two tables. As you can see, there is only one entry per order in the Order table. The related OrderItems table, however, contains multiple entries per order, one for each item ordered.
Figure 7.14 Order entry provides a good example of how data should be normalized.
The Order Table. Create the Order table with the information shown in
Table 1.7.
| Field Name | Data Type | Field Properties |
| OrderID | AutoNumber | Select Increment from the drop-down list in the New Values field. |
| CustomerID | Number | Select Long Integer from the drop-down list in the Field Size field. |
| OrderDate | Date/Time | |
| PurchaseOrder | Text | Type 30 in the Field Size field. |
| ShipTo | Text | Type 50 in the Field Size field. |
| ShipCompany | Text | Type 40 in the Field Size field. |
| ShipAddress1 | Text | |
| ShipAddress2 | Text | |
| ShipCity | Text | Type 40 in the Field Size field. |
| ShipState | Text | Type 5 in the Field Size field. |
| ShipZip | Text | Type 10 in the Field Size field. |
| ShipMethodID | Number | Select Long Integer from the drop-down list in the Field Size field. |
| ShippingCharge | Currency | |
| Taxable | Yes/No |
The OrderID column stores the unique order ID. You should designate this column as the primary key.
The CustomerID column stores the CustomerID value from the Customer table, linking an order to the appropriate customer. If a customer has more than one order, then all those orders have the same CustomerID and are linked to the same Customer row.
ShipMethodID is another column that has been normalized. It contains the ID to relate an order with the ShippingMethod table.
ShippingCharge introduces a new data type, the Currency type. As its name implies, this data type is used to store money amounts.
NOTE: Almost all database systems have a currency data type, but they are not all called Currency. Microsoft SQL Server uses the term money for a similar data type.
Taxable is defined as having a Yes/No data type. A column that has a Yes/No data type can accept only two values: Yes and No. This data type is useful for storing flags (a value that indicates if an option is selected or not). Whenever a column can have only one of two responses--Yes or No--then you can use this data type.
NOTE: Microsoft Access uses the term Yes/No to describe a data type that has only two states: Yes (On, True) and No (Off, False). Other database systems use different terms to describe this data type. Microsoft SQL Server calls this a bit data type.
The Order Table's Indexes. The Order table has four indexes that you should create as follows:
Create a primary key on the OrderID column.
Create a Duplicates OK index on the CustomerID column.
Create a Duplicates OK index on the OrderDate column.
Create a Duplicates OK index on the ShipMethodID column.
You index the OrderDate column so orders can be sorted by order date, a sort order that you anticipate will be used often. You index the ShipMethodID column so shipping can easily find all orders that need to be shipped via the same method.
The OrderItems Table. Create the OrderItems table with the
information shown in Table 1.8.
| Field Name | Data Type | Field Properties |
| OrderID | Number | Select Long Integer from the drop-down list in the New Values field. |
| OrderLine | Number | Select Byte from the drop-down list in the New Values field. |
| BookID | Number | Select Long Integer from the drop-down list in the New Values field. |
| Quantity | Number | Select Integer from the drop-down list in the New Values field. |
| UnitPrice | Currency | |
| SalePrice | Currency |
The OrderID column must be a Long Integer because it is the data type of the OrderID in the Order table. The OrderID column in each table is what relates an order to the order items.
OrderLine is the line number within a specific order. You will not have more than 256 line items in each order (your invoices can't even print that many items on a single order), so a byte is adequate.
NOTE: Even though this table does not have a primary key, you still can uniquely identify every row. The combination of OrderID plus OrderLine is guaranteed to be unique, and the index that spans these two columns is a unique index.
With some database systems, you can specify unique constraints. The database follows these rules when rows are inserted or updated. A unique constraint tells the database to reject any rows that are not unique across the entire constraint. If a row that violates this constraint is inserted, the database returns an error and rejects the row.
If your database does not support unique constraints, you have to ensure that your values are unique.
BookID contains the ID of the title ordered and relates back to the Inventory table you created earlier.
The OrderItems Table's Indexes. The OrderItems table has just two indexes
that you should create as follows:
Create a unique index called Order that indexes the OrderID column plus the OrderLine column. To specify that this index is unique (a Duplicates No index), select Yes from the drop-down list box in the Unique field in the Index Properties area, as shown in Figure 7.16.
Create a Duplicates OK index on the BookID column.
Figure 7.16 To create a unique index that spans more than one column, you must use the Indexes window.
The ShippingMethod Table. The Order table stores shipping method IDs
instead of shipping details. These IDs relate the order record to the shipping
method in the ShippingMethod table. Create the ShippingMethod table as detailed
in Table 1.9.
| Field Name | Data Type | Field Properties |
| ID | AutoNumber | Select Increment from the drop-down list in the New Values field. |
| ShippingMethod | Text | Type 20 in the Field Size field. |
Create a primary key on the ID column, and save the table as ShippingMethod.
You have now created nine tables, and almost every one of these tables is related. When an order is placed, for example, the following steps must occur:
If this process sounds rather complex, don't worry about it. As soon as you start building an application that uses these tables, you'll find that it all makes perfect sense. In the meantime, you may find that drawing a flowchart to describe the relationships between tables is worthwhile. The flowchart in Figure 7.17 shows the relationships between some of the tables created in this chapter.
Figure 7.17 Almost all the tables created in this chapter are related together.
In the next chapter, you learn how to create SQL statements to query your tables for data. To do so, you need sample data in your tables. You can use Microsoft Access, and indeed whatever front-end application you use to create your tables, to add that data.
For your immediate needs, though, you need to add data to the Employee tables. You should add two rows to the Departments table and ten rows to the Employee table.
First, add the data to the Departments table. If Microsoft Access is not currently running, load the program. Then open the A2Z database. You are presented with a list of the available tables. Click the Departments table to select it, and then choose Open. The table then opens so you can add and edit data, as shown in Figure 7.18.
Figure 7.18 Microsoft Access opens tables in a grid in which you can add or edit data.
The ID column is an AutoNumber column; you don't enter a value in it because Microsoft Access does that job for you automatically.
In the Description column, type Sales. Sales is the first department to create, and Access assigns ID 1 to it. Now click the empty Description field beneath the Sales field, and type Accounting. Access assign ID 2 to this record.
Now you're going to add rows to the Employee table. First, save the Departments table by clicking the Save button (the one with the picture of the disk). Then, from the File menu, choose Close to close the table.
Next, open the Employee table by selecting it and double-clicking or by clicking the Open button. You are now going to put values in all the columns. The values you need are listed in Table 1.10. Enter the data for all ten rows. Note that you have to scroll the window to get to the DepartmentID and PhoneExtension columns.
| FirstName | LastName | DepartmentID | PhoneExtension |
| Adam | Stevens | 1 | 4878 |
| Adrienne | Green | 4 | 4546 |
| Dan | Johnson | 4 | 4824 |
| Jack | Smith | 1 | 4545 |
| Jane | Smith | 4 | 4876 |
| Jennifer | White | 1 | 4345 |
| Kim | Black | 1 | 4565 |
| Lynn | Wilson | 1 | 4464 |
| Marcy | Gold | 1 | 4912 |
| Steven | Jones | 1 | 4311 |
After you enter the data, click the Save button and close the window. You're now ready to start learning SQL.
){kind=link}
){kind=link}
){kind=link}
){kind=link}