Creating a View

If you want users to view and update data in a table or set of tables, but you do not want them to open the underlying tables directly, you can create a view. An SQL view is like a virtual table. Similar to an Access Select query, a view can display data from one or more tables. Instead of providing all the available data in your tables, you decide exactly what fields you'd like to include for viewing.

To create a view, use a SELECT statement to select the columns you want to include in a view. Next, associate the SELECT statement with a CREATE VIEW statement. The syntax looks like this:

CREATE VIEW viewName [(columnNames)] AS

SELECT (columnNames) FROM tableName;

Views must have unique names in the database. The name of the View cannot be the same as the name of an existing table. Specifying the names of columns following the name of the view is optional (note the square brackets in the syntax above). Column names must be specified in the SELECT statement. Use the asterisk (*) to select all columns.

Let's put more meaning into the above syntax. The example statement below creates a view that lists only orders with a Freight amount less than $20.

Programming with the Jet Data Definition Language

CREATE VIEW cheapFreight AS

SELECT Orders.OrderID, Orders.Freight, Orders.ShipCountry FROM Orders

WHERE Orders.Freight < 20;

The SELECT statement that defines the view cannot contain any parameters and cannot be typed directly in the SQL pane of the Query window. It must be used through the ADO's Execute method after establishing connection to a database, as illustrated below:

Sub Create_View_CheapFreight() Dim conn As ADODB.Connection Set conn = CurrentProject.Connection conn.Execute "CREATE VIEW CheapFreight AS " & _

"SELECT Orders.OrderID, Orders.Freight, " &_ "Orders.ShipCountry " & _ "FROM Orders WHERE Orders.Freight < 20;" Application.RefreshDatabaseWindow conn.Close Set conn = Nothing End Sub

The Application.RefreshDatabaseWindow statement ensures that after the view is created it is immediately listed in the Queries pane of the Database window. If you omit this statement, you will need to refresh the Database window manually by pressing F5 or choosing View | Refresh.

A view can be used as if it were a table. To return data from the CheapFreight view, double-click its name, or perform the following steps:

1. In the Access Database window, click the Queries object button.

2. Click the New button to display the New Query dialog box.

3. Choose Design View and click OK.

4. Click Close in the Choose Table dialog box.

6. Type the following statement: SELECT * FROM CheapFreight;

After performing the above steps, the Query window displays all the records as returned from the CheapFreight view. Remember that a view never stores any data; it simply returns the data as stated in the SELECT statement used in the view definition.

Because a view is like a Select query, you can use the OpenQuery method of the Access DoCmd object to open it from your VBA code:

Sub OpenView()

DoCmd.OpenQuery "CheapFreight", acViewNormal End Sub

Part III

The OpenQuery method is used to carry out the OpenQuery action in Visual Basic.

To get working experience with the views, let's proceed to the hands-on section. We will start by creating a view called vw_Employees. This view is based on the Employees and Orders tables, and contains five columns (Employee Id, Full Name, Title, ReportsTo, and Order Id).

0 0

Post a comment