Lesson 12: Introduction to Microsoft Access Databases

	Introduction to Microsoft Access Databases

The Structured Query Language (SQL)

Introduction

Microsoft Visual Basic is used for its ability to handle computer applications for the Microsoft Windows family of operating systems. To provide a common approach to database development, a universal language was developed to apply it to any programming environment or any operating system. The Structured Query Language, or SQL, is a language used to create and maintain databases.

SQL can be pronounced Sequel or S. Q. L. In our lessons, we will consider the Sequel pronunciation. For this reason, the abbreviation will always be considered as a word, which would result in "A SQL statement" instead of "An SQL statement". Also, in these lessons, we will regularly write, "The SQL" instead of “The SQL language, as the L already represents Language.

The SQL is used by various database environments such as Microsoft Access, MySQL, Microsoft SQL Server, Oracle, Paradox, etc. As it usually happens, each environment adds its flavor to the language to adapt it to whatever needs to be done. Based on this, the SQL we will learn in these lessons has some differences with the way it is interpreted in other programming environments.

As it is common with other computer languages, the SQL comes with its syntax, vocabulary, and rules. The SQL is equipped with keywords that tell it what to do and how to do it. In these lessons, we will apply the SQL as it is implemented in Microsoft Access.

Microsoft Access supports many ways of using, interpreting, or applying the SQL. Still, probably the most common or simplest way to use SQL is to call the RunSQL() method of the DoCmd object. Its syntax is:

Sub Expression.RunSQL(ByVal SQLStatement As Variant, Optional ByVal UseTransaction As Variant)

This method takes two arguments. The first one is required and contains what you want to use. The second argument is optional and has to do with transactions (which we haven't covered).

Practical Learning: Introducing SQL

Start Microsoft Access
Open the Exercise3 database

A SQL Statement

When using SQL, you write short or long sections of code. Code based on SQL is referred to as a SQL statement. In Microsoft Access, to open a window that you can use to create or use a SQL statement, on the Ribbon, click Create. In the Queries section, click Query Design. This would display the Show Table dialog box. Click Close (in future lessons, we will learn more about that Show Table dialog box):

Query

To open the window that allows you to write code:

Right-click the middle of the Query window and click SQL View
On the Ribbon, in the Results section of the Design tab of the Ribbon, click the SQL button
On the Ribbon, in the Results section, click the arrow of the View button and click SQL View

This would display a window with a default line of code. You can either edit it or delete the default code and replace it with your own code.
After writing your SQL expression, you can execute it to see the result. To do this, in the Results section of the Ribbon, click the Run button . After viewing the result, to return to the Query window:

Right-click the title bar of the window and click SQL Code
In the Results section of the Home tab of the Ribbon, click the arrow of the View button and click SQL Code

Other means of creating and using SQL code include:

On the Ribbon, click Create. In the Queries section, click Query Wizard . Use the Query Wizard to create a query. When you finish, the database engine would generate the necessary SQL code for you. You can then modify that code to suit your needs
In Microsoft Visual Basic, you can write a SQL statement as a string and pass it to the RunSQL() method of the DoCmd object

When writing an expression, SQL is not case-sensitive. This means that the words Case, case, and CASE represent the same thing. This applies to keywords of the SQL or words that you will add in your expressions.

Introduction to SQL Operators

SELECT Something

The most fundamental operator used in the SQL is called SELECT. This operator is primarily used to display a value to the user. In this simple case, it uses the following formula:

SELECT Value;

The value on the right side of SELECT must be appropriate and we will see examples in the next few sections.

SELECT This AS

When you execute a SQL statement in the Query window, its results are displayed in a spreadsheet. To be able to recognize a value, the top section of a column of the spreadsheet displays a label, called a caption. Here is an example:

SQL Statement Result

If you create a simple SELECT Value; expression, Microsoft Access assigns a default caption to the column. In reality, the SQL allows you to specify a caption that would be used for the value. This is done using the following basic formula:

SELECT Value As Caption;

The words SELECT and AS are required. As mentioned already, SELECT would be used to specify a value and AS in this case allows you to specify a caption of your choice.

The value to select can be a number. Here is an example:

SELECT 48;

The caption can be made of a word but the word cannot be one of the SQL's keywords. Here is an example:

SELECT 48 AS Age;

This would produce:

You can also use non-literal characters or digits in the caption. If the caption is made of a combination of words, you can concatenate them to create one word. Here is an example:

SELECT 24.85 AS HourlySalary;

If you want the caption to display different words, you can include them between an opening square bracket "[" and a closing bracket "]". Here is an example:

SELECT 25.05 AS [Hourly Salary];

This would produce:

Instead of displaying one column, you may want to display more than one. To do this, you can separate them with commas. Here is an example:

SELECT 42.50 AS [Weekly Hours], 25.05 AS [Hourly Salary];

This would produce:

Besides a number, the value of a SELECT expression can be a string. If it is, you can include it between single-quotes. Here is an example:

SELECT 'Martens, Laurent' AS [Employee Name];

In the same way, you can mix number-based and string-based columns.

Practical Learning: Introducing SELECT

On the Ribbon, click Create
In the Queries section, click Query Design
In the Show Table dialog box, click Close
Right-click the top section of the window and click SQL View

To use SELECT, change the statement as follows:

SELECT 'Larens' AS LastName,38.50 AS [Weekly Hours],22.12 AS [Hourly Salary];

SQL Statement

To view the result, on the Ribbon, click the Run button
To return to the code window, on the Ribbon, click the arrow of the View button and click SQL View

Unary Operators

The Positive Operator +

Algebra uses a type of ruler to classify numbers. This ruler has a middle position of zero. The numbers on the left side of the 0 are referred to as negative while the numbers on the right side of the rulers are considered positive:

-∞	-6	-5	-4	-3	-2	-1	1	2	3	4	5	6	+∞
0
-∞	-6	-5	-4	-3	-2	-1	1	2	3	4	5	6	+∞

A value on the right side of 0 is considered positive. To express that a number is positive, you can write a + sign on its left. Examples are +4, +228, +90335. In this case the + symbol is called a unary operator because it acts on only one operand.

The positive unary operator, when used, must be positioned on the left side of its operand, never on the right side.

As a mathematical convention, when a value is positive, you do not need to express it with the + operator. Just writing the number without any symbol signifies that the number is positive. Therefore, the numbers +4, +228, and +90335 can be, and are better, expressed as 4, 228, 90335. Because the value does not display a sign, it is referred as unsigned as we learned in the previous lesson.

To express a variable as positive or unsigned, you can just type it. here is an example:

SELECT +1250

The Negative Operator -

As you can see on the above ruler, in order to express any number on the left side of 0, it must be appended with a sign, namely the - symbol. Examples are -12, -448, -32706. A value accompanied by - is referred to as negative.

The - sign must be typed on the left side of the number it is used to negate.

Remember that if a number does not have a sign, it is considered positive. Therefore, whenever a number is negative, it MUST have a - sign. In the same way, if you want to change a value from positive to negative, you can just add a - sign to its left.

Here is an example that uses two variables. One has a positive value while the other has a negative value:

SELECT -1250

Binary Operators

String Concatenation

We have seen that the SELECT keyword could be used to create a list of values. These values are separate of each other. You can also combine values to create a string or a value that is in fact an expression. For example, you can combine a first name and a last name to create a full name. An expression that combines columns can be performed on text-based columns. such as a first name being added to a last name to get a full name.

String concatenation consists of adding one string to another to get a new string. This is done using the & operator. The formula of the expression is:

String1 & String2

String1 and String2 must be recognizable strings. When this statement executes, String2 would added to the end of String1, resulting in String1String2. In the same way, you can add as many strings as you want by separating them with the & operator.

Practical Learning: Concatenating Strings

To concatenate strings, change the statement as follows:

SELECT "Larens" & ", " & "Ernestine" AS [Employee Name],
       38.50 AS [Weekly Hours],
       22.12 AS [Hourly Salary];

To execute the statement, right-click the title bar of the window and click Datasheet View
To return to the code window, on the Ribbon, click View -> SQL View

The Addition +

The addition can be used to add one value to another. This is done using the + operator. Here is an example:

SELECT 412.48 + 66.84 AS Total;

This would produce:

Addition

The order you use to add two or more values doesn't matter. This means Value1 + Value2 is the same as Value2 + Value1. In the same way a + b + c is the same as a + c + b the same as b + a + c and the same as c + b + a. This means that the addition is associative.

Practical Learning: Using the Addition

To use the addition, change the statement as follows:

SELECT "Larens, Ernestine" AS [Employee Name], 8.50 AS Monday, 9.50 AS Tuesday, 
8.00 AS Wednesday, 8.00 AS Thursday, 8.50 AS Friday, 
Monday+Tuesday+Wednesday+Thursday+Friday AS [Weekly Hours], 
22.12 AS [Hourly Salary];

To view the result, on the Ribbon, click the Run button
To return to the code window, on the Ribbon, click the arrow of the View button and click SQL View

The Subtraction

The subtraction is used to take out or subtract one value from another value. It is essentially the opposite of the addition. The subtraction is performed with the - sign. Here is an example:

SELECT 1240 - 608

Unlike the addition, the subtraction operation is not associative. This means that a - b - c is not necessarily equal to c - b - a.

The Multiplication *

The multiplication can be used to multiply one value by another. This is done using the * operator. For example, to get the weekly salary of an employee, you can multiply the weekly hours by the hourly salary and get the result. As mentioned for the addition, the order of the operands is not important.

Practical Learning: Using the Multiplication

To use the multiplication, change the statement as follows:

SELECT "Larens, Ernestine" AS [Employee Name],
8.50 AS Mon, 9.50 AS Tue, 8.00 AS Wed, 8.00 AS Thu, 8.50 AS Fri,
Mon+Tue+Wed+Thu+Fri AS [Weekly Hours],
22.12 AS [Hourly Salary], [Hourly Salary] * [Weekly Hours] AS [Weekly Salary];

To view the result, on the Ribbon, click the Run button
To return to the code window, on the Ribbon, click the arrow of the View button and click SQL View
Save and close the query window

The Division

The division is similar to cutting an item in pieces or fractions of a set value. Therefore, the division is used to get the fraction of one number in terms of another. The division is performed with the forward slash /. Here is an example:

SELECT 128 / 42

This would produce 3

When performing the division, be aware of its many rules. Never divide by zero (0). Make sure that you know the relationship(s) between the numbers involved in the operation.

The Modulo

In the above division, 128/42, the result is 3. When you multiply 42 by 3, as in 42*3, you get 126. In some cases, you may be interested in knowing the amount that was left out after the operation. The modulo operation is used to get the remainder of a division as a natural number. The remainder operation is performed with the MOD operator. Here is an example:

SELECT 128 Mod 42 AS [128 mod 42];

This would produce:

MOD

Parentheses

Like most computer languages, SQL uses parentheses to isolate a group of items that must be considered as belonging to one entity. For example, parentheses allow a procedure to delimit the list of its arguments. Parentheses can also be used to isolate an operation or an expression with regards to another operation or expression. For example, when studying the algebraic operations, we saw that the subtraction was not associative and could lead to unpredictable results. In the same way, if your operation involves various operators such as a mix of addition(s) and subtraction(s), you can use parentheses to specify how to proceed with the operations, that is, what operation should (must) be performed first. Here is an example:

SELECT (154 - 12) + 8 AS First, 154 - (12 + 8) AS Second;

This would produce:

Parentheses

As you can see, using the parentheses controls how the whole operation would proceed. This difference can be even more accentuated if your operation includes 3 or more operators and 4 or more operands.

Introduction to the Tables of a Database

Overview

A database is a list of values. Such a list of values is called a table. Here is an example of a table:

First Name	Last Name	Date Hired	Hourly Salary

Because Microsoft Access is a visual application, you will usually perform most of your operations visually by clicking here, clicking there, dragging here, and dropping there. Still some other operations you will have to perform with code. Many operations will use the DoCmd class. One of the methods of the DoCmd class is called DoMenuItem. Its syntax is:

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

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

Visually Creating a Table

As a database application, Microsoft Access provides all the tools you need to create the necessary tables of your database. To visually create a table in Microsoft Access, on the Ribbon, 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. 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

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

Design View

SQL as Data Definition Language

The structured query language comes in two broad parts. The data definition language (DDL) is the SQL part that is used to create the objects of a database. The most fundamental object of a database is the table.

Table Creation With SQL

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

CREATE TABLE Name;

The CREATE TABLE expression must be used to let the SQL interpreter know that you want to create a table. The Name specifies the name of the new table. The Name can use the rules and suggestions we used for variables. A table would start as follows::

CREATE TABLE Employees(. . .)

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

Although you should usually use one-word names for tables, you can use a name made of various parts. In this case, start the name with [ and end it with ]. Here is an example:

Private Sub cmdTable_Click()
    DoCmd.RunSQL "CREATE TABLE Full Time Consultants](...);"
End Sub

Using a Table

Selecting a Table

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 the Navigation Pane.

Table Selected

In this example, a table named Customers 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, "Employees", 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.

Opening a Table

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

View Name	Result
acViewDesign	The table will display in Design View
acViewNormal	The table will display in Datasheet View
acViewPreview	The table will display in Print Preview

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 "Employees", AcView.acViewNormal, AcOpenDataMode.acReadOnly
End Sub

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

Closing a Table

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

View Name	Result
acSaveNo	The table doesn't need to be saved
acSavePrompt	Prompt the user to save the changes
acSaveYes	Save the table without having to prompt the user

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

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. 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
    Dim 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 working on a database, the total number of its tables is stored as the Count property of the AllTables collection.

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

The ability to identify a table by name allows you to check the existence of a table in a database. For example, since Microsoft Access would not allow you to create a table if another table with the same name exists already, you can check to find out if a table exists already with the name you are trying to use. Here is an example:

' This function is used to search the tables in the current database.
' The function receives the name of the table to look for.
' If a table with that name exists already, the function returns true.
' If no table with that name is found, the function returns false
Private Function TableExists(ByVal tblName As String) As Boolean
    Dim obj As AccessObject, dbs As Object
    Set dbs = Application.CurrentData
    Dim exists As Boolean
    
    exists = False
    
    For Each obj In dbs.AllTables
        If obj.Name = tblName Then
            exists = True
        End If
    Next obj

    TableExists = exists
End Function

Private Sub cmdCreateTable_Click()
    If TableExists("Units") = True Then
        MsgBox "The table you are trying to create exists already."
    Else
        REM Create the table here
    End If
End Sub

TableExists is an accessory Boolean function that checks the existence of a table.

Renaming a Table

Renaming a table consists of changing its name from the original or previous name. 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.

Copying a Table

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

Introduction to Deleting a Table

If you happen to have a table you don't need anymore in your database, you can remove it. 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 Navigation Pane 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.

Deleting a Table in the SQL

To delete a table in SQL, 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:

DROP TABLE Countries;

Columns Fundamentals

Introduction

An mentioned already, a database is a series of tables. A table organizes its values in categories. Each category is called a column or a field.

From now on, we will use the words "column" and "field" interchangeably and they will always mean the same thing.

Here is an example of a table:

First Name	Last Name	Date Hired	Hourly Salary

In this case, examples of columns are First Name, Last Name, Date Hired, and Hourly Salary.

Creating a Column With SQL

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

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

Although we used the name in one word, you can use a name that is made of many parts. In this case, the name must start with [ and end with ]. Here is an example:

Private Sub cmdCreateTable_Click()
   DoCmd.RunSQL "CREATE TABLE Customers([First Name] . . .);"
End Sub

Introduction to Fields Types

Besides its name, the most fundamental detail you must provide about a column is the type of value it would hold. This piece of information is called a data type. If you are visually creating a table in the Design View, to specify the data type of a column, you can select an option in the corresponding Data Type section. Here is an example:

Text Field

The most basic value a column can hold is (a piece of) text. If you are working in the Design View of a table, after specifying the name of a column, select its Data Type as Text. A Text data type allows the user to enter any kinds of characters or group of characters.

The formula to create a table in SQL is:

CREATE TABLE TableName(Column1, Column2, Column_X)

We also know that the actual formula of creating a column is:

ColumnName DataType Options

We also saw how to specify the name of a new column. Here was our example:

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

After the name of the column, you must specify the data type of the column. If you are creating a column whose value would be text-based, you can apply the TEXT, the CHAR, or the VARCHAR data types. Here is an example:

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

In the same way, you can create as many columns as you want by separating them with commas. Here are examples:

Private Sub cmdMicrosoftAccess_Click()
   DoCmd.RunSQL "CREATE TABLE Customers(FirstName Text, LastName Char);"
End Sub

If you are planning to use a long SQL statement to create a table, you can either use the line continuation operator to span the statement on various lines, or you can declare a String variable, store the statement in it, and then pass that variable to the DoCmd.RunSQL() method.

Each one of the text, char, or varchar data types would produce the same effect. Here is an example:

Private Sub Detail_Click()
    DoCmd.RunSQL "CREATE TABLE RepairOrders(CustomerName text, " & _
                                           "CustomerAddress text, " & _
                                           "CustomerCity varchar, " & _
                                           "CustomerState char, " & _
                                           "CustomerZIPCode char, " & _
                                           "CarMakeModel varchar, " & _
                                           "CarYear char);"
End Sub

Columns Maintenance

Introduction to the Field Object

In the database environments, a column is called a field. In fact in Microsoft Access, a column is an object of type Field. To programmatically use a field, declare a variable of type Field. Here is an example:

Private Sub cmdVideoAnalyze_Click()
    Dim rstVideos As Recordset
    Dim fldEVideo As Field
    
    . . .
    
    rstVideos.Close
    Set rstVideos = Nothing
End Sub

The columns of a table are stored in a collection called Fields.

The Number of Fields in a Table

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.

Identifying a Field

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)

The name is the most fundamental characteristic of a column. Each column of the Fields collection is an object of type Field, which is the common name of a column in database systems. To identify the name of a column, the Field class is equipped with a property called Name.

The Value of a Field

A record is created by entering a value under a column in a table or in a Windows control of a form. To identify the value held by a column, the Field class is equipped with a property named Value.

Adding a New Column

In the SQL, to perform maintenance on a column, start with an ALTER TABLE expression as follows:

ALTER TABLE TableName ...

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

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

Deleting a Column

To delete a column using the SQL, 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