Home

The Tables of a Database

 

Table Creation

 

Introduction

We have defined a database as one or more lists. A list in a database is called a table. 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 values of a list.

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 names 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; but if we do, the name of a table will be included between [ and ]

Here is an example:

Imports ADODB

Module Central

    Sub Main()
      
        Dim strStatement As String = "CREATE TABLE Persons..."
        
    End Sub

End Module

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

Imports ADODB

Module Central

    Sub Main()
        Dim conDatabase As New Connection
        Dim strStatement As String = "CREATE TABLE Persons..."
        Dim objAffected As Object
        Dim strConnection As String

        objAffected = Nothing

        Try
            strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                            "Data Source='C:\\Programs\\Exercise1.mdb';"
            conDatabase.Open(strConnection, "", "", )
            conDatabase.Execute(strStatement, objAffected, 0)
        Finally
            conDatabase.Close()
        End Try
    End Sub

End Module

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 represented in the ConnectionClass class by the Tables property. To locate this collection, you can access the Tables property of the CatalogClass 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:

Imports ADODB

Module Central

    Sub Main()
        Dim conDatabase As New Connection
        Dim strStatement As String = "DROP TABLE Persons"
        Dim objAffected As Object
        Dim strConnection As String

        objAffected = Nothing

        Try
            strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                            "Data Source='C:\\Programs\\Exercise1.mdb';"
            conDatabase.Open(strConnection, "", "", )
            conDatabase.Execute(strStatement, objAffected, 0)
        Finally
            conDatabase.Close()
        End Try
    End Sub

End Module
 

Previous Copyright © 2007-2012 FunctionX Next