Table Creation

Introduction

 

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.

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.

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.

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