|
|
SQL Server views are a subset of the traditional Access queries that you know from Access database files and the T-SQL samples that you find in Books Online. A view is an object in a SQL Server database. However, SQL Server does not save the view as a table of data values. Instead, SQL Server stores a T-SQL SELECT statement and related properties that define the view. The purpose of a view is to act as a virtual table. Once you understand the purpose of a view in a database, some of its apparent limitations (when compared to other database objects and T-SQL statements) generally seem reasonable.
At its core, a SQL Server view is a single SELECT statement. The result set returned by this statement is the view's virtual table. The SELECT statement can include all the standard clauses of a typical SELECT statement except the ORDER BY clause. (The ORDER BY clause is permissible, but you must also include the TOP clause in the SELECT statement so that SQL Server can interpret the view and return a result set.) Although a view does permit a WHERE clause, its arguments cannot be parameters. In addition, you cannot include multiple SELECT statements in a view, and a view cannot process INSERT, UPDATE, and DELETE statements.
These limitations of views stem from their basic purpose—to represent a single, virtual table in T-SQL code. When you need to accomplish this, the view is an able tool. In addition, Access projects offer a Query Designer for constructing and editing views that resembles the Query Designer available in Access database files for stored queries. The Access project Query Designer can make designing views fast. Furthermore, you can use this tool to teach yourself T-SQL syntax by graphically designing views and then examining the T-SQL underlying them in the Query Designer's SQL pane.
This section uses the Orders table and the Shippers table, which are based on the corresponding tables in the NorthwindCS database. As mentioned earlier in the chapter, if you don't have the NorthwindCS database on your server, you probably have the Northwind database. Recall that the NorthwindCS sample Access project automatically installs the NorthwindCS database on the local server the first time you open the project if a copy of the Northwind database doesn't already exist on the local server.
The following two procedures show the code for creating the tables and populating them with values. Each procedure handles the creation and data population of one table. The procedures start by conditionally removing a prior version of the table (if one exists). Next, the procedures invoke a CREATE TABLE statement to define the table structure. After executing the CREATE TABLE statement, the database for the current project will have a new empty table. Then the procedures run a stored procedure that copies data from the NorthwindCS Orders table or the Shippers table to the local table of the same name. I discuss these stored procedures later in this chapter so that you can modify them as needed.
Sub CreateAndPopulateOrdersTable() Dim str1 As String Dim cnn1 As ADODB.Connection Dim TableName As String 'Point a Connection object at the current project Set cnn1 = CurrentProject.Connection 'Delete the table if it exists already TableName = "Orders" Drop_a_table cnn1, TableName 'Create the table str1 = "CREATE TABLE Orders " & _ "( " & _ "OrderID int IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED, " & _ "OrderDate datetime NULL , " & _ "ShipVia int NULL , " & _ "Freight money NULL " & _ ")" cnn1.Execute str1 'Run custom stored procedure to populate table based 'on NorthwindCS database str1 = "EXEC Copy_from_NorthwindCS_Orders" cnn1.Execute str1 'Refresh Database window to show new table RefreshDatabaseWindow End Sub Sub CreateAndPopulateShippersTable() Dim str1 As String Dim cnn1 As ADODB.Connection Dim TableName As String 'Point a Connection object at the current project Set cnn1 = CurrentProject.Connection 'Delete the table if it exists already TableName = "Shippers" Drop_a_table cnn1, TableName 'Create the table str1 = "CREATE TABLE Shippers " & _ "( " & _ "ShipperID int IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED, " & _ "CompanyName varchar(40) NOT NULL , " & _ "Phone varchar(24) NULL " & _ ")" cnn1.Execute str1 'Run custom stored procedure to populate table based 'on NorthwindCS database str1 = "EXEC Copy_from_NorthwindCS_Shippers" cnn1.Execute str1 'Refresh Database window to show new table RefreshDatabaseWindow End Sub
You can launch the Query Designer by selecting Queries in the Objects bar and clicking New. This opens the New Query dialog box. Select Design View, and click OK. Next, you will see the Show Table dialog box. You can choose from previously defined tables, views, and user-defined functions. The dialog box contains a tab for each of these objects. You can select objects from these tabs and click Add to include them as record sources for your view. When you are done, click Close to close the Show Table dialog box. Alternatively, you can just click Close. This frees you to write T-SQL code directly into the SQL pane without having to use the graphical UI.
Figure 11-14 shows the graphical UI and code specifications for a view. I added the Orders table to this view, which, as you can see, offers three panes. The toolbar buttons let you independently show or hide each of these panes in Design View. The top pane, which has an Orders table icon, is the Diagram pane. This pane can depict any joins between tables that serve as the record source for a view. In this case, just one table serves as the record source. You can add table columns from the Diagram pane to a view by selecting the check box next to the column you want.
The Query Designer automatically adds the column name for a checked box to the middle panel, called the Grid pane. The Grid pane contains a row for each column in the view's virtual table. Note that you can designate the order in which the view sorts its records. Figure 11-14 shows the Freight column being sorted in descending order. Because the view has only one sort key, its Sort Order column has a value of 1 and the Sort Order values for the other columns are blank.
The bottom pane is the SQL pane. This pane does not open by default, but you can manually open it with the SQL button on the Design view toolbar. The Query Designer automatically populates this pane with code when you commit changes to either of the other two panes. In addition, you can type T-SQL code into the SQL pane, and the Query Designer will revise the other two panes accordingly. Type your T-SQL syntax directly into this pane to avoid having to work with the graphical UI for the Query Designer.
When you're ready to examine the result set from a view, you must save the view before looking at the rows that it returns. The view depicted in Figure 11-14 appears in the sample database for this chapter as Orders_sorted_by_Freight. Whenever you edit a view's design, you must resave the view before you can see its result set. Click Run or choose Datasheet View from the View drop-down menu on the toolbar to examine the result set. If you haven't saved the view since the last change, Access automatically prompts you to save it. If you're examining a view's result set for the first time, you must assign it a name. Access prompts with a name such as View1, but you can override this default name with one that is meaningful for your application.
SQL Server stores datetime and smalldatetime data type values internally in a format similar to that used for Access database files. However, SQL Server normally displays dates in a string format, such as Varchar or Char. Indeed, you can filter SQL Server fields with dates by using a string. Alternatively, you can also use various functions to help specify filtering criteria. The DATEPART function is particularly useful for this purpose.
The following T-SQL statement shows the syntax for using a string filter employed in the Orders_in_1996_1 view. The statement specifies the return of rows that are less than or equal to the last day in 1996. The statement is from the SQL pane of the Query Designer. You do not necessarily need the dbo quali