Microsoft Access VBA - Lesson 27: Topics on ADO

	Topics on ADO

The Data Source of an Application

Introduction

A database is primarily considered a project that holds one or more lists of items. There can be other issues involved such as how the data would be made available to the users, what computer(s) would access the data, what types of users (physical or else) would access the database.

A database is started as a computer file before being presented to the user(s). A database can reside in one computer and used by one person. A database can also be stored in one computer but accessed by different computers in a network. Another database can be created and stored in a server to be accessed through the Internet. These and other related scenarios should be dealt with to create and distribute an effective database.

A Data Source

You may plan to create a database that would be used by one person from one computer. As your job becomes more effective, you could be asked to create another database that would be accessed by different people. Regardless of why and how, after creating a database, you should have a way of making it available to those who would use. To do this, you can create a data source. With Microsoft Access on Microsoft Windows, you have two main options.

In Microsoft Access, a database is created as a computer file and it has a path, that is, where the database file is located. The path to a file is also known as its location. The path to a database, including its name, is also called the data source. In some of your database operations, you might be asked to provide a data source for your database. In this case, provide the complete path followed by the name of the database.

Using the path to a database is probably the easiest way to access it but this is more convenient if you are working in Microsoft Access. If you plan to access your database from another programming environment, one solution is to create an ODBC data source. To do this, in the Control Panel (Start -> Control Panel, System and Security), click Administrative Tools. Double-click Data Source (ODBC) to open the ODBC Data Source Administrator:

ODBC Data Source Administrator

Click the Add button. This would launch a wizard. In the first page of the Create New Data Source wizard, click Microsoft Access Driver (*.mdb, *.accdb):

Create New Data Source

Click Finish. In the following screen, you would be asked to enter a name for the data source. You can enter the name in one or more words. In the Description text box, you can enter a short sentence anyway you like. Here is an example:

ODBC Microsoft Access Setup

To specify the database that would be used, click Select. If the database is located on a local drive, select i. Here is an example:

Select Database

If the database is located in a shared drive of a computer connected to the one you are using, click Network, locate, and select the computer. After selecting the database, click OK. If you need to be authenticated in order to use the database (if the database is protected), click the Advanced button. By default, a database is meant to allow anybody to use. In this case, you can leave the Login Name and the Password empty. Otherwise, type the necessary credentials. Here is an example:

Data Source

After using (that is, if you had used) the Set Advanced Options dialog box, click OK (or Cancel to keep it the way it previously was):

ODBC Microsoft Access Setup

After entering the necessary information and selecting the desired database, you can click OK. Your new data source should appear in the list of User Data Sources:

ODBC Microsoft Access Setup

Click OK to close the ODBC Data Source Administrator dialog box.

Using an ODBC Data Source

To use an ODBC data source to open a database, include its name (and the credentials) in the connection string. Here is an example:

Private Sub cmdDataSource_Click()
    Dim connector As ADODB.Connection
    
    Set connector = New ADODB.Connection
    
    connector.Open "DSN=gdcs;UID=;PWD=;"

    Set connector = Nothing
End Sub

Remember that, in this case, the ODBC data source must have been created.

SQL Aggregate Functions

To help you with performing or getting statistics in a record set, Microsoft Access provides a series of procedures named aggregate functions. These functions practically use the same syntax that is:

Function FunctionName(ColumnName) As Variant

Each of these functions takes the name of a column as argument and produces or retrieves a result from that column. The value produced by the function depends on that function. The available functions are:

Count: Microsoft Access uses the Count() function to count the number of occurrences of the category in the column and produces the total
First: Microsoft Access uses the First() function to get the first occurrence of the value in the category
Last: Microsoft Access uses the Last() function to get the last occurrence of the value in the category
If the column holds numeric values:
- Sum: The Sum() function is used to sum up the values in the category
- Avg: The sum of value in a category would be divided by the number of occurrences in that category to get the average
- Min: The lowest value of the category would be produced from the Min() function
- Max: The highest value of the category would be produced using the Max() function
- StdDev: The standard deviation of the values of the category would be calculated using the StdDev() function
- Var: The statistical variance of the values in the category would be calculated

If none of these functions is suited for the type of statistic you want to get, you can write your own expression or condition.

Stored Procedures

Introduction

We know that a procedure is an assignment used to take care of a task. The procedure is created in a module. Normally, such an assignment is intended to be used only by the object (such as a form) that owns that module, unless you decide to invoke that procedure somewhere else in the database. One of the main limitations of such a procedure is that it is not an object and it cannot be saved as such. ADO provides an alternative.

You can create a procedure and save it as an object. Although you may use the event of a button, a form, or another control to create the procedure, the object or control doesn't own the procedure. In fact, after creating the procedure, it becomes an object and you can access it from anywhere in the database. Also, the procedure is saved as an object (as done for a table, a query, or a form) and it is stored in the database. Because the procedure is stored as an object, it is referred to as a stored procedure.

Like tables, queries, forms, and reports, stored procedures are represented in the Navigation Pane with an icon and a name.

Creating a Stored Procedure

To create a stored procedure, use the following formula:

CREATE PROCEDURE ProcedureName
AS
Body of the Procedure

Start with the CREATE PROCEDURE expression. You can also use CREATE PROC. Both expressions produce the same result. Like everything in your database, you must name your stored procedure. The name of a stored procedure can be any string that follows the rules of objects.

After the name of the procedure, type the AS keyword. The section, group of words, or group of lines after the AS keyword is called the body of the procedure. It states what you want the procedure to do or what you want it to produce.

It is important to keep in mind that there are many other issues related to creating a procedure.

Probably the simplest procedure you can write would consist of selecting columns from a table. This is done with the SELECT keyword and applying the techniques we reviewed for data selection. A stored procedure can also be used to enter new records in a table or to update one or some values of one or some of the records. For example, imagine that a company is hiring some contractors and all of them would be paid the same salary. You can create a stored procedure that would enter this salary into each existing record by simply updating them. The stored procedure can be created as follows:

Private Sub cmdPrepareNewMinSalary_Click()
    Dim conEmployees As ADODB.Connection
    Dim strProcedure As String
    
    Set conEmployees = Application.CurrentProject.Connection
    strProcedure = "CREATE PROCEDURE SetNewMinSalary " & _
                   "AS " & _
                   "Update Employees " & _
                   "SET HourlySalary = 12.50;"
    
    conEmployees.Execute strProcedure
    conEmployees.Close
End Sub

This procedure would be used to visit each record and assign or change the hourly salary of the employee to 12.50.

Executing a Stored Procedure

After creating a procedure, to get its result, you would need to execute it. To execute a procedure, you use the EXECUTE keyword followed by the name of the procedure. Although there are some other issues related to executing a procedure, for now, we will consider that the simplest syntax to call a procedure is:

EXECUTE ProcedureName

Alternatively, instead of EXECUTE, you can use the EXEC keyword:

EXEC ProcedureName

For example, to execute the above procedure, you would use code as follows:

Private Sub cmdNewMinSalary_Click()
    Dim conEmployees As ADODB.Connection
    Dim strProcedure As String
    
    Set conEmployees = Application.CurrentProject.Connection
    strProcedure = "EXECUTE SetNewMinSalary;"
    
    conEmployees.Execute strProcedure
    conEmployees.Close
End Sub

Arguments and Parameters

Like a regular function, a stored procedure can take one or more arguments. To create a procedure that takes an argument, type the formula CREATE PROCEDURE or CREATE PROC followed by the name of the procedure, then type the name of the argument. The parameter is created like a column of a table. That is, a parameter must have a name, a data type and an optional length. Here is the syntax you would use:

CREATE PROCEDURE ProcedureName
(ParameterName DataType)
AS
Body of the Procedure

When implementing the procedure, you can define what you want to do with the parameter(s), in the body of the procedure. Consider the following list of employees;

Employees

Imagine that you want to raise the salary of the employees who are still earning the minimum wage. Instead of simply setting everybody's minimum, you can create a stored procedure that would visit each record and, whenever it finds a salary below your minimum, it would raise it. You can create such as procedure as follows:

CREATE PROCEDURE SetNewMinSalary 
(NewMinSalary Currency)
AS
	UPDATE Employees
        SET Salary = NewMinSalary
	WHERE HourlySalary < NewMinSalary

This can be done in code as follows:

Private Sub cmdCreateProcedure_Click()
    Dim conEmployees As ADODB.Connection
    Dim strProcedure As String
    
    Set conEmployees = Application.CurrentProject.Connection
    strProcedure = "CREATE PROCEDURE SetNewMinSalary " & _
                   "(NewMinSalary Currency) " & _
                   "AS " & _
                   "Update Employees " & _
                   "SET HourlySalary = NewMinSalary " & _
                   "WHERE HourlySalary < NewMinSalary;"
                   
    conEmployees.Execute strProcedure
    
    MsgBox "The new stored procedure has been created."
    conEmployees.Close
End Sub

When executing a procedure that takes a parameter, make sure you provide a value for the parameter. The syntax used is:

EXEC ProcedureName ParameterValue

If the parameter is Boolean or numeric, make sure you provide an appropriate value. If the parameter is a character or a string, type its value in single-quotes. If it is a number, provide a valid value. Here is an example:

Private Sub cmdExecuteProcedure_Click()
    Dim conEmployees As ADODB.Connection
    Dim strProcedure As String
    
    Set conEmployees = Application.CurrentProject.Connection
    strProcedure = "EXECUTE SetNewMinSalary 14.50;"
    
    conEmployees.Execute strProcedure
    
    MsgBox "The minimum hourly salary has been set to 14.50/hr"
    conEmployees.Close
End Sub

When this code executes, every employee whose salary is less than 10.15 would be raised to 14.50. The others would be kept as is. The above code would produce:

Another type of procedure can be made to take more than one parameter. In this case, create the parameters in the section before the AS keyword, separated by a comma. The syntax you would use is:

CREATE ProcedureName
(ParameterName1 DataType, ParameterName2 DataType, ParameterName_n DataType_n)
AS
Body of the Procedure

When calling a procedure that takes more than one parameter, you must still provide a value for each parameter but you have two alternatives. The simplest technique consists of providing a value for each parameter in the exact order they appear in the procedure.

Alternatively, you can provide the value for each parameter in the order of your choice. In this case, you must type the name of each parameter and assign it the corresponding value.

Deleting a Procedure

One of the strong characteristics of a stored procedure, as compared to functions in traditional languages, is that a stored procedure is treated like an object in its own right. Therefore, after creating it, if you don't need it anymore, you can get rid of it.

To visually delete a stored procedure, in the Navigation Pane, right-click its name and click Delete:

Deleting a Stored Procedure

You will be asked to confirm your decision or change your mind.

To programmatically delete a stored procedure, execute the DROP PROCEDURE statement. The formula to follow is:

DROP PROCEDURE ProcedureName

Of course, you should make sure you are in the right database and also that the ProcedureName exists. Here is an example:

Private Sub cmdDeleteProcedure_Click()
    Dim conEmployees As ADODB.Connection
    Dim strProcedure As String
    
    Set conEmployees = Application.CurrentProject.Connection
    strProcedure = "DROP PROCEDURE SetNewMinSalary;"
    
    conEmployees.Execute strProcedure
    
    MsgBox "The procedure has been deleted."
    conEmployees.Close

End Sub

Commands

Introduction

From what we know already, a SQL statement is an expression that is presented to the SQL interpreter that interprets it and returns a result. ADO proposes yet another way to formulate and execute such a statement. A command is an action that is created or formulated and submitted to the database. The action can be any of the types of SQL statements and queries we have used so far. That is, it can consist of creating a database or a table. It could also consist of selecting fields or restricting the list of values from a table.

Command Creation

To support command, the ADO library provides a class named Command. To start a command, declare a variable of type ADODB.Command and initialize it using the New operator. Here is an example:

Private Sub cmdVideosRatedR_Click()
    Dim cmdVideos As ADODB.Command
    
    Set cmdVideos = New ADODB.Command
    
End Sub

One of the characteristics of a command is the action it is supposed to perform. This is specified as a string and it is represented by the CommandText property of the Command object. It can be similar to any of the SQL statements we have used so far. Here is an example:

Private Sub cmdVideosRatedR_Click()
    Dim cmdVideos As ADODB.Command
    
    Set cmdVideos = New ADODB.Command
    
    cmdVideos.CommandText = "SELECT * FROM Videos WHERE Rating='R'"
End Sub

Alternatively, you can use the name of a table as the text of the command. The text of a command can also be the name of a stored procedure.

After setting the action, you should specify the type of command that will be performed. The types of commands are members of the CommandTypeEnum enumerator. If the command is text-based, such as a SQL statement, you can specify the type as adCmdText. Here is an example:

Private Sub cmdVideosRatedR_Click()
    Dim cmdVideos As ADODB.Command
    
    Set cmdVideos = New ADODB.Command
    
    cmdVideos.CommandText = "SELECT * FROM Videos WHERE Rating='R'"
    cmdVideos.CommandType = adCmdText
    
End Sub

If the text of the command is specified as the name of a table, you can set the command type to adCmdTable. If the text of the command is represented by the name of a stored procedure, then you can specify the command type as adCmdStoredProc.

After using a Command object, you should close it. To do this, you can call its Close() method.

Command Execution

After creating a Command object, to use its action, you must execute it. To start, you should specify what connection would be used to pass it. To support this, the Command class is equipped with a property named ActiveConnection. You can assign a Connection object to this property. Here is an example:

Private Sub cmdVideosRatedR_Click()
    Dim rstVideos As ADODB.Recordset
    Dim cmdVideos As ADODB.Command
    
    Set cmdVideos = New ADODB.Command
    
    cmdVideos.CommandText = "SELECT * FROM Videos WHERE Rating='R'"
    cmdVideos.CommandType = adCmdText
    cmdVideos.ActiveConnection = CurrentProject.Connection
     
    rstVideos.Close
    Set rstVideos = Nothing
    Set cmdVideos = Nothing
End Sub

To execute a command, you can use the Command itself, a Connection, or a Recordset objects. To execute a command using the Command object, you can call its Execute() method and assign it to a Recordset object. Here is an example:

Private Sub cmdVideosRatedR_Click()
    Dim rstVideos As ADODB.Recordset
    Dim cmdVideos As ADODB.Command
    
    Set cmdVideos = New ADODB.Command
    
    cmdVideos.CommandText = "SELECT * FROM Videos WHERE Rating='R'"
    cmdVideos.CommandType = adCmdText
    cmdVideos.ActiveConnection = CurrentProject.Connection
        
    Set rstVideos = cmdVideos.Execute
    
    rstVideos.Close
    Set rstVideos = Nothing
    Set cmdVideos = Nothing
End Sub

Alternatively, you can create a command as a text-based SQL statement and pass it as the Source argument to the Open() method of the Recordset.

Data Views

Introduction

We know that, after sorting or filtering records on a table, we could save the results of the table and the table would remember its previous state the next time it's opened. The most formal way to create and save sorting or filtering as an object is through a query. A query is saved as a regular object like a table or a form. The concept of queries is mostly used by Microsoft Access. Other database environments or libraries don't necessarily use queries as objects. For example, ADO allows you to create a list that is either sorted, filtered or both and save it as a formal object. This is referred to as a view.

A view is a technique of creating a list of records based on data from an existing table, a query, or another view.

View Creation

Microsoft Access doesn't have its own means of creating a view. Instead, you can create it as part of an ADO database. To create a view, you can start with the following SQL syntax:

CREATE VIEW ViewName
AS
SELECT Statement

The creation of a view starts with the CREATE VIEW expression followed by a name. The name of a view follows the rules and suggestions we have applied to other objects so far. After the name of the view, use the AS keyword to indicate that you are ready to define the view.

Because a view is like a query, it can be defined using a SELECT statement, following the same rules we applied for data sorting or filtering. Here is an example that creates a view:

Private Sub cmdCreateRegistration_Click()
	Dim conDatabase As ADODB.Connection
	Dim SQL As String

    	Set conDatabase = Application.CurrentProject.Connection
    
    	SQL = "CREATE VIEW StudentsIdentification " & _
              "AS SELECT FirstName, LastName FROM Students"

	conDatabase.Execute SQL
    
	conDatabase.Close
    	Set conDatabase = Nothing
End Sub

After creating the view, it is represented in the Navigation Pane where you can access it.

Using a View

After creating a view, you can use it as you would a query. For example, you can use it as the Record Source of a form. Here is an example:

Private Sub cmdApplyRegistration_Click()
    Me.RecordSource = "StudentsIdentification"
    Me.txtFirstName.ControlSource = "FirstName"
    Me.txtLastName.ControlSource = "LastName"
End Sub

You can also extract the values of a view using a SELECT statement. You can also display the value of a query in Datasheet View like a table or a query. To do this, call the OpenQuery() method of the DoCmd object.

A View With Alias Names

A view is a list of values from a table, an existing query, or another view. A view is not a procedure but it can be used as if it were one. As imitating a procedure, a view can include a type of argument. The word argument here only means that some values can be passed to a view but these values can be specified only when creating the view. They are not real arguments.

When creating a view, you can create placeholders for columns and pass them in the parentheses of the view. This would be done as follows:

CREATE VIEW StudentsIdentification([First Name], [Family Name])
. . .

If you use this technique, the names passed in the parentheses of the view are the captions that would be displayed in place of the columns of the view. This technique allows you to specify the strings of your choice for the columns. If you want a column header to display the actual name of the column, write it the same. Otherwise, you can use any string you want for the column. If the name is in one word, you can just type it. If the name includes various words, include them between an opening square bracket "[" and a closing square bracket "]".

After listing the necessary strings as the captions of columns, in your SELECT statement of the view, you must use the exact same number of columns as the number of arguments of the view. In fact, each column of your SELECT statement should correspond to an argument of the same order.

Here is an example:

CREATE VIEW CarIdentifier([Tag #], Manufacturer, [Type of Car], Available)
AS
SELECT TagNumber, Make, Model, Available FROM Cars

Because, as we stated already, a view is not a procedure and the values passed to the view are not real arguments, when executing the view, don't specify the names of arguments. Simply create a SELECT statement and specify the name of the view as the source.

Deleting a View

As mentioned earlier, after creating a view, it becomes a named object like a table, a query or a form. As such, it can be removed from a database.

To visually delete a view, in the Navigation Pane, right-click its name and click Delete.

To programmatically delete a view, use the DROP VIEW expression in the following formula:

DROP VIEW ViewName

On the right side of the DROP VIEW expression, enter the name of the undesired view and execute the statement. Here is an example:

Private Sub cmdAlterView_Click()
    Dim conDatabase As ADODB.Connection
    Dim SQL As String

    Set conDatabase = Application.CurrentProject.Connection
    
    SQL = "DROP VIEW StudentsIdentification"

    conDatabase.Execute SQL
    
    MsgBox "The RegistIdentification view has been deleted.", vbInformation
    
    conDatabase.Close
    Set conDatabase = Nothing
End Sub

You will not be warned before the interpreter deletes the view.