Columns Fundamentals

Introduction

We saw that a table was used to organize the values of a list by using categories of information. Here is an example:

With this type of arrangement, each column holds a particular category of information. A table must have at least one column. This means that, to create a table using the CREATE TABLE TableName formula, you must specify a (at least one) column.

To create the columns of a table, on the right side of the name, type an opening and a closing parentheses. 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 the sections with colons. This would be done as follows:

CREATE TABLE TableName(Column1 DataType1 Options1,
		       Column2 DataType2 Options2,
		       Column_n DataType_n Options_n)

In the next sections, we will review the factors of this formula. To create a table in ADO, you can pass the whole statement to the Execute() method of the Connection class.

Characteristics of a Column

 

Introduction

Like a table of a database, a column must have a name. As mentioned for a table, the name of a column is very flexible. Because of 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 if we do, the name of the column will be included between [ and ]

When creating the table, set 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.

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. To specify the data type of a column, pass the name of the data type as the second factor of the column.

Text-Based Columns: 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. You can specify the data type as Char, Text, String or Varchar. Here are examples of three columns created with these types:

Imports ADODB

Module Central

    Sub Main()
        Dim conDatabase As New Connection
        Dim strStatement As String
        Dim objAffected As Object
        Dim strConnection As String

        objAffected = Nothing
        strStatement = "CREATE TABLE Customers(FirstName char, " & _
                                              "MiddleName String, " & _
                                              "LastName varchar);"
        Try
            strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                            "Data Source='C:\Programs\Exercise1.mdb';"
            conDatabase.Open(strConnection, "", "", )
            conDatabase.Execute(strStatement, objAffected, 0)
            Console.WriteLine("A new table named Customers has been created")
        Finally
            conDatabase.Close()
        End Try
    End Sub

End Module

Each one of the char, string, or varchar data types would produce the same effect. A column with the string, the char, or the varchar data type allows any type of value made of any character up to 255 symbols. If you want the column to hold longer text, specify its data type as Memo, NOTE, or LONGTEXT. Such a column can hold any type of text, any combination of characters, and symbols, up to 64000 characters. Here are examples:

Imports ADODB

Module Central

    Sub Main()
        Dim conDatabase As New Connection
        Dim strStatement As String
        Dim objAffected As Object
        Dim strConnection As String

        objAffected = Nothing
        strStatement = "CREATE TABLE Employees(FullName String, " & _
                                              "JobDescription Text, " & _
                                              "Comments Memo, " & _
                                              "ShortTimeGoals Note, " & _
                                              "LongTimeGoals LongText);"
        Try
            strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                            "Data Source='C:\Programs\Exercise1.mdb';"
            conDatabase.Open(strConnection, "", "", )
            conDatabase.Execute(strStatement, objAffected, 0)
            Console.WriteLine("A new table named Employees has been created")
        Finally
            conDatabase.Close()
        End Try
    End Sub

End Module

Boolean Columns: If you want to create a column to hold only values as being true or being false, specify its data type as YESNO, BIT, or LOGICAL. Here are examples:

Imports ADODB

Module Central

    Sub Main()
        Dim conDatabase As New Connection
        Dim strStatement As String
        Dim objAffected As Object
        Dim strConnection As String

        objAffected = Nothing
        strStatement = "CREATE TABLE Contractors(" & _
                                          "FullName String, " & _
                                          "IsFullTime BIT, " & _
                                          "CanTeachVariousSubjects LOGICAL, " & _
                                          "IsMarried YESNO);"
        Try
            strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                            "Data Source='C:\Programs\Exercise1.mdb';"
            conDatabase.Open(strConnection, "", "", )
            conDatabase.Execute(strStatement, objAffected, 0)
            Console.WriteLine("A new table named Contractors has been created")
        Finally
            conDatabase.Close()
        End Try
    End Sub

End Module

Byte and Integer1: If you want a column to hold natural numbers, you can specify its data type as Byte or Integer1. This is suited for a column that will hold small numeric values not to exceed 255. Here are examples:

Imports ADODB

Module Central

    Sub Main()
        Dim conDatabase As New Connection
        Dim strStatement As String
        Dim objAffected As Object
        Dim strConnection As String

        objAffected = Nothing
        strStatement = "CREATE TABLE EmploymentStatus(" & _
                                                "StatusID Byte, " & _
                                                "Category Integer1);"
        Try
            strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                            "Data Source='C:\Programs\Exercise1.mdb';"
            conDatabase.Open(strConnection, "", "", )
            conDatabase.Execute(strStatement, objAffected, 0)
      Console.WriteLine("A new table named EmploymentStatus has been created")
        Finally
            conDatabase.Close()
        End Try
    End Sub

End Module

Short and Integer2: If you want the column to hold larger numbers that can exceed 255, specify its data type as SHORT or INTEGER2. 

Long: If the column will hold small to very large numbers, specify its data type as INT, INTEGER, INTEGER4 or Long.

Floating-Point Value With Single Precision: If you want to create a column that will hold regular decimal values without regards to precision on its value, specify its data type as Single. 

Floating-Point Value With Double Precision: If the values of a column will require a good level of precision, specify its data type as either Double, or Numeric. Here is an example:

Imports ADODB

Module Central

    Sub Main()
        Dim conDatabase As New Connection
        Dim strStatement As String
        Dim objAffected As Object
        Dim strConnection As String

        objAffected = Nothing
        strStatement = "CREATE TABLE Students(" & _
                                            "FullName varchar, " & _
                                            "Height Single, " & _
                                            "Weight Numeric, " & _
                                            "GPA Double);"
        Try
            strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                            "Data Source='C:\Programs\Exercise1.mdb';"
            conDatabase.Open(strConnection, "", "", )
            conDatabase.Execute(strStatement, objAffected, 0)
            Console.WriteLine("A new table named Students has been created")
        Finally
            conDatabase.Close()
        End Try
    End Sub

End Module

Money and Currency Columns: If you want the values of a column to hold monetary values, specify its data type as either Money or Currency. Here is an example:

Imports ADODB

Module Central

    Sub Main()
        Dim conDatabase As New Connection
        Dim strStatement As String
        Dim objAffected As Object
        Dim strConnection As String

        objAffected = Nothing
        strStatement = "CREATE TABLE Customers(" & _
                   			"FullName Text, " & _
                			"WeeklyHours Double, " & _
                			"HourlySalary Money, " & _
               				"WeeklySalary Currency);"
        Try
            strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                            "Data Source='C:\Programs\Exercise1.mdb';"
            conDatabase.Open(strConnection, "", "", )
            conDatabase.Execute(strStatement, objAffected, 0)
            Console.WriteLine("A new table named Customers has been created")
        Finally
            conDatabase.Close()
        End Try
    End Sub

End Module

Both Money and Currency have the same effect.

Date and Time: If you are creating a column whose values would consist of date, time, or both date and time, specify its data type as DATE or DATETIME. Here are examples:

Imports ADODB

Module Central

    Sub Main()
        Dim conDatabase As New Connection
        Dim strStatement As String
        Dim objAffected As Object
        Dim strConnection As String

        objAffected = Nothing
        strStatement = "CREATE TABLE Teachers(" & _
                    "FullName Text, " & _
                    "DateHired Date, " & _
                    "DateLastReviewed DateTime);"
        Try
            strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                            "Data Source='C:\Programs\Exercise1.mdb';"
            conDatabase.Open(strConnection, "", "", )
            conDatabase.Execute(strStatement, objAffected, 0)
            Console.WriteLine("A new table named Teachers has been created")
        Finally
            conDatabase.Close()
        End Try
    End Sub

End Module

Both data types have the same effect in Microsoft Access.

Binary: The binary data type can let a column 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 creating a column, set its data type to either BINARY or VARBINARY.

Image: If you are creating a column that will hold external documents, such as pictures, formatted (from Microsoft Word for example), or spreadsheet, etc, specify its data type to one of the following: IMAGE, OLEOBJECT, LONGBINARY, or GENERAL.

Column maintenance consists of adding a new column or deleting an existing column. Because the columns belong to a table, their maintenance is related to it. To perform this maintenance, you start with the ALTER TABLE expression followed by the name of the table.

After a table with one or more columns has been created, you can add a new column to it. To add a new column, after the ALTER TABLE statement and the name of the table, 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 Employees:

Imports ADODB

Module Central

    Sub Main()
        Dim conDatabase As New Connection
        Dim strStatement As String
        Dim objAffected As Object
        Dim strConnection As String

        objAffected = Nothing
        strStatement = "ALTER TABLE Employees ADD COLUMN CellPhone string;"
        Try
            strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                            "Data Source='C:\Programs\Exercise1.mdb';"
            conDatabase.Open(strConnection, "", "", )
            conDatabase.Execute(strStatement, objAffected, 0)
            Console.WriteLine("The Employees table has been modified")
        Finally
            conDatabase.Close()
        End Try
    End Sub

End Module

It is important to understand that the ADD COLUMN expression creates a new column at the end of the existing column(s). It cannot be used to insert a column in a table.

To delete a column, start with the ALTER TABLE expression followed by the name of the table. After the ALTER TABLE TableName expression, follow it with a DROP COLUMN expression using this formula:

ALTER TABLE TableName DROP COLUMN ColumnName;

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

Imports ADODB

Module Central

    Sub Main()
        Dim conDatabase As New Connection
        Dim strStatement As String
        Dim objAffected As Object
        Dim strConnection As String

        objAffected = Nothing
        strStatement = "ALTER TABLE Employees DROP COLUMN Comments;"
        Try
            strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                            "Data Source='C:\Programs\Exercise1.mdb';"
            conDatabase.Open(strConnection, "", "", )
            conDatabase.Execute(strStatement, objAffected, 0)
            Console.WriteLine("The Comments column has been " & _
    "deleted from the Employees table.")
        Finally
            conDatabase.Close()
        End Try
    End Sub

End Module