The Tables of a Database

A Table as a List

Introduction

Imagine that you want to create a computer database for a company. After planning, you may want to start creating the list of employees. You could start with the list of names that employees call each other at work. Here is an example:

Julie, Suzie, John, Lucie

This information, however valuable would not provide much detail about each employee, such as the last name, which is used for employment verification, the salary used for payroll. To make the list more useful, you may need to provide more details about each employee, etc. Here is an example of how you may decide to change the list:

This is the essence of a database. A computer database is an application made of one or more lists that store information that can eventually be explored at will. The list should be easy to use and as intuitive as possible, providing as much information as possible but without any unneeded detail.

A list of items in a database is primarily referred to as a table. This means that the information that a database contains is stored in one or more tables. To make that information easy to use or locate, it is organized in sections called columns. If you observe the above table, you may find it difficult to know what type of information each column represents. To improve it, each column has a header that can display a label that indicates what the column represents. Based on this, the above table should display as follows:

This implies that, to get a database, you should start by creating one table. Later on, you can add as many tables as you judge necessary.

As a database application, Microsoft Access provides all the tools you need to create the necessary tables of your database. In future lessons, we will learn that Microsoft Visual Basic, through various libraries, provides other means of creating a table.

To create a table, you can use the main menu, the Database window, or the New Object button on the toolbar. To create a table using the main menu, you can click Insert -> Table.

If you want to use the Database window, of course you should display it first. If another object is on the screen and the Database Window is in the background, you can press F11 to display it. To programmatically display the Database window, you can call the SelectObject() method of the DoCmd object. Its syntax is:

DoCmd.SelectObject(ObjectType, ObjectName, InDatabaseWindow)

The first argument of this method can be A_TABLE, the second argument can be the name of an object to select. Because this argument is optional, in this case you can omit it. The third argument is a Boolean value that allows you to specify if you want to display the Database window. Based on the scenario we are describing, this argument should be passed as True. Here is an example:

Private Sub cmdDatabaseWindow_Click()
    DoCmd.SelectObject A_TABLE, , True
End Sub

When this code executes, it will display the Database window.

When in the Database window, if a section other than Tables is displaying, to select the Tables section, click Tables. The Database window is equipped with a toolbar under its title bar. From this toolbar, you can click the New button.

As another option to start creating a new table, on the Database toolbar, you can click the arrow of the New Object button and click Table. Any of these three actions would display the New Table dialog box. To programmatically display the New Table dialog box, you can execute the following code:

Private Sub cmdDatabaseWindow_Click()
    DoCmd.DoMenuItem 1, A_FILE, 3, 0, A_MENU_VER20
End Sub

From the New Table dialog box, you can click Datasheet View and click OK. As an alternative, from the Tables section of the Database window, you can double-click Create Table By Entering Data. This also would display a spreadsheet-like window. The Datasheet View appears like a spreadsheet:

You can use it to start entering values or you can change some of its aspects.

From the New Table dialog box, you can also click Design View and click OK. As an alternative, from the Tables section of the Database window, you can double-click Create Table In Design View. This also would display the Design View of a new table:

This view is the most significant, the most detailed, and the best window to create a table as it provides many options.

On the New Table dialog box, you can click Table Wizard and click OK. As an alternative, from the Tables section of the Database window, you can double-click Create Table By Using Wizard. Any of these two actions would start the Table Wizard:

The Table Wizard provides the fastest means of creating a table.

To start a table, you must create an object called a column. In the next lesson, we will learn the various techniques of creating a column.

While or after creating a table, to make it part of your database, you must save it. In fact, if you try closing a table after starting to create it, you would be asked whether you want to save it or not. Also, some other operations we will see in the next lessons require that you save the table even if you have not completed it.

To save a table while still working on it, you can click the Save button on the Table Design or the Table Datasheet toolbar. This would display the Save As dialog box.

Microsoft Access is very flexible with names of tables. The name of a table:

• Can start with a letter, a digit, an underscore, or almost any special character of your choice
• Can contain any combination of letters, digits, underscores, or special characters. For example, tables can be named #wr, 14, or ^dwe
• Can contain space. Examples are first 12 or $@ p&&d%

This flexibility can become 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. There are two reasons. First, by avoiding one space, we are avoiding more than one. For example, we eliminate the following confusion: Magazine   Subscribers; in this case, we may not know how many spaces are between the words. Second, using one-word names makes it easy to use the name in an expression where we can omit surrounding it with square brackets

1. Start Microsoft Access and create a Blank Database named GCS1
2. To create a new table, on the main menu, click Insert -> Table
    
   
3. In the New Table dialog box, click Table Wizard and click OK
4. In the first page of the Table Wizard, make sure the Business radio button is selected.
   In the Samples Tables list box, click Customers
5. In the Sample Fields list box, double-click CustomerID, PhoneNumber
6. Click Next
7. Accept the name of the table as Customers and click Next
8. Click Finish

In the first lesson, we mentioned that Microsoft Access is equipped with its own library used to create and manage databases. You can verify the existence of this library if you open the References dialog box from the Tools menu of Microsoft Visual Basic:

To programmatically create a table using the Microsoft Access Object Library, you can first declare a variable of type Object and then initialize it with the CreateTableDef() method of the current database object. This method takes as argument the name of the new table. After creating the table, you must add it to the current database. To support this, the CurrentDb object is equipped with the TableDefs property. TableDefs is in fact a collection. The TableDefs collection is equipped with the Append() method that is used to add a new table to the current database. This would be done as follows:

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

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

In DAO, a table is an object of type TableDef. The tables of a DAO database are stored in a collection called TableDefs. To give access to this collection, the DAO object is equipped with a property named TableDefs that is of type TableDefs.

To provide access to a TableDef object, the DAO object is equipped with a property called TableDef. Before creating a table, you should first declare a variable of type DAO.TableDef. To initiate the table, use the Set operator to assign the CreateTableDef() method of a database you have previously opened. here is an example:

Private Sub cmdCreateTable_Click()
   Dim dbDeja As DAO.Database
   Dim tblEmployees As DAO.TableDef

   ' Open the database
   Set dbDeja = DBEngine.OpenDatabase("C:\Yevol\Exercise.mdb")

   ' Create a new TableDef object.
   Set tblEmployees = dbDeja.CreateTableDef("Employees")

   . . .

End Sub

After initiating a table, you can add it to the database by passing it to the Append() method of the TableDefs property of the database that will receive the table. This would be done as follows:

Private Sub cmdCreateTable_Click()
    Dim dbDeja As DAO.Database
   Dim tblEmployees As DAO.TableDef

   ' Open the database
   Set dbDeja = DBEngine.OpenDatabase("C:\Yevol\Exercise.mdb")

   ' Create a new TableDef object.
   Set tblEmployees = dbDeja.CreateTableDef("Employees")

   . . .

   ' Add the new table to the database.
   dbDeja.TableDefs.Append tblEmployees
   dbDeja.Close
End Sub

In the SQL, to create a table, you can type the expression CREATE TABLE followed by the name of the table. The syntax starts with:

CREATE TABLE Name;

The CREATE and TABLE keywords must be used to let the SQL interpreter know that you want to create a table. The Name factor specifies the name of the new table. The Name can use the rules and suggestions we have been applying to the variables of the database objects. As mentioned already, a table needs at least one column. The formula to create a column is:

CREATE TABLE Employees(ColumnName DataType)

A column is specified with a name and a data type. The name can follow the rules and suggestions we reviewed for the tables.

After formulating the SQL statement, you can pass it to the RunSQL() method of the DoCmd object. Here is an example that creates a table named Employees:

Private Sub cmdTable_Click()
    DoCmd.RunSQL "CREATE TABLE Employees(EmployeeName Text);"
End Sub

The ADO library relies on the SQL to perform most of its database operations. Based on this, to create a database in ADO, formulate a SQL statement that creates a database and let ADO execute it. To execute a SQL statement in ADO, the Connection object is equipped with the Execute() method. Its syntax is:

Connection.Execute ExecString, RecordsAffected, Options 

In this syntax, the SQL statement that creates the database can be passed as the first argument. The other two arguments are optional.

Before performing most operations on a table, you may need to select it. This is a routine operation that is usually done transparently but in some cases, it is a prerequisite. If you select a table, some operations you perform may affect it, depending on how such operations are carried out. A table indicates that it is selected when it is highlighted:

In this example, a table named CD@Home is selected.

To select a table, in the Tables section of the Database window, you can simply click it, once. If another table or another item is already selected in the Tables section of the Database window, you can press the up or the down arrow key continuously until the table is selected.

To programmatically select a table, you can use the DoCmd object that is equipped with the SelectObject() method. The syntax to use would be:

DoCmd.SelectObject acTable, [objectname][, indatabasewindow]

The first argument must be acTable in this case because you want to select a table. The second argument is the name of the table you want to select. If you want to select the table and only highlight it in the Database window, then pass the third argument as True.

Here is an example:

If the table is already opened (in the next section we will see how to open a table) and it is displaying, it is most likely in the background. If you omit the third argument or pass it as False, the table would be displayed in the foreground. If the table is not opened and you omit the third argument or pass it as False, you would receive an error. You can use a conditional statement and error handling to make sure the user doesn't see this nasty dialog box.

By default, if you open a database in Microsoft Access, all of its tables are closed. Before using a table, you may need to open it first and this depends on what you want to do with the table. A table can be opened in one of three different views:

• The Datasheet View, also called normal view, of a table displays as a spreadsheet: it is divided in (vertical) categories and horizontal rows:
   
  
   
  To open a table in normal view, from the Tables section of the Database window, you can either double-click the table or you can right-click it and click Open
• The Design View of a table is used for table design
   
  
   
  To open a table in Design View, from the Tables section of the Database window, right-click the desired table and click Design View. Alternatively, you can first select it. Then, on the toolbar of the Database window, click Design
• The Preview view of a table resembles a report and allows you to see what it would look like if you wanted to print it
   
  
   
  To open a table in Preview mode, from the Tables section of the Database window, right-click the table and click Print Preview

To programmatically open a table, you can use the DoCmd object that provides the OpenTable() method. Its syntax is:

DoCmd.OpenTable tablename[, view][, datamode]

The first argument of this method is the name of the table that you want to open. The second argument is a constant value as follows:

This second argument is optional. If you omit it, the acViewNormal option applies.

The third argument, also optional, has to do with data entry, which we haven't reviewed yet. This means that you can omit it.

Here is an example:

When this  code executes, a table named Members would be opened in Design View.

After using a table, you can close it. If there is a structural change that needs to be saved, Microsoft Access would prompt you.

To close a table, you can click its system Close button or . You can also double-click its System button on the left side of its title bar. You can also press Ctrl + F4.

To programmatically close a table, you can call the Close() method of the DoCmd object. Its syntax is:

DoCmd.Close acTable, [objectname], [save]

The first argument must be specified as acTable because you are trying to close a table. The second argument can be the name of the table you want to close. If you suspect that there might be a need to save the structure of the table, you can pass the third argument with one of the following values:

1. To close the Customers table, click its system Close button or
2. In the same way, close the database

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 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. Also, you should check whether the table is involved in a relationship or an expression.

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.

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