The Columns of a Table: Column Maintenance

Introduction

Column maintenance consists of renaming, copying, moving, or deleting a column. This operation is performed differently, of course, depending on the means you are using. You can work visually in the Table Wizard, in the Datasheet View, or in the Design View of a table.

If you are programmatically maintaining a column, because a column must belong to a table, before performing any operation on it, you must first obtain a reference to the table. We saw how to do this for a Microsoft Access Object Library or for DAO. Here is an example for DAO:

Private Sub cmdModifyPersons_Click()
    Dim curDatabase As DAO.Database
    Dim tblPersons As DAO.TableDef

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Get a reference to a table named Persons
    Set tblPersons = curDatabase.TableDefs("Persons")
End Sub

If you are working in SQL, to perform maintenance on a column, you can start with an ALTER TABLE expression as follows:

ALTER TABLE TableName ...

The TableName factor must specify the table on which the maintenance will be performed. After the table name, you can then issue the desired command.

If you are creating a new table using the Table Wizard or if you are inserting a column using the Field Builder dialog box, the column already has a name. In the Table Wizard, after selecting a column and adding it to the Fields In My New Table list box, to change the name of the column, first select it. Then, click the Rename Field button. This would display the Rename Field dialog box. You can type the desired name and click OK. If you decide to change the name of a column, it may be a good idea to be familiar with the data type of the column. For example, if you have a column whose name ends with ID but change that name with a string that doesn't end with ID, the column would keep its Long Integer data type but its role may become confusing.

If you are working in the Datasheet View of a table, to change the name of a column, you can double-click the column header. You can also right-click the column header and click Rename Column. Another alternative is to click any cell under a column, then, on the main menu, click Format -> Rename Column. Any of these actions would put the column name into edit mode. You can type the desired name.

If you are working in the Design View of a table, to rename a column, in the top section of the window, you can double-click the name of a column to put it into edit mode, type the desired name and press Enter or Tab.

If you are working in a form or report, any control bound to a column already has a name. In most cases, we will keep those names. If you add an unbound control that would require an expression as a placeholder, we may use a different naming convention to name it. We may imitate the famous Hungarian naming convention but that nobody has been able to keep up with. For example, we may start the name of a text box with txt and the name of a button with btn. In some other cases, we may not. For this reason, we will not list the convention we will use because we cannot guarantee consistency. Still, to change the name of a control on a form or a report, first display it in Design View. Click the control on the form or report. In the Properties window, click Name and type the desired name.

If you add an unbound control to a Data Access Page, it automatically receive a name as an HTML ID attribute. If you don't like that ID, first display the page in Design View and click the control. Then, in the Properties window, click the id field and type the desired ID.

1. In the Tables section of the Database window, right-click the Employees table and click Design View
2. Double-click StateOrProvince to select it and type State
3. In the lower section of the view, click Caption and press Delete to remove the caption
4. Change PostalCode to ZIPCode
5. In the lower section of the view, click Caption and type ZIP Code
6. Save the table

If you want to create a column that has many similarities with another column, you can just make a copy of the desired column. You can do this either in the Table Wizard or in the Design View of a table. To copy a column in the Table Wizard, simply select it twice from the Sample Fields. Since two columns cannot have the same name, the copied column would be given a cumulative name. In most cases, you can simply rename the copied column if you want it to have a different name.

To copy a column in the Design View of the table, in the top section of the window, right-click the desired column and click Copy. The right-click another column or an empty cell under the Field Name and click Paste. Before closing the table or changing its view, you will need to change the name of the columns so you would not have two columns with the same name.

If you are working in a form or a report and want to copy a control, first right-click that control and click Copy. Then right-click the form or report and click Paste.

If a column is misplaced in a table, you can change its location. To change the position of a column in the Datasheet View, first click the column header to highlight it. Click it again but hold your mouse down. Then move it left or right in the desired direction. A vertical guiding line would display to indicate the new position. When you reach the desired position, release the mouse.

To move a column in the Design View of a table, first click the gray button on its left to select it. Click it again but hold the mouse down. Then move it up or down. A horizontal guiding line would come up. When the line displays at the desired position, release it.

If you have a column you don't need anymore on a table, you can remove that column. To remove a column in the Datasheet View of a table, right-click the column header and click Delete Column.

To remove a column in the Design View of a table, right-click any section of the undesired field and click Delete Rows.

To programmatically delete a column, if you are using either Microsoft Access Object Library or DAO, call the Delete() method of the TableDef object and pass it the name of the column. The syntax of this method is:

TableDef.Fields.Delete ColumnName

In this formula, replace ColumnName with the name of the column you want to delete. Here is an example:

Private Sub cmdModifyPersons_Click()
    Dim curDatabase As DAO.Database
    Dim tblPersons As DAO.TableDef

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Get a reference to a table named Customers
    Set tblPersons = curDatabase.TableDefs("Persons")
    
    tblPersons.Fields.Delete "DateHired"
End Sub

Before deleting a column, make sure it exists, otherwise, you would receive a 3265 error:

Even if the column exists, before deleting a column, make sure its table is closed. Otherwise, you would receive a 3211 error:

You can check these issues using error handling. Here is an example:

Private Sub cmdModifyPersons_Click()
On Error GoTo cmdModifyPersons_Error

    Dim curDatabase As DAO.Database
    Dim tblPersons As DAO.TableDef

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Get a reference to a table named Customers
    Set tblPersons = curDatabase.TableDefs("Persons")
    
    tblPersons.Fields.Delete "FullName"
    
    Exit Sub
cmdModifyPersons_Error:
    If Err.Number = 3265 Then
        MsgBox "The column you are trying to delete doesn't exist on the table"
    ElseIf Err.Number = 3211 Then
        MsgBox "Before deleting the column, please close the table first " & _
               "and make sure nobody is using it"
    End If
    
    Resume Next
End Sub

If you are using SQL, to delete a column, after the ALTER TABLE TableName expression, follow it with a DROP COLUMN expression as in this formula:

ALTER TABLE TableName DROP COLUMN ColumnName;

Replace the name of the undesired column with the ColumnName factor of our formula. Here is an example:

Private Sub cmdAlterPersons_Click()
    DoCmd.RunSQL "ALTER TABLE Persons DROP COLUMN FullName"
End Sub

1. To remove a column, right-click SpouseName and click Delete Rows
2. Save and close the table

While using a table, you may find out that it lacks a certain column. You can add such a new column between two existing columns or at the end of the other columns.

If you are working in the Datasheet View of a table, to insert a column, right-click the column that would succeed it and click Insert Column. A new column with a default name would be inserted. You can then rename it as you see fit.

The Design View, once again, provides the best way to insert a new column because it gives you more control. To insert a new column between two existing ones, right-click the column that would succeed it and click Insert Rows. A new column without a name would be created. You can then click the empty field, type a name, and specify its data type if it is not suited for Text. To add a new column at the end of the others, an operation that is not inherently possible in the Datasheet View, simply click the first empty field under the Field Name column, type its name and specify its data type.

1. In the Tables section of the Database window, right-click Customers and click Design View
2. Right-click PhoneNumber and click Insert Rows
3. Click the new empty field and type CustomerName
4. Press F6
5. In the lower section of the table, click Caption and type Customer Name
6. Save and close the table

After a table with one or more columns has been created, you can add a new column to it. In the strict sense, you cannot add a new column to a table in the Datasheet view. In the Design View, to add a new column, click the first empty cell under Field Name, type the desired name of the column, then set its data type and its properties.

To programmatically add a new column, whether using the Microsoft Access Object Library or DAO, first declare an Object (Microsoft Access Object Library) or a Field (DAO) variable. After getting a reference to the table that will receive the new column, assign the CreateField() method of the table to the column's variable. Finally, call the Append() method of the Fields collection of the table and pass it the column variable. Here is an example:

Private Sub cmdAddColumn_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object
    Dim colFullName As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Get a reference to a table named Customers
    Set tblStudents = curDatabase.TableDefs("Students")
    
    Set colFullName = tblCustomers.CreateField("FullName", DB_TEXT)
    tblCustomers.Fields.Append colFullName
End Sub

If you are using SQL, to add a new column, in an ALTER TABLE statement, include an ADD COLUMN expression using the following formula:

ALTER TABLE TableName
ADD COLUMN ColumnName DataType

The ColumnName factor must be a valid name for the new column and you must follow the rules for naming columns. The data type must be one of those we reviewed. Here is an example that adds a new string-based column named CellPhone to a table named Contractors:

Private Sub cmdDeleteColumn_Click()
    DoCmd.RunSQL "ALTER TABLE Contractors ADD COLUMN CellPhone TEXT;"
End Sub