Home

Tables Fundamentals
 

Overview of Tables
 

Introduction

A database is primarily a list of items, such as names of family members: Judie, Ernest, Bill, David, Hermine. Such a list can be created as follows:

Family Members
 
Name
Judie
Ernest
Bill
David
Hermine

To make the list more elaborate, each entry can contain other internal information, such as the age, the gender, the type of relationship, etc. Here is an example:

Family Members
 
Name Age Gender Relationship
Judie 18 Female Sister
Ernest 24 Male Cousin
Bill 52 Unknown Uncle
David 36 Male Brother
Hermine 12 Unknown Niece

This arrangement makes it easy to visualize the items in the list and to locate data. Information is organized by categories. The categories are vertically positioned. Each category is also called a column. Information is also organized horizontally in each line called a row. The information that belongs to the same row is also called a record.

Practical Learning: Introducing Tables

  1. If you want to follow the lessons on this site, Start Microsoft Visual Basic 6 or the programming environment you will use for these lessons
     
  2. On the opening dialog box, click Standard EXE and click Open
  3. Save the project in a new folder inside of your ADO Lessons folder and name it MVA1
  4. Save the file of the form as Main and save the project as MVA
  5. Add a new command button to the form. Change its Caption to Create Database and its Name to cmdCreateDatabase
  6. Use the same approach of our previous lesson to create a new database named MVA and store it in the above Tables folder (don't forget to add a reference to ADOX using the References dialog box):
      
    Option Explicit

Private Sub cmdCreateDatabase_Click()
    Dim conCatalog As ADOX.Catalog
    
    Set conCatalog = New ADOX.Catalog
    conCatalog.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                      "Data Source='C:\ADO Lessons\MVA1\MVA.mdb'"
    
    MsgBox "A new Microsoft JET database named MVA.mdb has been created"
    
    Set conCatalog = Nothing
End Sub
  7. Execute the application and execute the statement
  8. To allow the user to create other databases, change the design of the form as follows (the name of the text box displays in it):
     
  9. Change the code of the Click event of the button as follows:
      
    Private Sub cmdCreateDatabase_Click()
    Dim conCatalog As ADOX.Catalog
    Dim strDbName As String
    
    Set conCatalog = New ADOX.Catalog
    strDbName = "C:\ADO Lessons\" & Me.txtDatabaseName.Text & ".mdb"
    conCatalog.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                      "Data Source=" & strDbName
    
MsgBox "A new Microsoft JET database named " & strDbName & " has been created"
    
    Set conCatalog = Nothing
End Sub
  10. Save All
 

Table Creation

To create a table, you start a statement with the CREATE TABLE expression followed by the desired name of the table as follows: 

CREATE TABLE TableName

When creating a table, you must provide a name for it. The name of a table can be made of digits, letters, underscores, or a combination of those. In our lessons, we will use the following rules:

  • The name will start with a letter in uppercase. Examples are Employees, Members
  • After the first letter, the name can have letters, digits, underscores or a combination of those. Examples are Title_1, Category_A_2
  • If the name is a combination of words, each part will start with an uppercase letter. Examples are FullNames, StatesAndCapitals
  • We will use one word names; that is, no space in the name

Every table must have at least one column. The list of columns of a table starts with an opening parenthesis “(“ and ends with a closing parenthesis “)”. If the table will be made of more than one column, you can separate them with a comma. The syntax used would be: 

CREATE TABLE TableName(Column1, Column2, Column_n);

To create a column, you specify its name, followed by its data type, and some possible options. Therefore, the syntax of creating a column is: 

ColumnName DataType Options

Notice that there is only space that separates the sections of the syntax. For this reason, the name of a column should be in one word because, if you put space after the first word, the SQL engine would treat the next word as a data type. For example, the following statement would produce an error: 

CREATE TABLE Test(Marital Status)

If you want to use space in a column name, include it between an opening square bracket “[“ and a closing square bracket “]”. The above statement would be changed to: 

CREATE TABLE Test([Marital Status])

After (or on the right side of) the column name, you must specify the type of information, also called the data type) that will be stored in the cells under that column. Here are the data types you can use:

Data Type Description
Char or Text The char or the text data types can be used for columns whose fields would receive (or present) text. The field must not contain more than 255 characters
Varchar This is one of the most regularly used data types of a SQL column. It can used for strings (text) of any kind
Memo The cell of a Memo type can hold text of up to 65656 characters
Bit This is used for a Boolean or logical field that accept only a True or False, a Yes or No, an On or Off, or a 0 or –1 as a value
Smallint The smallint data type can be used for a field that would hold numbers that can range from –32,768 to 32767
Int or Integer Each of these data types can be used to represent a natural number 
Long This is used for fields that would hold small to very large natural numbers
Real Real is a relatively small data type in the world of double-precision representation but can be used on a column whose fields would hold numbers that can range from – 3.402823E38 to – 1.401298E-45 for negative values or from 1.401298E-45 to 3.402823E38 for positive values
Float The float data type can be used on a column whose fields would hold numbers with a decimal portion. Like the Single data type, the float is mainly on a column where number precision is not a significant issue
Numeric The numeric data type can be used on a column whose fields would hold numbers with a decimal portion
Double The Double data type is the preferred data type for cells that would hold decimal numbers
Money and Currency This data type is appropriate for fields that would hold numbers that represent monetary values
DateTime The DATETIME data type can be applied to a column whose fields would display either date, time or both date and time values

 
Here is an example that creates a table and names it Persons 

CREATE TABLE Persons
(
FirstName VARCHAR,
[Last Name] varchar,
Gender int,
[Date Of Birth] DateTime,
MaritalStatus Integer,
IsMarried Bit,
PersonalIncome Money,
HouseholdIncome Currency
);

A field size can be used to specify the size of value used on a field. Based on this, when creating a column whose data type is char, text or varchar, you can optionally specify the desired number of characters that the field should allow. To specify the maximum number of characters of a text-based field, include it in parentheses just to the left of the data type.

In ADO, to create a table, you include its whole statement between double-quotes, as a string. To actually perform the assignment, you can call the Execute() method of the ADO reference you are using, passing your string as argument.

 

Practical Learning: Creating a Table
  1. Add a new command button to the form
  2. Change its Caption to Create Table and name it cmdCreateTable
  3. Generate its Click event and implement it as follows:
      
    Private Sub cmdCreateTable_Click()
    Dim conEmployees As ADODB.Connection
    Dim strSQL As String
    
    Set conEmployees = New ADODB.Connection
    conEmployees.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                      "Data Source='C:\ADO Lessons\MVA1\MVA.mdb'"
    
    strSQL = "CREATE TABLE Employees (" & _
             "DateHired DateTime," & _
             "EmplNumber Text(6)," & _
             "Dept int," & _
             "FirstName VarChar(20)," & _
             "LastName varchar(20)," & _
             "Address varchar(50)," & _
             "City VARCHAR(40)," & _
             "State VarChar(32));"
    
    conEmployees.Execute strSQL
    
    MsgBox "A table named Employees has been added to the MVA.mdb database"
    
    Set conEmployees = Nothing
End Sub
  4. To add a reference to ADO, on the main menu, click Project -> References...
  5. In the Available References checked list box of the References dialog box, click the check box of your latest ADO driver:
     
    References
  6. Click OK
  7. Test the application and execute the new statement by clicking the second button
     
    Motor Vehicle Administration
  8. Return to your programming environment
  9. To create another table, change the code of the Create Table button as follows:
      
    Private Sub cmdCreateTable_Click()
    Dim conTable As ADODB.Connection
    Dim strSQL As String
    
    Set conTable = New ADODB.Connection
    conTable.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                  "Data Source='C:\ADO Lessons\MVA1\MVA.mdb'"
    
    strSQL = "CREATE TABLE Departments(DeptName VarChar(40), Description varchar(255));"
    
    conTable.Execute strSQL
    
    MsgBox "A table named Departments has been added to the MVA.mdb database"
     
    Set conTable = Nothing
End Sub
  10. Test the application and execute the statement of the button
  11. Return to your programming environment
 

Table Deletion

If you have a table in a database but don't need that table anymore, you can get rid of it. To delete a table, write the DROP TABLE expression followed by the name of the table.
 

Practical Learning: Deleting a Table
  1. Add a new command button to the form
  2. Change its Caption to Delete Table and its Name to cmdDeleteTable
  3. Double-click the Delete Table button and implement its Click event as follows:
      
    Private Sub cmdDeleteTable_Click()
    Dim conDepartments As ADODB.Connection
    Dim strSQL As String
    
    Set conDepartments = New ADODB.Connection
    conDepartments.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source='C:\ADO Lessons\MVA1\MVA.mdb'"
    
    strSQL = "DROP TABLE Departments;"
    
    conDepartments.Execute strSQL
    
    MsgBox "The Departments table of the MVA.mdb database has been deleted"
    
    Set conDepartments = Nothing
End Sub
  4. Execute the application and click the Delete Table button
     
  5. Return to your programming environment
 
Previous Copyright © 2001-2005 FunctionX, Inc. Next