The Columns of a Table

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 started 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 programmatically create a column, using the Microsoft Access Object Library or DAO, you can call the CreateField() method of the table. 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.

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 in the previous lesson:

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

   ' Open the database
   Set dbExercise = DBEngine.OpenDatabase("C:\Yevol\Exercise.mdb")

   ' Create a new TableDef object.
   Set tblEmployees = dbExercise.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 is:

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:

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

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

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

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

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.

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.

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 dbExercise As DAO.Database
    Dim tblEmployees As DAO.TableDef
    Dim fldEmployeeNumber As DAO.Field
    
    ' Specify the database to use
    Set dbExercise = DBEngine.OpenDatabase("C:\Programs\Exercise1.mdb")

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

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

   ' Add the new table to the database.
   dbExercise.TableDefs.Append tblEmployees
   dbExercise.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.

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

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.

To exercise a good level of control over the values that can be entered or stored in a database, you can configure each column to allow some types of value and/or to exclude some other types. This is done by specifying an appropriate type of data for the column. The type of data of a column follows the same approaches we reviewed about data types on variables but there are differences with the names of data types among the various libraries used.

In Microsoft Access, to specify the data type of a column, you have two alternatives, one is clear but the other is not. If you create a table using the Table Wizard, every column you select and add to the Fields In My New Table has a specific data type. While you are still in the Table Wizard, you cannot know clearly the data type of a column and you cannot change the data type of a column. Only with experience will you know that all columns whose name end with ID are numeric, all column that appear as date-oriented are Date based. The columns that appear as Boolean values use a Boolean data type. All columns named Notes use a Memo data type, etc.

Probably the best place to specify a data type in Microsoft Access is with the Design View of a table. To specify the data type of a column, after entering its name under Field Name, click its corresponding box under Data Type and click the arrow of the combo box to display the list of available types:

If you are programmatically creating a table using either Microsoft Access' own library or if using DAO, pass the data type as the second argument to the CreateField() method.

If you are using SQL to create your table, pass the name of the data type as the second factor of the column.

The various data types available are the following.

If the fields under a column would be used to hold any type of value, including regular text, such a column is treated as string-based. There are various data types you can apply to such a column. The most common data type is Text. This is equivalent to the String data type we reviewed for Microsoft Visual Basic.

If you are programmatically creating your table, using the Microsoft Access Object Library, to apply a string type to a column, you can use DB_Text. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object
    Dim colFullName As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Create a new table named Students
    Set tblStudents = curDatabase.CreateTableDef("Students")
    ' Create a colume named FullName in the Students table
    Set colFullName = tblStudents.CreateField("FullName", DB_Text)
    ' Add the FullName column to the Students table
    tblStudents.Fields.Append colFullName
    ' Add the Students table to the current database
    curDatabase.TableDefs.Append tblStudents
    
    DoCmd.SelectObject acTable, "Students", True
End Sub

If you are using DAO to create your table and you want the column to hold text values, you can apply the dbText type.

If you are creating your table using SQL, for a column whose value would be string-based, you can apply the TEXT, the CHAR, or the VARCHAR data types. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim strSQL As String
    
    strSQL = "CREATE Table Contracts (FirstName Text, LastName Varchar);"
    DoCmd.RunSQL strSQL
End Sub

Each one of the text, char, or varchar data types would produce the same effect. A column with the Text, the char, or the varchar data type allows any type of value made of any character up to 255 symbols. If you want column to hold longer text and if you are creating the table in the Design View, after specifying its name, set its Data Type to Memo. Like the Text data type, the Memo is used for any type of text, any combination of characters, and symbols. The main difference with the Text data type is that a Memo column allows storing longer text up to 64000 characters.

If you are programmatically creating a column, using the Microsoft Access Object Library, if you want it to hold longer text than the Text data type or a regular String would handle, pass its type as DB_MEMO. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object
    Dim colFullName As Object
    Dim colComments As Object

    Set curDatabase = CurrentDb
    Set tblStudents = curDatabase.CreateTableDef("Students")
    
    ' Create a colume named FullName in the Students table
    Set colFullName = tblStudents.CreateField("FullName", DB_Text)
    tblStudents.Fields.Append colFullName
    ' Create the Comments column
    Set colComments = tblStudents.CreateField("Comments", DB_MEMO)
    tblStudents.Fields.Append colComments
    
    ' Add the Students table to the current database
    curDatabase.TableDefs.Append tblStudents
    
    DoCmd.SelectObject acTable, "Students", True
End Sub

If you are creating the table using DAO and want to use long text values on a column, specify its data type as dbMemo. Here is an example:

Private Sub cmdTableCreation_Click()
    Dim curDatabase As DAO.Database
    Dim tblStudents As DAO.TableDef
    Dim colFullName As DAO.Field
    Dim colAnnualReview As DAO.Field

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Create a new table named Students
    Set tblStudents = curDatabase.CreateTableDef("Students")

    Set colFullName = tblStudents.CreateField("FullName", dbText)
    tblStudents.Fields.Append colFullName
    
    Set colAnnualReview = tblStudents.CreateField("AnnualReview", dbMemo)
    tblStudents.Fields.Append colAnnualReview
    
    ' Add the Students table to the current database
    curDatabase.TableDefs.Append tblStudents
    
    DoCmd.SelectObject acTable, "Students", True
End Sub

If you are using SQL to create your table, you can apply the MEMO, the NOTE, or the LONGTEXT data types to a column that would hold long text.

If you want to create a column to hold only values as being true or being false, Yes or No, On or Off, 0 or –1 and if you are creating the table in the Design View, set its data type to Yes/No.

If you are programmatically creating the column on a table, using the Microsoft Access Object Library, and the column would be Boolean-based, set its data type as DB_BOOLEAN. Here is an example:

Private Sub cmdTableCreator_Click()
    Dim curDatabase As Object
    Dim tblEmployees As Object
    Dim colFullName As Object
    Dim colIsMarried As Object

    Set curDatabase = CurrentDb
    Set tblEmployees = curDatabase.CreateTableDef("Employees")
    
    Set colFullName = tblEmployees.CreateField("FullName", DB_TEXT)
    tblEmployees.Fields.Append colFullName
    Set colIsMarried = tblEmployees.CreateField("IsMarried", DB_BOOLEAN)
    tblEmployees.Fields.Append colIsMarried
    
    ' Add the Students table to the current database
    curDatabase.TableDefs.Append tblEmployees
End Sub

If you are creating a table using DAO and you want a column to hold Boolean values, specify its data type as dbBoolean. Here is an example:

Private Sub cmdTableCreation_Click()
    Dim curDatabase As DAO.Database
    Dim tblStudents As DAO.TableDef
    Dim colFullName As DAO.Field
    Dim colWasTransfered As DAO.Field

    Set curDatabase = CurrentDb
    
    Set tblStudents = curDatabase.CreateTableDef("Students")
    
    Set colFullName = tblStudents.CreateField("FullName", dbText)
    tblStudents.Fields.Append colFullName
    
    Set colWasTransfered = tblStudents.CreateField("WasTransfered", dbBoolean)
    tblStudents.Fields.Append colWasTransfered
    
    curDatabase.TableDefs.Append tblStudents
End Sub

If you are using SQL to create the column, set its data type as YESNO, BIT, or LOGICAL. Here are examples:

Private Sub cmdTable_Click()
    DoCmd.RunSQL "CREATE TABLE Contractors(" & _
                 "FullName TEXT, " & _
                 "AvailableOnWeekend BIT, " & _
                 "OwnsACar LOGICAL, " & _
                 "CanShareOwnCar YESNO);"
End Sub

These are equivalent to Microsoft Access' Yes/No and to Microsoft Visual Basic's Boolean data type.

In Lesson 3, we introduced the various types of integral values available in Microsoft Visual Basic. When creating a table in the Design View of Microsoft Access, if you want a column to hold natural numbers, first set its data type to Number. In the lower section of the window, click the arrow of the Field Size property and select from the list:

Byte: The Byte data type is the same as we reviewed with variables in Lesson 3. It is made for small numeric value not more than 255. If you are programmatically creating the table using the Microsoft Access Object Library and you want the column to hold these types of numbers, set its data type to DB_BYTE. Here is an example:

Private Sub cmdTableCreator_Click()
    Dim curDatabase As Object
    Dim tblEmployees As Object
    Dim colFullName As Object
    Dim colDepartmentCode As Object

    Set curDatabase = CurrentDb
    Set tblEmployees = curDatabase.CreateTableDef("Employees")
    
    Set colFullName = tblEmployees.CreateField("FullName", DB_TEXT)
    tblEmployees.Fields.Append colFullName
    Set colDepartmentCode = tblEmployees.CreateField("DepartmentCode", DB_BYTE)
    tblEmployees.Fields.Append colDepartmentCode
    
    curDatabase.TableDefs.Append tblEmployees
End Sub

If you are creating the table using DAO and you want a column that would hold small numeric values, you can specify its data type as dbByte.

If you are using SQL to create the column, set its data type to either Byte or Integer1.

Integer: As mentioned in Lesson 3, if you want to use values higher than the Byte is made for, you can use the Integer type. To apply to a column you are creating the Design View of a table, after setting its Data Type to Number in the top section of the table, in the lower section select Integer as its Field Size.

If you are programmatically the column using the Microsoft Access Object Library and you want this type of numeric value, set its data type to DB_INTEGER. Here is an example:

Private Sub cmdTableCreator_Click()
    Dim curDatabase As Object
    Dim tblEmployees As Object
    Dim colFullName As Object
    Dim colDepartmentCode As Object

    Set curDatabase = CurrentDb
    Set tblEmployees = curDatabase.CreateTableDef("Employees")
    
    Set colFullName = tblEmployees.CreateField("FullName", DB_TEXT)
    tblEmployees.Fields.Append colFullName
    Set colDepartmentCode = tblEmployees.CreateField("DepartmentCode", DB_INTEGER)
    tblEmployees.Fields.Append colDepartmentCode
    
    curDatabase.TableDefs.Append tblEmployees
End Sub

If you are using DAO, specify the data type of the column as dbInteger. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim dbThisOne As DAO.Database
    Dim tblEmployees As DAO.TableDef
    Dim fldEmployeeNumber As DAO.Field
    Dim fldFullName As DAO.Field
    
    ' Specify the database to use
    Set dbThisOne = CurrentDb

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

    Set fldEmployeeNumber = tblEmployees.CreateField("EmployeeNumber", dbInteger)
    tblEmployees.Fields.Append fldEmployeeNumber
    
    Set fldFullName = tblEmployees.CreateField("FullName", dbText)
    tblEmployees.Fields.Append fldFullName

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

If you are creating the column using SQL and you want to use an integer, set its data type to one of the following: SHORT or INTEGER2. Here is an example:

Private Sub cmdTable_Click()
    DoCmd.RunSQL "CREATE TABLE Countries(DiplCode Short, AreaCode Integer2);"
End Sub

Long: Besides the Byte and the integer, another natural number supported in the libraries is called Long or Long Integer. This is used for significantly high numbers. If you are creating a table in the Design View and you want to apply this data type, select it in the Field Size.

If you are programmatically creating the column using the Microsoft Access Object Library, you can set its data type to DB_LONG. Here is an example:

Private Sub cmdTableCreator_Click()
    Dim curDatabase As Object
    Dim tblEmployees As Object
    Dim colFullName As Object
    Dim colDepartmentCode As Object

    Set curDatabase = CurrentDb
    Set tblEmployees = curDatabase.CreateTableDef("Employees")
    
    Set colFullName = tblEmployees.CreateField("FullName", DB_TEXT)
    tblEmployees.Fields.Append colFullName
    Set colDepartmentCode = tblEmployees.CreateField("DepartmentCode", DB_LONG)
    tblEmployees.Fields.Append colDepartmentCode
    
    curDatabase.TableDefs.Append tblEmployees
End Sub

If you are creating your column in DAO, set its data type to dbLong. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim dbThisOne As DAO.Database
    Dim tblEmployees As DAO.TableDef
    Dim fldEmployeeNumber As DAO.Field
    Dim fldFullName As DAO.Field
    Dim fldWeeklyHours As DAO.Field
    
    ' Specify the database to use
    Set dbThisOne = CurrentDb

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

    Set fldEmployeeNumber = tblEmployees.CreateField("EmployeeNumber", dbLong)
    tblEmployees.Fields.Append fldEmployeeNumber
    
    Set fldFullName = tblEmployees.CreateField("FullName", dbText)
    tblEmployees.Fields.Append fldFullName

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

If you are creating the column using SQL, set its data type to one of the following: INT, INTEGER, INTEGER4 or Long. Here is an example:

Private Sub cmdTable_Click()
    DoCmd.RunSQL "CREATE TABLE Countries(Area long, Population integer);"
End Sub

Some of these data types would be applied only to Microsoft Access and may not be used in other SQL environments.

Besides natural numbers, a database also ought to support decimal values. We introduced them in Lesson 3 as values that hold a fraction. Because these are primarily considered as numbers, before applying them to a column, if you are creating a table in the Design View, first set its data type to Number and, in the lower section of the window, click the arrow of the Field Size property.

Floating-Point Value With Single Precision: If you want a regular decimal value without regards to precision on the column, set its data type to Single. If you are programmatically creating the column, specify its data type as DB_SINGLE. If you are using DAO, specify the data type of the column as dbSingle. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim dbThisOne As DAO.Database
    Dim tblEmployees As DAO.TableDef
    Dim fldEmployeeNumber As DAO.Field
    Dim fldFullName As DAO.Field
    Dim fldWeeklyHours As DAO.Field
    
    ' Specify the database to use
    Set dbThisOne = CurrentDb

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

    Set fldEmployeeNumber = tblEmployees.CreateField("EmployeeNumber", dbLong)
    tblEmployees.Fields.Append fldEmployeeNumber
    
    Set fldFullName = tblEmployees.CreateField("FullName", dbText)
    tblEmployees.Fields.Append fldFullName
    
    Set fldWeeklyHours = tblEmployees.CreateField("WeeklyHours", dbSingle)
    tblEmployees.Fields.Append fldWeeklyHours

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

If you are using SQL, specify the column's data type as Single.

Floating-Point Value With Double Precision: If the values of a column will require a good level of precision and if you are creating the table in Design View, specify the Field Size of the column as Double after setting its Data Type to Number.

If you are programmatically creating the column using the Microsoft Access Object Library, set its data type to DB_DOUBLE. Here is an example:

Private Sub cmdTableCreator_Click()
    Dim curDatabase As Object
    Dim tblEmployees As Object
    Dim colFullName As Object
    Dim colWeeklyHours As Object

    Set curDatabase = CurrentDb
    Set tblEmployees = curDatabase.CreateTableDef("Employees")
    
    Set colFullName = tblEmployees.CreateField("FullName", DB_TEXT)
    tblEmployees.Fields.Append colFullName
    Set colWeeklyHours = tblEmployees.CreateField("WeeklyHours", DB_DOUBLE)
    tblEmployees.Fields.Append colWeeklyHours
    
    curDatabase.TableDefs.Append tblEmployees
End Sub

If you are using DAO to create your table, set the column's data type to dbDouble.

If you are creating the table in SQL, specify the data type of the column as either NUMERIC or DOUBLE to support double-precision values.

When creating a table in the Design View, if you want a column to hold monetary values, set its Data Type to Currency. After setting this data type, when a person is using the application, the database would refer to the language set in the Control Panel and the rules in the Currency property page:

For example, if the database is being used in the US, the US dollar and its rules, including the $ symbol, would be applied. If you want to apply different rules to a particular column, after setting its Data Type to Currency, in the lower section of the window, click the arrow of the Field Size property and select the desired option:

If you are programmatically creating the column and you want it to hold monetary values, set its data type to DB_CURRENCY. Here is an example:

Private Sub cmdTableCreator_Click()
    Dim curDatabase As Object
    Dim tblEmployees As Object
    Dim colFullName As Object
    Dim colWeeklyHours As Object
    Dim colHourlySalary As Object

    Set curDatabase = CurrentDb
    Set tblEmployees = curDatabase.CreateTableDef("Employees")
    
    Set colFullName = tblEmployees.CreateField("FullName", DB_TEXT)
    tblEmployees.Fields.Append colFullName
    Set colWeeklyHours = tblEmployees.CreateField("WeeklyHours", DB_DOUBLE)
    tblEmployees.Fields.Append colWeeklyHours
    Set colHourlySalary = tblEmployees.CreateField("HourlySalary", DB_CURRENCY)
    tblEmployees.Fields.Append colHourlySalary
    
    curDatabase.TableDefs.Append tblEmployees
End Sub

If you are using DAO, specify its data type as dbCurrency.

If you are using SQL, specify its data type as Money or Currency. Here is an example:

Private Sub cmdTable_Click()
    DoCmd.RunSQL "CREATE TABLE Employees(" & _
                 "FullName Text, " & _
                 "WeeklyHours Double, " & _
                 "HourlySalary Money);"
End Sub

Both Money and Currency have the same effect in Microsoft Access.

If you are creating a table in the Design View and you want a column to hold date, time, or both date and time values, set its Data Type to Date/Time. Using the Microsoft Access Object Library, if you are programmatically creating a column that would hold date/time values, set its data type to DB_DATE. Here is an example:

Private Sub cmdTableCreator_Click()
    Dim curDatabase As Object
    Dim tblEmployees As Object
    Dim colFullName As Object
    Dim colDateHired As Object

    Set curDatabase = CurrentDb
    Set tblEmployees = curDatabase.CreateTableDef("Employees")
    
    Set colFullName = tblEmployees.CreateField("FullName", DB_TEXT)
    tblEmployees.Fields.Append colFullName
    Set colDateHired = tblEmployees.CreateField("DateHired", DB_DATE)
    tblEmployees.Fields.Append colDateHired
    
    curDatabase.TableDefs.Append tblEmployees
End Sub

If you are creating the column using DAO, set its data type to dbDate.

If you are creating a SQL statement to create the table, set its column's data type to either the DATE or the DATETIME types. Here are examples:

Private Sub cmdTable_Click()
    DoCmd.RunSQL "CREATE TABLE Employees(" & _
                 "FullName Text, " & _
                 "DateHired Date, " & _
                 "DateLastReviewed DateTime);"
End Sub

Both data types have the same effect in Microsoft Access.

The binary data type can let a field accept any type of data but it is equipped to interpret the value. For example, it can be used to receive hexadecimal numbers. To specify this when programmatically creating a column, using the Microsoft Access Object Library, specify its data type as DB_BINARY. Here is an example:

Private Sub cmdTableCreator_Click()
    Dim curDatabase As Object
    Dim tblBooks As Object
    Dim colShelf As Object

    Set curDatabase = CurrentDb
    Set tblBooks = curDatabase.CreateTableDef("Books")
    
    Set colShelf = tblBooks.CreateField("Shelf", DB_BINARY)
    tblBooks.Fields.Append colShelf
    
    curDatabase.TableDefs.Append tblBooks
End Sub

If you are creating the table in DAO, set the column's data type as dbBinary.

If you are programmatically creating the column in ADO, set its data type as either BINARY or VARBINARY.

If you are creating a column that will hold external documents, such as pictures, formatted (from Microsoft Word for example), or spreadsheet, etc, and if you are working in the table's Design View, set the column's data type to OLE Object.

If you are programmatically creating the table using SQL and if you are creating a column that would hold embedded objects, set its data type to one of the following: IMAGE, OLEOBJECT, LONGBINARY, or GENERAL.

A hyperlink is a type of string that allows the user to move to a different file when clicked. Use this data type if you want to allow the user to open documents stored in other applications.

1. Under the Field Name, click DateIssued and press Tab
2. Click the arrow of the combo box under Data Type and select Date/Time
3. Press the down arrow and click the arrow of the combo box
4. In the list, select Number for the Weight column
5. Save and close the table

Column maintenance consists of renaming, copying, moving, or deleting a column. This operation is performed differently, of course, depending on the means you are using. You can work visually in the Table Wizard, in the Datasheet View, or in the Design View of a table.

If you are programmatically maintaining a column, because a column must belong to a table, before performing any operation on it, you must first obtain a reference to the table. We saw how to do this for a Microsoft Access Object Library or for DAO. Here is an example for DAO:

Private Sub cmdModifyPersons_Click()
    Dim curDatabase As DAO.Database
    Dim tblPersons As DAO.TableDef

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Get a reference to a table named Persons
    Set tblPersons = curDatabase.TableDefs("Persons")
End Sub

If you are working in SQL, to perform maintenance on a column, you can start with an ALTER TABLE expression as follows:

ALTER TABLE TableName ...

The TableName factor must specify the table on which the maintenance will be performed. After the table name, you can then issue the desired command.

If you are creating a new table using the Table Wizard or if you are inserting a column using the Field Builder dialog box, the column already has a name. In the Table Wizard, after selecting a column and adding it to the Fields In My New Table list box, to change the name of the column, first select it. Then, click the Rename Field button. This would display the Rename Field dialog box. You can type the desired name and click OK. If you decide to change the name of a column, it may be a good idea to be familiar with the data type of the column. For example, if you have a column whose name ends with ID but change that name with a string that doesn't end with ID, the column would keep its Long Integer data type but its role may become confusing.

If you are working in the Datasheet View of a table, to change the name of a column, you can double-click the column header. You can also right-click the column header and click Rename Column. Another alternative is to click any cell under a column, then, on the main menu, click Format -> Rename Column. Any of these actions would put the column name into edit mode. You can type the desired name.

If you are working in the Design View of a table, to rename a column, in the top section of the window, you can double-click the name of a column to put it into edit mode, type the desired name and press Enter or Tab.

If you are working in a form or report, any control bound to a column already has a name. In most cases, we will keep those names. If you add an unbound control that would require an expression as a placeholder, we may use a different naming convention to name it. We may imitate the famous Hungarian naming convention but that nobody has been able to keep up with. For example, we may start the name of a text box with txt and the name of a button with btn. In some other cases, we may not. For this reason, we will not list the convention we will use because we cannot guarantee consistency. Still, to change the name of a control on a form or a report, first display it in Design View. Click the control on the form or report. In the Properties window, click Name and type the desired name.

If you add an unbound control to a Data Access Page, it automatically receive a name as an HTML ID attribute. If you don't like that ID, first display the page in Design View and click the control. Then, in the Properties window, click the id field and type the desired ID.

1. In the Tables section of the Database window, right-click the Employees table and click Design View
2. Double-click StateOrProvince to select it and type State
3. In the lower section of the view, click Caption and press Delete to remove the caption
4. Change PostalCode to ZIPCode
5. In the lower section of the view, click Caption and type ZIP Code
6. Save the table

If you want to create a column that has many similarities with another column, you can just make a copy of the desired column. You can do this either in the Table Wizard or in the Design View of a table. To copy a column in the Table Wizard, simply select it twice from the Sample Fields. Since two columns cannot have the same name, the copied column would be given a cumulative name. In most cases, you can simply rename the copied column if you want it to have a different name.

To copy a column in the Design View of the table, in the top section of the window, right-click the desired column and click Copy. The right-click another column or an empty cell under the Field Name and click Paste. Before closing the table or changing its view, you will need to change the name of the columns so you would not have two columns with the same name.

If you are working in a form or a report and want to copy a control, first right-click that control and click Copy. Then right-click the form or report and click Paste.

If a column is misplaced in a table, you can change its location. To change the position of a column in the Datasheet View, first click the column header to highlight it. Click it again but hold your mouse down. Then move it left or right in the desired direction. A vertical guiding line would display to indicate the new position. When you reach the desired position, release the mouse.

To move a column in the Design View of a table, first click the gray button on its left to select it. Click it again but hold the mouse down. Then move it up or down. A horizontal guiding line would come up. When the line displays at the desired position, release it.

If you have a column you don't need anymore on a table, you can remove that column. To remove a column in the Datasheet View of a table, right-click the column header and click Delete Column. To remove a column in the Design View of a table, right-click any section of the undesired field and click Delete Rows. To programmatically delete a column, if you are using either the Microsoft Access Object Library or DAO, call the Delete() method of the TableDef object and pass it the name of the column. The syntax of this method is: TableDef.Fields.Delete ColumnName In this formula, replace ColumnName with the name of the column you want to delete. Here is an example:

Private Sub cmdModifyPersons_Click()
    Dim curDatabase As DAO.Database
    Dim tblPersons As DAO.TableDef

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Get a reference to a table named Customers
    Set tblPersons = curDatabase.TableDefs("Persons")
    
    tblPersons.Fields.Delete "DateHired"
End Sub

Before deleting a column, make sure it exists, otherwise, you would receive a 3265 error:

Even if the column exists, before deleting a column, make sure its table is closed. Otherwise, you would receive a 3211 error:

You can check these issues using error handling. Here is an example:

Private Sub cmdModifyPersons_Click()
On Error GoTo cmdModifyPersons_Error

    Dim curDatabase As DAO.Database
    Dim tblPersons As DAO.TableDef

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Get a reference to a table named Customers
    Set tblPersons = curDatabase.TableDefs("Persons")
    
    tblPersons.Fields.Delete "FullName"
    
    Exit Sub
cmdModifyPersons_Error:
    If Err.Number = 3265 Then
        MsgBox "The column you are trying to delete doesn't exist on the table"
    ElseIf Err.Number = 3211 Then
        MsgBox "Before deleting the column, please close the table first " & _
               "and make sure nobody is using it"
    End If
    
    Resume Next
End Sub

If you are using SQL, to delete a column, after the ALTER TABLE TableName expression, follow it with a DROP COLUMN expression as in this formula:

ALTER TABLE TableName DROP COLUMN ColumnName;

Replace the name of the undesired column with the ColumnName factor of our formula. Here is an example:

Private Sub cmdAlterPersons_Click()
    DoCmd.RunSQL "ALTER TABLE Persons DROP COLUMN FullName"
End Sub

1. To remove a column, right-click SpouseName and click Delete Rows
2. Save and close the table

While using a table, you may find out that it lacks a certain column. You can add such a new column between two existing columns or at the end of the other columns.

If you are working in the Datasheet View of a table, to insert a column, right-click the column that would succeed it and click Insert Column. A new column with a default name would be inserted. You can then rename it as you see fit.

The Design View, once again, provides the best way to insert a new column because it gives you more control. To insert a new column between two existing ones, right-click the column that would succeed it and click Insert Rows. A new column without a name would be created. You can then click the empty field, type a name, and specify its data type if it is not suited for Text. To add a new column at the end of the others, an operation that is not inherently possible in the Datasheet View, simply click the first empty field under the Field Name column, type its name and specify its data type.

1. In the Tables section of the Database window, right-click Customers and click Design View
2. Right-click PhoneNumber and click Insert Rows
3. Click the new empty field and type CustomerName
4. Press F6
5. In the lower section of the table, click Caption and type Customer Name
6. Save and close the table

After a table with one or more columns has been created, you can add a new column to it. In the strict sense, you cannot add a new column to a table in the Datasheet view. In the Design View, to add a new column, click the first empty cell under Field Name, type the desired name of the column, then set its data type and its properties.

To programmatically add a new column, whether using the Microsoft Access Object Library or DAO, first declare an Object (Microsoft Access Object Library) or a Field (DAO) variable. After getting a reference to the table that will receive the new column, assign the CreateField() method of the table to the column's variable. Finally, call the Append() method of the Fields collection of the table and pass it the column variable. Here is an example:

Private Sub cmdAddColumn_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object
    Dim colFullName As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Get a reference to a table named Customers
    Set tblStudents = curDatabase.TableDefs("Students")
    
    Set colFullName = tblCustomers.CreateField("FullName", DB_TEXT)
    tblCustomers.Fields.Append colFullName
End Sub

If you are using SQL, to add a new column, in an ALTER TABLE statement, include an ADD COLUMN expression using the following formula:

ALTER TABLE TableName
ADD COLUMN ColumnName DataType

The ColumnName factor must be a valid name for the new column and you must follow the rules for naming columns. The data type must be one of those we reviewed. Here is an example that adds a new string-based column named CellPhone to a table named Contractors:

Private Sub cmdDeleteColumn_Click()
    DoCmd.RunSQL "ALTER TABLE Contractors ADD COLUMN CellPhone TEXT;"
End Sub

In the database environments, a column is called a field. In the various libraries used in Microsoft Access, a column is an object of type Field. The columns of a table are stored in a collection called Fields. To give access to this collection, in the Microsoft Access Object Library or in DAO, 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.

After creating the columns of a table, you may want to know the number of columns that a table has. To give you this information, the Fields collection is equipped with a property named Count.

To identify each column of a table, the Fields collection is equipped with a property named Item. This type of property is also referred to as indexed because it takes an argument that identifies the particular member that you want to access in the collection.

To access a column, you can pass its name or its index to the Item() indexed property. If you know the name of the column, you can pass it as a string. Here is an example:

Fields.Item("[Last Name]")

Item is the default property of a Fields collection. Therefore, you can omit it. Based on this, we can also write:

Fields("[Last Name]")

If you don't know the name of a column or you prefer to access it by its index, you can pass that index to the Item property. Remember that the index starts at 0, followed by 1, and so on. Based on this, to access the third column of a table, you would use either of these two:

Fields.Item(2)
Fields(2)