ADO With VB Express - Lesson 6: The Columns of a Table

	The Columns of a Table

Columns Fundamentals

Introduction

In the previous lesson, we saw that a table was used to organize the values of a list by using categories of information. Here is an example:

Last Name	First Name	Main Class	Years of Experience
Pastore	Albert	Math	4
Andong	Gertrude	Chemistry	8
Missiano	Helena	Physical Ed	5
Jones	Celestine	Comp Sciences	10

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 we introduced in the previous lesson, you must specify a column.

Column Creation

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(ColumnName DataType Options, ColumnName DataType Options)

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

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.

The Types of Columns

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, String or Varchar. Here are examples of three columns created with these types:

Private Sub btnCreateTable_Click(ByVal sender As System.Object, _

                ByVal e As System.EventArgs) Handles btnCreateTable.Click

        Dim conADO As ADODB.ConnectionClass = New ADODB.ConnectionClass

        Dim strSQL As String



        conADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _

               "Data Source='C:\Programs\SchoolRecords.mdb'", Nothing, Nothing, 0)

        strSQL = "CREATE TABLE Students(FirstName char, MiddleName String, " & _

                                       "LastName varchar);"

        conADO.Execute(strSQL, 0, 0)

    	conADO.Close

End Sub

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 Text, 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:

Private Sub btnCreateTable_Click(ByVal sender As System.Object, _

                ByVal e As System.EventArgs) Handles btnCreateTable.Click

        Dim conADO As ADODB.ConnectionClass = New ADODB.ConnectionClass

        Dim strSQL As String



        conADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _

               "Data Source='C:\Programs\SchoolRecords.mdb'", Nothing, Nothing, 0)

        strSQL = "CREATE TABLE Employees(FullName String, JobDescription Text, " & _

                                         "Comments Memo, ShortTimeGoals Note, " & _

                                         "LongTimeGoals LongText);"

        conADO.Execute(strSQL, 0, 0)

    	conADO.Close

End Sub

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:

Private Sub btnCreateTable_Click(ByVal sender As System.Object, _

                ByVal e As System.EventArgs) Handles btnCreateTable.Click

        Dim conADO As ADODB.ConnectionClass = New ADODB.ConnectionClass

        Dim strSQL As String



        conADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _

               "Data Source='C:\Programs\SchoolRecords.mdb'", Nothing, Nothing, 0)

        strSQL = "CREATE TABLE Employees(FullName String, IsFullTime Bit, " & _

                                         "CanTeachVariousSubjects Logical, " & _

                                         "IsMarried YesNo);"

        conADO.Execute(strSQL, 0, 0)

    	conADO.Close

End Sub

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:

Private Sub btnCreateTable_Click(ByVal sender As System.Object, _

                ByVal e As System.EventArgs) Handles btnCreateTable.Click

        Dim conADO As ADODB.ConnectionClass = New ADODB.ConnectionClass

        Dim strSQL As String



        conADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _

               "Data Source='C:\Programs\SchoolRecords.mdb'", Nothing, Nothing, 0)

        strSQL = "CREATE TABLE EmploymentStatus(StatusID Byte, Category Integer1);"

        conADO.Execute(strSQL, 0, 0)

        MessageBox.Show("A table named EmploymentStatus has been added to the " & _

                        "SchoolRecords.mdb database")

    	conADO.Close

End Sub

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 Double. Alternatively, you can specify the data type as Numeric. Here is an example:

Private Sub btnCreateTable_Click(ByVal sender As System.Object, _

                ByVal e As System.EventArgs) Handles btnCreateTable.Click

        Dim conADO As ADODB.ConnectionClass = New ADODB.ConnectionClass

        Dim strSQL As String



        conADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _

               "Data Source='C:\Programs\SchoolRecords.mdb'", Nothing, Nothing, 0)

        strSQL = "CREATE TABLE Students(FullName varchar, Height Single, " & _

                 "Weight Numeric, GPA Double);"

        conADO.Execute(strSQL, 0, 0)

    	conADO.Close

End Sub

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

Private Sub btnCreateTable_Click(ByVal sender As System.Object, _

                ByVal e As System.EventArgs) Handles btnCreateTable.Click

        Dim conADO As ADODB.ConnectionClass = New ADODB.ConnectionClass

        Dim strSQL As String



        conADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _

               "Data Source='C:\Programs\SchoolRecords.mdb'", Nothing, Nothing, 0)

        strSQL = "CREATE TABLE Employees(" & _

                 "FullName Text, " & _

                 "WeeklyHours Double, " & _

                 "HourlySalary Money, WeeklySalary Currency);"

        conADO.Execute(strSQL, 0, 0)

    	conADO.Close

End Sub

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:

Private Sub btnCreateTable_Click(ByVal sender As System.Object, _

                ByVal e As System.EventArgs) Handles btnCreateTable.Click

        Dim conADO As ADODB.ConnectionClass = New ADODB.ConnectionClass

        Dim strSQL As String



        conADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _

               "Data Source='C:\Programs\SchoolRecords.mdb'", Nothing, Nothing, 0)

        strSQL = "CREATE TABLE Employees(" & _

                 "FullName Text, " & _

                 "DateHired Date, " & _

                 "DateLastReviewed DateTime);"

        conADO.Execute(strSQL, 0, 0)

    	conADO.Close

End Sub

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, specify its data type as 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

Introduction

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.

Adding a New Column

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:

Private Sub btnAddColumn_Click(ByVal sender As System.Object, _

			ByVal e As System.EventArgs) Handles btnAddColumn.Click

        Dim conSchoolRecords As New ADODB.Connection



        conSchoolRecords.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _

                    "Data Source='C:\Programs\SchoolRecords.mdb'")

   conSchoolRecords.Execute("ALTER TABLE Employees ADD COLUMN CellPhone string;")



   MsgBox("A new column named CellPhone has been added to the Employees table.")

    	conADO.Close

End Sub

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.

Deleting a Column

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 name of the undesired column with the ColumnName factor of our formula. Here is an example:

Private Sub btnDeleteColumn_Click(ByVal sender As System.Object, _

		ByVal e As System.EventArgs) Handles btnDeleteColumn.Click

        Dim conSchoolRecords As New ADODB.Connection



        conSchoolRecords.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _

                    "Data Source='C:\Programs\SchoolRecords.mdb'")

        conSchoolRecords.Execute("ALTER TABLE Employees DROP COLUMN Comments;")

        MsgBox("The Comments column has been deleted from the Employees table.")
    	conADO.Close
End Sub


Previous	Copyright © 2005-2016, FunctionX, Inc.	Next