Table Maintenance

Introduction

Table maintenance consists of renaming, copying, or deleting a table. Once again, Microsoft Access supports all of the necessary operations. Before performing a maintenance operation on a table, you should make sure that the action is necessary and possible. If the table is opened, you cannot perform any operation on it. If the table is involved in an expression, when attempting a maintenance operation on it, sometimes you will be warned and sometimes you will be prevented from performing the operation.

The tables of a Microsoft Access database are stored in a collection called AllTables. Each table of this collection can be identified by its name or its index. When working on a database, the total number of its tables is stored as the Count property of the AllTables collection.

To identify a table in the AllTables collection, you can access it using its name if you know it. To help with this, each table of the collection has a property called Name that represents the name of the table. Here is an example that shows the name of each table of the current database: Private Sub cmdTables_Click() Dim obj As AccessObject, dbs As Object Set dbs = Application.CurrentData ' Check each object of the AllTables collection For Each obj In dbs.AllTables ' When you find a table, display its name MsgBox obj.Name Next obj End Sub

When using the Name property of a table, you are supposed to know. In some cases, you may not know the name of a table. An alternative is to access a table by its rank in the collection. To support this, the AllTables collection is equipped with an Item() property, which is an array. In the parentheses, you can enter the index of the desired table. The first table has an index of 0, the second has an index of 1, and so on. Here is an example that would display then name of the third table of the current database:

MsgBox Application.CurrentData.AllTables(2).Name

In most cases, before performing an operation on a table, you will need to indicate what table you are referring to. This is usually easy to the user who can visually see the table. As for you as the database developer, you can first programmatically get a pointer to the table you intend to work on.

If a table exists already, to get a reference to it, first declare an Object variable. Then, assign the TableDefs property of the current database to the variable. TableDefs is a collection of the tables of the current database. To specify what table you are referring to, you can pass its name, as a string to the TableDefs indexed property. Here is an example:

Private Sub cmdGetReference_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object

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

Instead of using its name, you can also pass the numeric index of the table to the TableDefs property.

To get a reference to a table in DAO, you essentially use the same approach as above. Here is an example that gets a reference to the first table of the current database:

Private Sub cmdContrators_Click()
    Dim curDatabase As DAO.Database
    Dim tblContractors As DAO.TableDef

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Get a reference to a table named Contractors
    Set tblContractors = curDatabase.TableDefs(0)
End Sub

Renaming a Table

Renaming a table consists of changing its name from the original or previous name. As introduced above, you cannot rename a table if it is opened: you would receive an error.

To rename a table in the Database window, in the Tables section, you can right-click the table and click Rename. The name of the table would become highlighted, indicating that it is in edit mode. You can then type the new name and press Enter.

To programmatically rename a table, you can use the DoCmd object that provides the Rename() method. The syntax to use would be:

DoCmd.Rename(NewName, acTable, OldName)

Here is an example:

When this code executes, an existing table named StaffMembers would be renamed as Employees.

When you rename a table, you get the same table with a new name. This preserves the structure and contents of the table. An alternative to this approach consists of making a copy of an existing table. With this technique, you would get two tables that share the same structure and have the same contents, with different names. This can be useful if you want to experiment with the structure or contents of an existing table without risking to compromise it. Microsoft Access supports this in two ways.

To make a copy of an existing table using the Microsoft Windows Save As routine, in the Tables section of the Database window, you can right-click the table and click Save As... This would open the Save As dialog box:

If you are just trying to experiment with the table, you can accept the name. Otherwise, to give a different name to the new table, type the desired string in the Save Table To text box and click OK. If the name exists already, you would receive an error:

Besides the Save As technique, you can also use the Copy And Paste routine of Microsoft Windows. To do this, in the Tables section of the Database window, right-click an existing table, click Copy, then right-click an empty area of the Tables section of the Database window and click Paste. This would open the Paste Table As dialog box:

If you want to change your mind, you can click Cancel. Otherwise, you can type the desired string in the Table Name text box and click OK.

To programmatically make a copy of a table, you can use the DoCmd object that is equipped with the CopyObject() method. The syntax to use is:

DoCmd.CopyObject [destinationdatabase][, newname], acTable, sourceobjectname]

The first argument to this method is the name or path of the database where the copied object would be transferred to. If you are making a copy of the table in the same database that is opened, you can omit this argument.

The second argument is the name that you want the new table to have. It is the same string you would provide to the above Save As or the Paste Table As dialog boxes.

The third argument must be acTable because in this case you are copying a table.

The last argument is the current name of the existing table.

Here is an example:

From this example, a table named StaffMembers will be copied to generate a new table named Teachers, to the current database.

Deleting a Table

If you happen to have a table you don't need anymore in your database, you can remove it. Once again, don't remove a table if you have any doubt. It is better to have a useless table whose role is not clear than to delete a table in doubt. The reason is that, if you delete a table by mistake and then later on find out that you need it, you would have to recreate it completely. If you remove a table that is involved in an expression or a relationship, the expression or the relationship would be broken and this would result in unpredictable results. Fortunately, if you start deleting a table without using code, you would be warned. If the table is involved in a relationship, Microsoft Access would warn and may even prevent you from deleting it.

To visually remove a table, in the Tables section of the Database window, you can right-click the table and click Delete. As stated already, before the operation is carried out, you would be warned:

After reading the message, if you want to change your mind, you can click No. If you still want to delete the table, you can click Yes.

To programmatically delete a table, you can use the DoCmd object that is equipped with the DeleteObject() method. The syntax to use is:

DoCmd.DeleteObject acTable, [objectname]

The acTable argument indicates that you want to delete a table. If you select a table in the Database window when this method is called, you can omit the second argument and the selected table would be deleted. Otherwise, to specify the table you want to delete, pass its name as the second argument of the method.

Here is an example:

When this code executes, Microsoft Access would look for a table named Members. If it finds it, it would remove it from the database.

To delete a table using either the Microsoft Access Object Library or DAO, pass the name of the undesired table to the Detele() method of the TableDefs property of the database. Here is an example from the Microsoft Access Object Library:

Private Sub cmdDeleteTable_Click()
    Dim curDatabase As Object

    Set curDatabase = CurrentDb

    curDatabase.TableDefs.Delete "Books"
End Sub

If you are using SQL, to delete a table, 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:

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