The Tables of a Database

Table Creation

 

Introduction

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:

Last Name	First Name	Main Subject	Alternate Subject
Pastore	Albert	Math	Physics
Andong	Gertrude	Chemistry	Static
Missiano	Helena	Physical Ed
Jones	Celestine	Comp Sciences	Math

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. 

Table Creation

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.

The Name of a 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:

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 strCreate As String



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

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

        strCreate = "CREATE TABLE Students . . .;"



     conADO.Close

        

    End Sub

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:

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 strCreate As String



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

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

        strCreate = "CREATE TABLE Students . . .;"

        conADO.Execute(strCreate, 0, 0)

     conADO.Close

    End Sub

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.

Table Maintenance

 

The Tables Collection

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.

Deleting a Table

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:

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

                ByVal e As System.EventArgs) Handles btnDeleteTable.Click

        Dim conSchoolRecords As New ADODB.Connection

        

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

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

        conSchoolRecords.Execute("DROP TABLE Teachers;")



        MsgBox("The Teachers table of the SchoolRecords database has been deleted.")

     conADO.Close

End Sub