The Tables of a Database

A Table as a List

Introduction

Imagine that you want to create a computer database for an ice cream shop. 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. After displaying the Database window, it 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 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.

The Name of a Table

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. 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 particularly 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; if 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

Practical Learning: Creating a Table

Table Creation With the Microsoft Access Library

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, 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

Table Creation With DAO

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:\FunctionX\Deja.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:

Table Creation With SQL

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

Table Creation With ADO

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.