Data Views

Introduction

In previous lessons, we saw 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 necessary 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.

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 is 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 internally available to all objects of your database like a query but, because Microsoft Access doesn't have a Views section, you cannot see the view in the Database window.

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 Arguments

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.

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 delete a view, use the DROP VIEW expression in the following syntax:

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.