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).
In some of our lessons, we will use a database named Kolo Bank. To have an understanding of how this database works, we thought it would be a good idea if you create it from scratch yourself.
Kolo Bank is a (fictitious) financial institution where people open bank accounts to keep their money. The bank offers various types of accounts, including checking accounts, savings accounts, and certificates of deposit (CD).
The bank operates at various locations in the Washington, DC metropolitan area (in Silver Spring, MD, in Washington, DC, in Alexandria, VA, and many other places). There are two types of locations: branches and automatic teller machines (ATMs). Each branch has a name, an address, and staff members. An ATM is a machine where customers inserts a card (issued by the bank) to perform transactions such as checking their account balance, depositing, or retrieving money. Each location, whether a branch or an ATM, will be identified in the database with with a unique code, named a location code.
Staff members are employees of the bank. Each employee must be identified with a unique number, a name, a title, and other valuable information. Because customers are allowed to perform transactions at ATMs, each ATM is treated as an employee and has an employee number.
To keep money in the bank, a customer must open an account. The customer is then given a (unique) bank account. Besides the bank account, each customer must be identified with at least a name, the type of account, the address, and other necessary pieces of information.
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):
To open the window that allows you to write code:
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.
Other means of creating and using SQL code include:
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.
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:
The value on the right side of SELECT must be appropriate and we will see examples in the next few sections.
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:
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:
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.
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:
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:
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:
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.
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:
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.
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 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.
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.
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:
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:
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.
A database is a list of values. Such a list of values is called a table. Here is an example of a table:
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.
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:
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.
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(. . .)
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
When you create a table with code, it may not show right away in the Navigation Pane. To show it, you have various options. If you create a table using a control on a form, if you switch the form to Design View, the action would refresh the list of objects in the Navigation Pane. As a better alternative, the Application class provides a method named RefreshDatabaseWindow. Its syntax is:
Public Sub Application.RefreshDatabaseWindow
As you can see, this method doesn't take any argument. It is simply used to refresh the Navigation Pane.
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.
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.
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:
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.
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:
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 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:
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 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.
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.
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. 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.
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;
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.
Here is an example of a table:
In this case, examples of columns are First Name, Last Name, Date Hired, and Hourly Salary.
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
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:
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
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 fldEmployeeName As Field . . . End Sub
The columns of a table are stored in a collection called Fields.
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:
Item is the default property of a Fields collection. Therefore, you can omit it. Based on this, we can also write:
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:
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.
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.
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.
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
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