The Columns of a Table

Columns Fundamentals

Introduction

Earlier, we introduced a database as an application made of one or more lists. We also mentioned that, to make a list easy to view and explore, its items should be organized in categories. Here is the example we used:

In a list like this one, each category of information is called a column. In reality, the idea of a column is based on the fact that the categories are organized vertically. This, of course, makes it possible to know that under a column, the information is of the same type. When it comes to types, a database can also be configured so that each column is made for a particular type of data and some types of values would be excluded. Fortunately, Microsoft Access provides all the tools you need to create, configure, and maintain a column with the maximum flexibility.

The columns of a table are used to organize data and they are appropriate for table design. When the users start performing data entry, as we will see in the next lesson, some of them may not find tables user-friendly. An alternative is to create forms, reports, or Data Access Pages that would produce the same results as if working on a table.

As you may guess, a column must be part of a table. In fact, a table without a column is non-existent and a column must belong to a table. There are various techniques you can use to create a column, depending on how you started the table. The easiest technique you can use to create a column is through the Table Wizard. It allows you to select columns that have already been named and configured.

The Design View of a table allows you to provide as much detail as possible about a column you are creating. The Datasheet View is primarily made for data entry but it still allows you to create columns, although it gives you as little control as possible.

After creating the columns of a table, you can either generate a corresponding form, report, or Data Access Page from a table, or you can design from scratch.

1. Open the GCS1 database you created in the previous lesson
2. In the Tables section of the Database window, double-click Create Table By Using Wizard
3. In the first page of the wizard, click Employees in the Sample Tables list
4. In the Sample Fields, double-click EmployeeID, DateHired, EmployeeNumber, FirstName, MiddleName, LastName, Address, City, StateOrProvince, PostalCode, WorkPhone, Extension, Salary, SpouseName, and Notes
5. Click Finish
6. Close the table

As mentioned already, you must specify at least one column in order to create a table. To create a column, you can call the CreateField() method of the table from Microsoft Access' own library. This method takes three arguments and its syntax is:

Set field = object.CreateField(name, type, size)

The first argument, name, must be the name of the new column. The second argument must specify the data type that would be applied on the column. We will review data types next. The third argument holds a piece of information that has to do with either the computer memory or the number of characters.

After creating a column, you must add it to the table. To support this, the table object is equipped with Fields property, which is a collection. Like all collections, the Fields collection of the table is equipped with an Append() method that takes as argument an object that represents the collection.

In DAO, a column is called a field and it is an object of type Field. The columns of a table are stored in a collection called Fields. To give access to this collection, the TableDef object is equipped with a property named Fields that is of type Fields and each one of its items is an object of type Field. To use a field, you can first declare a variable of type DAO.Field. Here are examples:

Private Sub cmdCreateTable_Click()
   
    Dim fldEmployeeNumber As DAO.Field
    Dim fldFirstName As DAO.Field
    Dim fldLastName As DAO.Field
    
End Sub

Before creating the column(s), first initiate a table as we saw above:

Private Sub cmdCreateTable_Click()
   Dim dbDeja As DAO.Database
   Dim tblEmployees As DAO.TableDef

   ' Open the database
   Set dbDeja = DBEngine.OpenDatabase("C:\FunctionX\Deja.mdb")

   ' Create a new TableDef object.
   Set tblEmployees = dbDeja.CreateTableDef("Employees")

   . . .

End Sub

After initiating the table, you can add the desired columns to it. To create a column, you can call the CreateField() method of the TableDef object and assign it to the column variable. The formula to follow would be:

Set fldEmployeeNumber = tblEmployees.CreateField(ColumnName, DataType, FieldSize)

After creating the column, you can add it to the table. To do this, you can pass it to the Append() method of the Fields collection of the TableDef object. This would be done as follows:

Column Creation With SQL

To create a column, you specify its name, followed by its data type, and some possible options. In the parentheses of the CREATE TABLE TableName() expression, the formula of creating a column is:

ColumnName DataType Options

Notice that there is only space that separates the sections of the formula. This formula is for creating one column. If you want the table to have more than one column, follow this formula as many times as possible but separate them with colons. This would be done as follows:

CREATE TABLE TableName(ColumnName DataType Options, ColumnName DataType Options)

In the next sections, we will review the factors of this formula.

Column Creation With ADO

Once again, remember that ADO uses SQL statements to perform its database operations. Based on this, and as seen earlier, to create a table in ADO, formulate a SQL statement and pass it to the Execute() method of the Connection object.

The Name of a Column

Introduction

Like every object of the computer or of a database, the primary attribute of a column or a placeholder is its name. As mentioned for a table, Microsoft Access is very flexible with the names. On a table:

• The name of a column can start with a letter, a digit, an underscore, or even a special character. For example, you can have a column named #22
• The name of a column can contain one or more spaces

Names of columns such as @90T or Kw_3%3 can be confusing. Also, you are more likely to involve the names of columns in various expressions. Such expressions may not work with bizarre names. Based on this, we will adopt the same types of naming conventions we reviewed for tables:

• The name of a column will start with a letter. In most cases, the name will start in uppercase
• When a name is a combination of words, each part will start in uppercase. Examples are First Name or Date Hired
• In most cases, we will avoid including space in a name but remember that Microsoft Access allows it. All you have to do is to surround the name with square brackets when using it in an expression

If you are creating a table using the Table Wizard, each column you select would already have a name and you can see it in the Fields In My New Table list box. If you are working in the Datasheet View of a table, by default, each column of a brand new table has a name and they are Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8, Field9, and Field10. These names are only temporary. If you are working in the Design View of a table, to set the name of a column, click a new cell under the Field Name column, type the desired name and press Enter or Tab:

If you are programmatically creating a table using Microsoft Access' library, as seen in the syntax of the CreateField() method, you must pass the name of the column as the first argument.

If you are using DAO to create your table, pass the name of the column as the first argument to the CreateField() method of the TableDef object. This would be done as follows:

Private Sub cmdCreateTable_Click()
    Dim dbDeja As DAO.Database
    Dim tblEmployees As DAO.TableDef
    Dim fldEmployeeNumber As DAO.Field
    
    ' Specify the database to use
    Set dbDeja = DBEngine.OpenDatabase("C:\Programs\Exercise1.mdb")

    ' Create a new TableDef object.
    Set tblEmployees = dbDeja.CreateTableDef("Employees")

    Set fldEmployeeNumber = tblEmployees.CreateField("EmployeeNumber", ..., ...)
    tblEmployees.Fields.Append fldEmployeeNumber

   ' Add the new table to the database.
   dbDeja.TableDefs.Append tblEmployees
   dbDeja.Close
End Sub

If you are using SQL to create your table, pass the name of the column in the ColumnName placeholder of our formula. Here is an example:

CREATE TABLE Students(FullName, DataType Options)

Notice that the name of the column is not included in quotes.

Practical Learning: Naming Columns

1. To start a new table, in the Tables section of the Database window, double-click Create Table in Design View
2. As the caret is positioned under Field Name, type DateIssued and press the down arrow key
3. Type Weight
4. Right-click the first empty box under Field Name and click Build...
5. In the Sample Fields list, click Address and click OK
6. To save the table, on the Table Design toolbar, click the Save button
7. Set the Table Name to IdentityCards and click OK
8. After reading the message about the absence of a primary key, click No

The Caption of a Column

When showing a table in the Datasheet View, each column displays a string in its top section to indicate what it is used for. This string is referred to the column's caption. When creating a column in the Design View of a table, if you specify only the column name and switch it to Datasheet view, the column would use its name to display the caption. This means that the caption may appear in one word.

If you want to display a friendlier caption, in the Design View of the table, after specifying a column's name, in the lower section of the window, enter the desired string in the Caption field. There is no significant relationship between the column's name and its caption. You can type anything you want in the caption but you should give it a string that resembles the name of the column. For example, if a column is named FirstName, is caption should be First Name.