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:

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