The Tables of a Database

DoMenuItem(ByVal MenuBar As Variant, _
	   ByVal MenuName As Variant, _
	   ByVal Command As Variant, _
	   ByVal Subcommand As Variant, _
	   ByVal Version As Variant)

We will mentioned the arguments when we need to call this method. 

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

To create a table in Microsoft Access, on the Ribbon, you can click Create. In the Tables section of the Create tab of the Ribbon, you can click the Table button . This would display a spreadsheet-like window. The Datasheet View appears like a spreadsheet:

Another way to create or modify a table consists of displaying it in Design View. To do this, on the Ribbon, click Create. In the Tables section, click the Table Design button . To programmatically start a new table in Design View, you can execute the following code:

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

 Any of these two actions would display a type of window made of two parts. This is the Design View of a table:

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

The third option used to create a new table, probably the most convenient, consists of using a sample table provided by Microsoft Access. to get one of those, on the Ribbon, click Create. In the Tables section, click the Table Templates button . From the list, you can click the desired table. The table would then appear in Datasheet View.

We have seen that you can start or display a table either in Datasheet View or in Design View. At times, you will want to switch a table from one view to the other:

• To switch from the Datasheet View to the Design View:
  • You can right-click the title bar of the table and click Design View
  • On the Ribbon, click either the Home or the Datasheet tab. In the Views section, click the arrow under the View button and click Design View
• To switch from the Datasheet View to the Design View:
  • You can right-click the title bar of the table and click Datasheet View
  • On the Ribbon, click either the Home or the Datasheet tab. In the Views section, click the arrow under the View button and click Datasheet View

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

1. Start Microsoft Access and create a Blank Database named gcs1.
   Notice that you get a default table
2. To save the new table, right-click its title bar and click Save
3. Set the name to Customers and click OK

In the first lesson, we mentioned that Microsoft Access is equipped with its own library used to create and manage databases. 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. Here is an example:

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")
    
End Sub

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 initialize 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:\My Documents\Exercise.accdb")

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

   . . .

End Sub

After initializing the 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:\My Documents\Exercise.accdb")

   ' 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 table in ADO, formulate a SQL statement that creates a table 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 Cars is selected. To visually select a table, in the Navigation Pane, you can simply click it, once. If another table or another item is already selected in the Navigation Pane, 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 Navigation Pane, then pass the third argument as True.

Here is an example:

Private Sub cmdSelectTable_Click()
    DoCmd.SelectObject acTable, "Cars", True
End Sub

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.

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 visually close a table:

• You can click its Close button
• You can press Ctrl + F4
• If the database is configured to show overlapped windows, you can also double-click its system icon on the left side of its title bar. .

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

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

 The first argument is a member of the AcObjectType enumeration. For a table, the syntax to use is:

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

You can omit the AcObjectType:

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

The first argument must be specified as acTable because you are trying to close a table. The second argument must 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:

• To close the Customers table, on the right side of its tab, click its Close button

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:

• To open a table in Datasheet View, in the Navigation Pane, you can either double-click the table or you can right-click it and click Open
• To open a table in Design View, in the Navigation Pane, right-click the desired table and click Design View

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:

Private Sub cmdOpenTable_Click()
    DoCmd.OpenTable "Cars", AcView.acViewNormal, AcOpenDataMode.acReadOnly
End Sub

When this  code executes, a table named Cars would be opened in Datasheet View.

• In the Navigation Pane, double-click Customers to open the table

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 named 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. 

When using the Name property of a table, you are supposed to know the name of the table you want to access. In some cases, you may not know the name of a table. An alternative is to access a table by its index in the collection. To support this, the AllTables collection is equipped with an Item() property. 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 access the name of the third table of the current database:

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 reference to the table you intend to work on.

If a table exists already, to get a reference to it using the Microsoft Access Object library, first declare a variable of type Object. 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 Navigation Pane, 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:

Private Sub cmdRenameTable_Click()
    DoCmd.Rename "Employees", acTable, "StaffMembers"
End Sub

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

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, in the Navigation Pane, you can right-click the table and click Copy. Then right-click somewhere in the Navigation Pane again and click Paste 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.

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 Paste Table As dialog box.

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:

Private Sub cmdCopyTable_Click()
    DoCmd.CopyObject, "Teachers", acTable, "StaffMembers"
End Sub

From this example, a table named Teachers will be copied to generate a new table named StaffMembers , 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 Navigation Pane, 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:

Private Sub cmdDeleteTable_Click()
    DoCmd.DeleteObject acTable, "Members"
End Sub

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:\My Documents\Exercise.accdb'"
    
    strSQL = "DROP TABLE Departments;"
    
    conDepartments.Execute strSQL
    
    MsgBox "The Departments table of the Exercise.accdb database has been deleted"
    
    Set conDepartments = Nothing
End Sub

We have introduced a database as an application made of one or more lists. We also mentioned that, to make a list easy to view and explore, its items should be organized in categories. Here is the example we used:

In a list like this one, each category of information is called a column. In reality, the idea of a column is based on the fact that the categories are organized vertically. This, of course, makes it possible to know that under a column, the information is of the same type. When it comes to types, a database can also be configured so that each column is made for a particular type of data and some types of values would be excluded. Fortunately, Microsoft Access provides all the tools you need to create, configure, and maintain a column with the maximum flexibility.

The columns of a table are used to organize data and they are appropriate for table design. When the users start performing data entry, some of them may not find tables user-friendly. An alternative is to create forms that would produce the same results as if working on a table.

As you may guess, a column must be part of a table. In fact, a table without a column is non-existent and a column must belong to a table. There are various techniques you can use to create a column, depending on how you started the table. Probably the easiest technique consists of using an existing column provided by Microsoft Access. This would be a column that has already been named and configured. To get such a column, while the table is display in Datasheet View, in the Fields & Columns section of the Ribbon, click the New Field button . This would display a list of fields you can choose from:

To use one of these fields, drag it to a table in Datasheet View:

The Design View of a table allows you to provide as much detail as possible about a column you are creating. The Datasheet View is primarily made for data entry but it still allows you to create columns.

After creating the columns of a table, you can either generate a corresponding form or report, or you can design from scratch.

1. On the Ribbon, click Datasheet
2. In the Fields and Columns section, click New Field
3. In the Field Templates, scroll down in the list to see Contacts
4. Drag First Name and drop it on the right side of ID on the table
5. Once again in the Field Templates, drag Last Name and drop it on the right side of First Name on the table
6. To save the table, right-click its title bar and click Save

Like every object of the computer or of a database, the primary attribute of a column or a placeholder is its name. As mentioned for a table, Microsoft Access is very flexible with the names. On a table:

• The name of a column can start with a letter, a digit, an underscore, or even a special character. For example, you can have a column named #22
• The name of a column can contain one or more spaces

Names of columns such as @90T or Kw_3%3 can be confusing. Also, you are more likely to involve the names of columns in various expressions. Such expressions may not work with bizarre names. Based on this, we will adopt the same types of naming conventions we reviewed for tables:

• The name of a column will start with a letter. In most cases, the name will start in uppercase
• When a name is a combination of words, each part will start in uppercase. Examples are First Name or Date Hired
• In most cases, we will avoid including space in a name but remember that Microsoft Access allows it. All you have to do is to surround the name with square brackets when using it in an expression

If you are working in the Datasheet View of a table and if you use one of the columns from the Field Templates window, each column you select would already have a name. If you are working in the Design View of a table, to set the name of a column, click a new cell under the Field Name column, type the desired name and press Enter or Tab:

1. To switch the table to Design View, right-click its title bar and click Design View
2. In the top section of the table, double-click ID to select it (it should be selected already) and type CustomerID
3. Click the first empty box under Field Name and type DateHired
4. To save the table, right-click its title bar and click Save

When showing a table in the Datasheet View, each column displays a string in its top section to indicate what it is used for. This string is referred to the column's caption. When creating a column in the Design View of a table, if you specify only the column name and switch it to Datasheet view, the column would use its name to display the caption. This means that the caption may appear in one word.

If you want to display a friendlier caption, in the Design View of the table, after specifying a column's name, in the lower section of the window, enter the desired string in the Caption field. There is no significant relationship between the column's name and its caption. You can type anything you want in the caption but you should give it a string that resembles the name of the column. For example, if a column is named FirstName, its caption should be First Name.

1. In the top section of the window, click CustomerID
2. In the bottom section of the window, click Caption and type Customer ID
3. In the top section of the window, click DateHired
4. In the bottom section of the window, click Caption and type Date Hired
5. To close and save the table, on the right side of its tab, click the close button
6. When asked whether you want to save, click Yes
7. Click the Office button and click Access Options...
8. In the left frame, click Current Database
9. In the right frame, click Overlapping Windows
10. Click OK
11. On the message box, click OK
12. Close Microsoft Access

We saw how to start a programmatic creation of a table in the Microsoft Access Object Library 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

To support the creation of a column using the Microsoft Access Object Library, its table is equipped with a method named CreateField. This method takes three arguments and its syntax is:

Set field = object.CreateField(ByValye ColumnName As String, _
			       ByVal DataType As FieldType, _
			       ByVal Size As Integer)

Before calling this method, declare a variable of type Object for the new column. When calling this method, get a reference to it and assign it to the variable you would have created for the column. This would be done as follows:

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

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

None of the arguments to the CreateField() method is required.

The first argument of the CreateField() method, name, soecifies the name of the new column. The name follows the rules we saw for names of columns. Here is an example:

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

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

If you call the method without specifying the name of the column, at one time or another before actually creating the field, you will have to set its name. Otherwise you will receive an error. to support this, the field object has a property named Name. Therefore, to specify the name of the column, access its Name property and assign the desired string. Here is an example:

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

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

The second argument to the CreateField() method specifies the data type that would be applied on the column. We will review data types in other sections. Once again, the argument is optional. If you omit it when calling the method, you must specify it before actually creating the table. To support this, the field class is equipped with a property named Type. Therefore, to specify the data type of a column, assign the desired type to its Type property.

The third argument to the CreateField() method holds a piece of information that has to do with either the computer memory or the number of characters.

After creating a column, you must add it to the table. To support this, the table object is equipped with a property named Fields, which is a collection. We saw that the collections classes are equipped with a method named Add. The Fields collection of the table is equipped with an equivalent method but named Append method. This method takes as argument an object that represents the collection.

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

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

To support fields of a table, the DAO class is equipped with a property named Field, which represents a class of the same name. Therefore, before creating a column in the Microsoft DAO Object Library, declare a variable of type DAO.Field. Here are examples:

Private Sub cmdCreateTable_Click()   
    Dim fldEmployeeNumber As DAO.Field
    Dim fldFirstName As DAO.Field
    Dim fldLastName As DAO.Field
End Sub

Before creating the column(s), first initialize a table as we saw in the previous lesson:

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

   ' Open the database
   Set dbExercise = DBEngine.OpenDatabase("C:\My Documents\Exercise.accdb")

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

   . . .

End Sub

After initializing the table, you can add the desired columns to it. To create a column, you can call the CreateField() method of the TableDef object and assign it to the column variable. The formula to follow is:

Set fldEmployeeNumber = tblEmployees.CreateField(ByVal ColumnName As String, _
			       			 ByVal DataType As FieldType, _
			       			 ByVal FieldSize As Integer)

The arguments of this method follow exactly the same descriptions we reviewed for the Microsoft Access Object Library.

After creating the column, you can add it to the table. To do this, you can pass it to the Append() method of the Fields collection of the TableDef class. This would be done as follows:

Private Sub cmdCreateTable_Click()
    Dim dbExercise As DAO.Database
    Dim tblEmployees As DAO.TableDef
    Dim fldEmployeeNumber As DAO.Field
    
    ' Specify the database to use
    Set dbExercise = DBEngine.OpenDatabase("C:\My Documents\Exercise.accdb")

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

    Set fldEmployeeNumber = tblEmployees.CreateField(ColumnName, DataType, FieldSize)
    tblEmployees.Fields.Append fldEmployeeNumber
    

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

To specify the name of the new column, when calling the DAO.Database.CreateTableDef() method, pass the first argument as a string. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim dbExercise As DAO.Database
    Dim tblEmployees As DAO.TableDef
    Dim fldEmployeeNumber As DAO.Field
    
    ' Specify the database to use
    Set dbExercise = DBEngine.OpenDatabase("C:\My Documents\Exercise.accdb")

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

    Set fldEmployeeNumber = _
		tblEmployees.CreateField("EmployeeNumber", DataType, FieldSize)
    tblEmployees.Fields.Append fldEmployeeNumber
    

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

As mentioned already, to work in SQL, you can use the DoCmd class. To get a DoCmd object, you can access it as a property of the Application that is readily available whenever you start Microsoft Access.

To create a column in the SQL, specify its name, followed by its data type, and some possible options. In the parentheses of the CREATE TABLE TableName() expression, the formula of creating a column is:

ColumnName DataType Options

Notice that there is only space that separates the sections of the formula. This formula is for creating one column. If you want the table to have more than one column, follow this formula as many times as possible but separate them with commas. This would be done as follows:

CREATE TABLE TableName(ColumnName DataType Options, ColumnName DataType Options)

When creating a column, the first information you must provide is its name. Here is an example that starts a table with a column named

Private Sub cmdCreateTable_Click()
   DoCmd.RunSQL "CREATE TABLE Customers(FirstName . . .);"
End Sub

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

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 have a column you don't need anymore on a table, you can remove that column.

To programmatically delete a column, if you are using either the 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.

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

In the database environments, a column is called a field. In the various libraries used in Microsoft Access, a column is an object of type Field. The columns of a table are stored in a collection called Fields. To give access to this collection, in the Microsoft Access Object Library or in DAO, the TableDef object is equipped with a property named Fields that is of type Fields and each one of its items is an object of type Field.

After creating the columns of a table, you may want to know the number of columns that a table has. To give you this information, the Fields collection is equipped with a property named Count.

To identify each column of a table, the Fields collection is equipped with a property named Item. This type of property is also referred to as indexed because it takes an argument that identifies the particular member that you want to access in the collection.

To access a column, you can pass its name or its index to the Item() indexed property. If you know the name of the column, you can pass it as a string. Here is an example:

Fields.Item("[Last Name]")

Item is the default property of a Fields collection. Therefore, you can omit it. Based on this, we can also write:

Fields("[Last Name]")

If you don't know the name of a column or you prefer to access it by its index, you can pass that index to the Item property. Remember that the index starts at 0, followed by 1, and so on. Based on this, to access the third column of a table, you would use either of these two:

Fields.Item(2)
Fields(2)

After creating a table and filling it up with some values, you can explore them. One way you can do this consists of isolating records based on specific conditions. This technique of isolating records is also referred to as filtering. To filter records of a table and display the results to the user, you have various alternatives.

Data filtering is performed using the SQL and other means provided by Microsoft Access. To filter data, you can either create a query or write a SQL statement.

To create a query in Microsoft Access, on the Ribbon, you can click Create. In the Other section, you would click Query Wizard or Query Design.

To programmatically create a query using either the Microsoft Access Object Library or DAO, you can use the CreateQueryDef() method of the Database object. The syntax of this method is:

CreateQueryDef(NewQueryName, SQLStatement)

The first argument is the name you will give to the new query. The second argument is a SQL statement that specifies the contents of the query.

After creating a query and saving it, you can use it as the source of data for a form. To do this, you can visually specify it in the Record Source property of the form. You can also assign the name of a query, as a string, to the form's RecordSource property. Here is an example:

Private Sub cmdSetRecordSource_Click()
    Me.RecordSource = "ListOfEmployees"
End Sub

Instead of first creating a query, you can access the Record Source property of the form and click its ellipsis button. This would prompt you to visually create a query. After creating it, you can close the query window. Microsoft Access would create the necessary SQL statement for you and assign it to the Record Source property of the form.

Instead of visually creating a query, you can write a SQL statement that selects the records and assign that statement to the RecordSource property of the form in code.

Once a query has been created and saved, it becomes a database object like the others we have used so far. If a query exists already, to use it, the user can open it like a table. To programmatically open a query, you can call the OpenQuery() method of the DoCmd object. This method takes one string argument as the name of the query. Here is an example:

Private Sub cmdOpenVideoTitles_Click()
    DoCmd.OpenQuery "VideoTitles"
End Sub

After using a query, the user can close it like a regular window by clicking its system Close button. To programmatically close a query, you can call the Close() method of the DoCmd object, passing the first argument as acQuery and the second argument as the name of the query. Here is an example:

Private Sub cmdCloseVideoTitles_Click()
    DoCmd.Close acQuery, "VideoTitles"
End Sub

When this method is called, it checks whether the query is opened. If a query with that name is opened, it would be closed. If no query with that name is opened, nothing would happen.