The Columns of a Table

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:

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.

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.

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.

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:

System::Void btnCreateTable_Click(System::Object^ sender, System::EventArgs^ e)
{
	 ADODB::ConnectionClass ^ conADO = gcnew ADODB::ConnectionClass;
	 String ^ strCreate; 
	 Object ^obj = gcnew Object;

	 conADO->Open(L"Provider=Microsoft.Jet.OLEDB.4.0;" +
	          L"Data Source='C:\\Programs\\Exercise.mdb'", L"", L"", 0);
	 strCreate = L"CREATE TABLE Persons(FirstName char, MiddleName String, " +
                     L"LastName varchar);";
	 conADO->Execute(strCreate, obj, 0);
	 conADO->Close();
}

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:

System::Void btnCreateTable_Click(System::Object^  sender, System::EventArgs^  e)
{
	 ADODB::ConnectionClass ^ conADO = gcnew ADODB::ConnectionClass;
	 String ^ strCreate; 
	 Object ^obj = gcnew Object;

	 conADO->Open(L"Provider=Microsoft.Jet.OLEDB.4.0;" +
	          L"Data Source='C:\\Programs\\Exercise.mdb'", L"", L"", 0);
	 strCreate = L"CREATE TABLE Employees(FullName String, JobDescription Text, " +
                     L"Comments Memo, ShortTimeGoals Note, " +
                     L"LongTimeGoals LongText);";
	 conADO->Execute(strCreate, obj, 0);
	 conADO->Close();
}

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:

System::Void btnCreateTable_Click(System::Object^  sender, System::EventArgs^  e)
{
	 ADODB::ConnectionClass ^ conADO = gcnew ADODB::ConnectionClass;
	 String ^ strCreate; 
	 Object ^obj = gcnew Object;

	 conADO->Open(L"Provider=Microsoft.Jet.OLEDB.4.0;" +
	          L"Data Source='C:\\Programs\\Exercise.mdb'", L"", L"", 0);
	 strCreate = L"CREATE TABLE Employees(FullName String, IsFullTime Bit, " +
                     L"CanTeachVariousSubjects Logical, " +
                     L"IsMarried YesNo);";
	 conADO->Execute(strCreate, obj, 0);
	 conADO->Close();
}

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:

System::Void btnCreateTable_Click(System::Object^  sender, System::EventArgs^  e)
{
	 ADODB::ConnectionClass ^ conADO = gcnew ADODB::ConnectionClass;
	 String ^ strCreate; 
	 Object ^obj = gcnew Object;

	 conADO->Open(L"Provider=Microsoft.Jet.OLEDB.4.0;" +
	          L"Data Source='C:\\Programs\\Exercise.mdb'", L"", L"", 0);
 strCreate = L"CREATE TABLE EmploymentStatus(StatusID Byte, Category Integer1);";
	 conADO->Execute(strCreate, obj, 0);
	 conADO->Close();
}

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:

System::Void btnCreateTable_Click(System::Object^  sender, System::EventArgs^  e)
{
	 ADODB::ConnectionClass ^ conADO = gcnew ADODB::ConnectionClass;
	 String ^ strCreate; 
	 Object ^obj = gcnew Object;

	 conADO->Open(L"Provider=Microsoft.Jet.OLEDB.4.0;" +
	          L"Data Source='C:\\Programs\\Exercise.mdb'", L"", L"", 0);
	 strCreate = L"CREATE TABLE Students(FullName varchar, Height Single, " +
	         L"Weight Numeric, GPA Double);";
	 conADO->Execute(strCreate, obj, 0);
	 conADO->Close();
}

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:

System::Void btnCreateTable_Click(System::Object^  sender, System::EventArgs^  e)
{
	 ADODB::ConnectionClass ^ conADO = gcnew ADODB::ConnectionClass;
	 String ^ strCreate; 
	 Object ^obj = gcnew Object;

	 conADO->Open(L"Provider=Microsoft.Jet.OLEDB.4.0;" +
	          L"Data Source='C:\\Programs\\Exercise.mdb'", L"", L"", 0);
	 strCreate = L"CREATE TABLE Customers(" +
		         L"FullName Text, " +
			 L"WeeklyHours Double, " +
			 L"HourlySalary Money, WeeklySalary Currency);";
	 conADO->Execute(strCreate, obj, 0);
	 conADO->Close();
}

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:

System::Void btnCreateTable_Click(System::Object^  sender, System::EventArgs^  e)
{
	 ADODB::ConnectionClass ^ conADO = gcnew ADODB::ConnectionClass;
	 String ^ strCreate; 
	 Object ^obj = gcnew Object;

	 conADO->Open(L"Provider=Microsoft.Jet.OLEDB.4.0;" +
	          L"Data Source='C:\\Programs\\Exercise.mdb'", L"", L"", 0);
	 strCreate = L"CREATE TABLE Teachers(" +
		         L"FullName Text, " +
			 L"DateHired Date, " +
			 L"DateLastReviewed DateTime);";
	 conADO->Execute(strCreate, obj, 0);
	 conADO->Close();
}

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

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