The Tables of a Database

In the first lesson, we defined a database as one or more lists. A list in a database is called a table. The idea is that a table is an arrangement of the categories of information stored in a list and a table makes it easy to locate and manage the records of a list. To better explore lists, you should know how a table organizes its value.

A table is made of one or more categories divided as columns. Consider the following example of a list of teachers of a high school:

Notice that the first named are grouped in a common category, so are the last names and so on. This makes it easy to locate a category and possibly a value.

To create a table, you start an expression with CREATE TABLE followed by the name of the table:

CREATE TABLE Name;

The CREATE and TABLE keywords must be used to create a table. The Name factor specifies the name of the new table.

After the CREATE TABLE expression, you must enter a name for the table. The name of a table can be very flexible. This flexibility can be overwhelming and confusing. To avoid these, there are suggestions and conventions we will apply when naming our tables:

• The name of a table will start with a letter. In most cases, the name will start in uppercase
• Because we believe that a table represents a list of items, its name will be in plural. Examples are Students, Employees, Products
• When a name is a combination of words, each part will start in uppercase. Examples are Student Names or Sport Activities
• In most cases, we will avoid including space in a name.

Here is an example:

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

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

After formulating the expression that creates the table, you can pass it to the Execute() method of a Connection variable. This would be done as follows:

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 . . .";
	 conADO->Execute(strCreate, obj, 0);
	 conADO->Close();
}

Besides the CREATE TABLE expression followed by a name, there are other issues related to creating a table. We will review more details in future lessons.

The tables of an ADO database are stored in a collection. To locate this collection, you can access the Tables property of the Catalog class of the ADOX namespace.

To remove a table from a database, create a DROP TABLE expression followed by the name of the table. The formula to use is:

DROP TABLE TableName;

Replace the TableName factor of our formula with the name of the table you want to delete. Here is an example:

System::Void btnDeleteTable_Click(System::Object^  sender, System::EventArgs^  e)
{
	 ADODB::Connection ^ conADO = gcnew ADODB::Connection;
	 Object ^obj = gcnew Object;

	 conADO->Open(L"Provider=Microsoft.Jet.OLEDB.4.0;" +
                    L"Data Source='C:\\Programs\\Exercise.mdb'", L"", L"", 0);
	 conADO->Execute(L"DROP TABLE Persons;", obj, 0);
 MessageBox::Show(L"The Persons table of the Exercise database has been deleted.");
	 conADO->Close();
}