Home

Columns Management
 

Introduction

After creating a table, you may not like its structure. Instead of deleting it to recreate it, you can simply modify it. To make any modification on a table, you can start with the ALTER TABLE expression followed by the name of the table.
 

Column Addition

One of the operations you can perform to manage a table consists of adding a new column to a table. This is done with the ADD operator followed by the name of the new column and some options, if any.

Practical Learning: Adding a New Column to a Table
  1. This lesson continues from the previous lesson
    Add a new command button to the form
  2. Change its Caption to Modify Table and its Name to cmdModifyTable
  3. Double-click the new button and implement its Click event as follows:
      
    Private Sub cmdModifyTable_Click()
    Dim conModify As ADODB.Connection
    Dim strSQL As String
    
    Set conModify = New ADODB.Connection
    conModify.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                   "Data Source='C:\ADO Lessons\MVA1\MVA.mdb'"
    
    strSQL = "ALTER TABLE Employees Add EmployeeReviews Memo;"
    
    conModify.Execute strSQL
    
    MsgBox "A new column named EmployeeReviews has been added to " & _
           "the Employees table of the MVA.mdb database has been deleted"
    
    Set conModify = Nothing
End Sub
  4. Test the application and the button
     
  5. Return to your programming environment

Column Deletion

As opposed to creating a new column, if you have a column that has become useless on a table, you can get rid of it. To delete a column, use the DROP COLUMN expression followed by the name of the column. Before deleting a column, make sure it exists on the table.

Here is an example:

Private Sub cmdAlterTable_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:\Programs\MSVB\ADO2\MVA1.mdb'"
    
    strSQL = "ALTER TABLE Employees DROP COLUMN Notes;"
    
    conEmployees.Execute strSQL
    
    MsgBox "The Notes column of the Employees table has been removed"
    
    Set conEmployees = Nothing
End Sub

Practical Learning: Deleting a Column
  1. Change the code of the Modify Table button as follows:
      
    Private Sub cmdModifyTable_Click()
    Dim conModify As ADODB.Connection
    Dim strSQL As String
    
    Set conModify = New ADODB.Connection
    conModify.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                   "Data Source='C:\ADO Lessons\MVA1\MVA.mdb'"
    
    strSQL = "ALTER TABLE Employees DROP COLUMN Dept;"
    
    conModify.Execute strSQL
    
    MsgBox "The Dept column of the Employees table has been removed"
    
    Set conModify = Nothing
End Sub
  2. Test the application and the button
     
  3. Return to your programming environment
  4. To add a new column to the table, change the code of the Modify button as follows:
      
    Private Sub cmdModifyTable_Click()
    Dim conModify As ADODB.Connection
    Dim strSQL As String
    
    Set conModify = New ADODB.Connection
    conModify.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                   "Data Source='C:\ADO Lessons\MVA1\MVA.mdb'"
    
    strSQL = "ALTER TABLE Employees ADD Department Double;"
    
    conModify.Execute strSQL
    
    MsgBox "A Department column that can handle decimal values " & vbCrLf & _
           "has been added to the Employees table"
    
    Set conModify = Nothing
End Sub
  5. Execute the application and the the new statement
     
  6. Return to your programming environment

Column Modification

If a certain aspect of a column is wrong while its name fine, you can modify the column without deleting and recreating it. To modify an aspect of the column, use the ALTER COLUMN, followed by the name of the column, and the modification to perform. For example, to change the data type and optionally the length of data, on the right side of the column name, enter the data type and the option.

Practical Learning: Modifying a Column
  1. Change the code of the Modify Table button as follows:
      
    Private Sub cmdModifyTable_Click()
    Dim conModify As ADODB.Connection
    Dim strSQL As String
    
    Set conModify = New ADODB.Connection
    conModify.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                   "Data Source='C:\ADO Lessons\MVA1\MVA.mdb'"
    
    strSQL = "ALTER TABLE Employees ALTER COLUMN Department VarChar(40);"
    
    conModify.Execute strSQL
    
    MsgBox "The data type of the Employees column of the Employees table" & _
           vbCrLf & _
           "has been modified to handle text"
    
    Set conModify = Nothing
End Sub
  2. Test the application and the button
     
    MVA - Modifying a column
  3. Return to your programming environment
 
 
Previous Copyright © 2001-2005 FunctionX, Inc. Next