HandsOn Creating a View Based on a Table

^^ Note: The Acc2003_Chap23.mdb file contains the tables from the Northwind database that will be used in this chapter's exercises.

1. Open the Acc2003_Chap23.mdb file from the book's downloadable files, or create this file from scratch using the Microsoft Office Access user interface. (Be sure to import the Employees and Orders tables from Northwind.mdb.)

2. Switch to the Visual Basic Editor window and insert a new module.

3. In the module's Code window, enter the Create_View procedure as shown below.

' Don't forget to set up a reference to the ' Microsoft ActiveX Data Objects Library ' in the References dialog box

Sub Create_View()

Dim conn As ADODB.Connection

Set conn = CurrentProject.Connection

On Error GoTo ErrorHandler conn.Execute "CREATE VIEW vw_Employees AS " & _

"SELECT Employees.EmployeeId as [Employee Id], " &_ "FirstName & chr(32) & LastName as [Full Name], " & _ "Title, ReportsTo, Orders.OrderId as [Order Id] " & _ "FROM Employees " & _ "INNER JOIN Orders ON " & _ "Orders.EmployeeId = Employees.EmployeeId;"

Application.RefreshDatabaseWindow ExitHere:

If Not conn Is Nothing Then

If conn.State = adStateOpen Then conn.Close End If

Set conn = Nothing Exit Sub ErrorHandler:

If Err.Number = -2147217900 Then conn.Execute "DROP VIEW vw_Employees" Resume

Programming with the Jet Data Definition Language

Else

MsgBox Err.Number & ":" & Err.Description Resume ExitHere End If End Sub

The procedure above creates a view named vw_Employees. If the view already exists, it will be deleted using the DROP VIEW statement. The Chr(32) statement will insert a space between the first and last name.

Views are visible in Microsoft Access 2003 after selecting the Queries object in the left pane of the Database window. Notice that views don't differ much from a saved query. When you open the view created by the Cre-ate_View procedure in Design view, you will notice that this view is simply a Select query. Because the query defined by the SELECT statement is updatable, the vw_Employees view is also updatable. If the query was not updatable, the view would be read-only.

Views cannot contain the ORDER BY clause. To return the records in a specific order, you might want to use the view in a stored procedure, as discussed later in this chapter.

¿P vw Employees : Select Query

I SELECT Empties .ErnpkweelD, FiitHwne & 1 • &Lastr&rne A3 Fulhlame, Emplove«-Title, Emdff/eeü.RtportsTOj orders. Ord&ID

FROH ErrcJü/eei INNER JOIN Ordrss ON Erodavees ,&rtVü/ee]D = Orders. EmployefcID;

Figure 23-1: The statement used to select records for the view (see Hands-On 23-1) is shown here in the SQL view window.

0 0

Post a comment