Team LiB
Previous Section Next Section

Books Online and Other SQL Server Learning Resources

After you open an Access project and create a new SQL Server database, you need to learn how to use them. This chapter, along with Chapter 12 and Chapter 13, gets you started by providing fundamental instruction and helpful code samples. Because the topic is monumental in scope, these chapters are meant to complement existing developer resources. Books Online is the definitive Microsoft-supplied SQL Server reference source. The sample database applications that Microsoft makes available, such as the NorthwindCS project and its matching database, are another great resource. The pubs database is another database sample, though it is less widely available to Access developers than NorthwindCS. The pubs database relates to SQL Server much as the Northwind database relates to Access.

Note 

Those who want to learn more about T-SQL may derive value from Programming Microsoft SQL Server 2000 with Microsoft Visual Basic .NET (Microsoft Press, 2002), which I also authored. This book presents numerous T-SQL programming samples for typical developer tasks.

Getting Books Online

Books Online is a valuable documentation resource that discusses SQL Server administrative issues and T-SQL code samples. T-SQL code samples in Books Online primarily target Query Analyzer, one of the SQL Server Client Tools that ships with all commercial editions of SQL Server 2000. Neither Query Analyzer nor Books Online ship with MSDE 2000. In addition, most editions of Office 2003 will not have the SQL Client Tools either. However, both of these resources plus other resources, such as the pubs database mentioned below, ship with the SQL Client Tools. One edition of Office 2003 will have the SQL Client Tools through the SQL Server 2000 Developer Edition. I highly recommend this edition for any Access developer doing a significant amount of work with SQL Server 2000.

If you do not have ADE, you can still use Books Online. Be aware that there is a distinct version of Books Online for each version of SQL Server. All the Books Online samples apply to MSDE 2000 and SQL Server 2000. The SQL Server 2000 version of Books Online is available for download from the Microsoft Web site http://www.microsoft.com/SQL/techinfo/productdoc/2000/books.asp. The ADE also includes Books Online as part of its client tools for SQL Server.

Adapting Books Online Code for the SQL Pane

The following excerpt is the first T-SQL code sample (titled example A) for the CREATE VIEW statement in the SQL Server 2000 version of Books Online. The sample works perfectly from Query Analyzer, but it fails when you copy the code into the SQL pane of a view member in the Views collection of an Access project. (Later in the chapter, in "SQL Server Views," I will explain how to work with the SQL pane in an Access project.) When you click the Run button after copying the code into the SQL pane and accept the prompt to save T-SQL, Access can report a succession of errors. The first of these errors results from the fact that the SQL Server view template in Access does not support the USE keyword. The fundamental issue is that an Access project is a different kind of SQL Server client than Query Analyzer.

USE pubs
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
     WHERE TABLE_NAME = 'titles_view')
     DROP VIEW titles_view
GO
CREATE VIEW titles_view
AS 
SELECT title, type, price, pubdate
FROM titles
GO

The sample performs two tasks that can cause errors. First, the code deletes any existing view in the database with the identifier "titles_view". Second, it invokes the CREATE VIEW statement, which Access views do not support. There's no need to conditionally execute a DROP VIEW statement or unconditionally execute a CREATE VIEW statement from an Access project because the File, Save As and File, Save commands handle these tasks automatically. In addition, view templates in Access projects do not process the USE and GO keywords that Query Analyzer needs for many code samples.

To make the preceding sample work from a view in an Access project, simply copy two lines from the sample into the view's SQL pane: the SELECT statement after the AS keyword, and the SELECT statement before the trailing GO keyword. If you're running this sample from an Access project that doesn't link to the pubs database, such as Chapter11.adp, you can still reference the titles table as the source for the query. When the pubs database resides on the server, you can specify the source for the query as pubs..titles. The first name represents the database. This database name qualifier replaces the need for the USE statement. The last name represents the table within the database. The complete syntax for the view follows:

SELECT title, type, price, pubdate
FROM pubs..titles

The syntax for specifying the source omits an intervening name for the database owner, which is implicitly dbo. As long as a user doesn't create a table named titles in the pubs database, it's acceptable to omit the owner when specifying a database source owned by the dbo. If a user does create a table named titles, it is necessary to explicitly introduce an intervening owner name (such as dbo) to reference the table.

Note 

The database owner, or dbo, can be any member of the sysadmin group. This special security group of logins has broad authority in any database on a server, including the ability to create objects, such as tables. As long as a user connects to a database with one of the special logins in the sysadmin group, any objects he or she creates during a session will have dbo as their owner. If a user logs on with a login that's not in the sysadmin group, that object will always require an explicit owner name that refers to the login whenever anyone other than the object's owner refers to it. Database owners are a security topic that we'll cover in more detail in Chapter 13.

If the pubs database is not on the server for the current project, you can still reference the table. However, you'll have to reference the server name explicitly. SQL Server offers a couple of different approaches to this, which I'll cover later in this chapter in "SQL Server Views."

Adapting Books Online Code for a VBA Project

You can run the previous Books Online sample in the code window of a VBA project pretty much as is. The following sample shows an adaptation of the immediately preceding sample that works from a VBA code window. As you can see, this sample uses a lot more of the original Books Online sample. But it still has some changes. For example, the GO keyword after the DROP VIEW statement is commented out. Leaving it in would generate an error. The USE statement is removed so that the new view enters the database for the current project instead of the pubs database. Although the sample excludes the GO keywords, it follows their batching conventions for lines of T-SQL code. This is essential for the CREATE VIEW statement to succeed.

Sub CreateTitleViewInCurrentProject() 
Dim cnn1 As ADODB.Connection
Dim str1 As String
   
'Point the Connection object at the current project
Set cnn1 = CurrentProject.Connection
   
'The code purposely leaves out the USE statement to create the
'view in the database for the current project, and it comments
'out the GO statement
str1 = "" & _
    "IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS " & _
    "WHERE TABLE_NAME = 'titles_view') " & _
    "DROP VIEW titles_view " & _
    "--GO "
cnn1.Execute str1
 
'The code breaks here so that the CREATE VIEW statement is the first
'statement in its batch; comment prefix removes GO statement
str1 = "CREATE VIEW titles_view " & _
    "AS " & _
    "SELECT title, type, price, pubdate " & _
    "FROM pubs..titles " & _
    "--GO "
cnn1.Execute str1
   
End Sub

By the conclusion of the sample, your current project has a new view with an identifier of titles_view. This new object might not appear immediately in the Database window. In this case, select Queries from the Object bar in the Database window, and choose View, Refresh. Alternatively, you can write a pair of short VBA procedures that loop through the members of the Views collection and print their names to the Immediate window (see the next code sample). The members will include one named title_view. The procedure uses SQL-DMO objects, so your VBA project needs a reference to the Microsoft SQLDMO Object Library. The VBA project for the Chapter11.adp file has this reference already. The following pair of procedures demonstrates the syntax for looping through the members of the Views collection. This sample assumes your sa user has a password equal to the string password. You might need to update the procedure if the password is different.

Sub CallPrintViewsInADBOnLocalServer() 
Dim DBName As String
   
DBName = "Chapter11SQL"
PrintViewsInADBOnLocalServer DBName
   
End Sub
   
'A utility routine for printing views in a database
'on the local server
Sub PrintViewsInADBOnLocalServer(DBName)
Dim srv1 As New SQLDMO.SQLServer
   
srv1.Connect "(local)", "sa", "password"
   
For Each vew1 In srv1.Databases(DBName).Views
    Debug.Print vew1.Name
Next vew1
   
End Sub

Sample Databases

The NorthwindCS and pubs databases are the two most readily available databases that you can use as models for your custom application development. The NorthwindCS database ships as part of Office 2003. Office 2003 also includes a NorthwindCS Access project that links to the NorthwindCS database. As mentioned earlier in this chapter, the NorthwindCS Access project includes a script for automatically loading the NorthwindCS database to a local server if the Northwind database isn't already installed. Otherwise, the script connects to the SQL Server Northwind database if it's available locally. The script for managing the first-time startup of the NorthwindCS project resides in its Startup module.

Working with the NorthwindCS Database

The NorthwindCS database is so attractive as a resource because nearly all Access developers have some familiarity with the Northwind database. The NorthwindCS Access project offers the same functionality as the Northwind Access database file. Therefore, you can use the NorthwindCS Access project as a model for performing any task in SQL Server that the Northwind Access database file performs for Jet databases. For example, the Ten Most Expensive Products stored procedure selects just the top 10 products in terms of unit price and lists them in descending order. The T-SQL script for this stored procedure follows:

ALTER PROCEDURE [Ten Most Expensive Products] 
AS 
SET ROWCOUNT 10 
SELECT Products.ProductName AS TenMostExpensiveProducts, 
Products.UnitPrice 
FROM Products 
ORDER BY Products.UnitPrice DESC
Note 

The preceding script is edited slightly (for ease of reading) from the version that appears when you open the stored procedure by selecting it in the Database window and clicking Design. The script initially appears as one long line because Microsoft does not ship the script with built-in carriage returns. As you add the carriage returns to improve readability, remember to leave a blank space after all keywords. Then save and reopen the stored procedure with the revised format.

The syntax for this stored procedure is different than the corresponding query in the Access Northwind database file. That sample uses the TOP clause to moderate the behavior of the SELECT statement. However, this sample achieves the same result with a SET ROWCOUNT statement. This statement causes SQL Server to halt the processing of a SELECT statement after it returns a specified number of records—10, in the case of the sample script. The ORDER BY clause arranges those rows in descending order based on UnitPrice.

If you're a SQL Server DBA or developer, you might be confused about why the script begins with an ALTER PROCEDURE statement instead of a CREATE PROCEDURE statement (as is common in the Books Online samples). When you design a stored procedure within an Access project's stored procedure template, you use CREATE PROCEDURE the very first time that you input and save the stored procedure. Thereafter, Access automatically changes the CREATE PROCEDURE statement to an ALTER PROCEDURE statement. This prevents you from having to drop the old stored procedure before re-creating a new one with the changes you make in Design view. In fact, Access projects do not permit you to drop and then re-create stored procedures from their stored procedure templates.

When you work with a stored procedure based on a single SELECT statement, Access permits you to create the query statement in its graphical Design view. This relieves you of some syntax requirements otherwise associated with specifying stored procedures. The following code comes from the SQL pane of the Design view for a stored procedure. The stored procedure's T-SQL representation appears without ALTER PROCEDURE, AS, or parameter declarations. To me, the most exciting aspect of the script is that it demonstrates the use of parameters without the bother of explicitly declaring them. The @Beginning_Date and @Ending_Date parameters control the range of orders about which the stored procedure returns records. Notice also that the syntax uses a three-part naming convention for the identifying fields. The field identifiers start with a reference to the owner (dbo), move on to specifying the source table, and conclude with the column name.

SELECT dbo.Orders.ShippedDate, dbo.Orders.OrderID, 
    dbo.[Order Subtotals].Subtotal,
    DATENAME(yy, dbo.Orders.ShippedDate) 
    AS Year
FROM dbo.Orders INNER JOIN dbo.[Order Subtotals]
    ON dbo.Orders.OrderID = dbo.[Order Subtotals].OrderID
WHERE (dbo.Orders.ShippedDate IS NOT NULL) 
    AND (dbo.Orders.ShippedDate BETWEEN @Beginning_Date AND @Ending_Date)

The Access 2003 innovations for stored procedures based on a single SELECT statement are even better. Now you can design them with a graphical query designer—even when they contain parameters. Figure 11-6 depicts the graphical view of the Sales by Year stored procedure, represented in the preceding code sample from the NorthwindCS database. Notice that the code enables a join and the specification of parameters.

Click To expand
Figure 11.6: A graphical view of the Sales by Year stored procedure in the NorthwindCS database.

The graphical view in Figure 11-6 is the default for stored procedures based on a single SELECT statement. You can open a SQL pane in the query designer or open the stored procedure in SQL View. Using SQL View shows the syntax of the ALTER PROCEDURE statement, including its AS keyword. This view is necessary when a stored procedure relies on more than a single SELECT statement.

Working with the Pubs Database

Many Access developers will be less familiar with the pubs database than with the NorthwindCS database, a look-alike of the Northwind Access database file. Nevertheless, the pubs database is the source for many samples in Books Online. For this reason, Access developers can boost their learning of SQL Server by getting comfortable with the pubs database. SQL Server DBAs and developers will likely have firsthand experience working with the pubs database.

No matter what your background, you need a version of SQL Server other than MSDE 2000 to work with the pubs database. This is because the database does not ship with MSDE 2000. Office developers planning to do extensive SQL Server work should consider getting ADE because this version ships with a Developer Edition of SQL Server 2000. For this reason, ADE offers an opportunity to readily gain experience with pubs database samples.

One way to get started with pubs is to open an Access project for an existing database and then point that project at the pubs database on a server. After opening the Access project, you can examine the members of its Tables and Queries classes. Figure 11-7 displays the Database window and the Data Link Properties dialog box for an Access project connected to the pubs database. The Access project file has the name Forpubs.adp and connects to the local SQL Server. Therefore, for this Access project to open properly you must have the pubs database on your local SQL Server default instance. The Database window shows the names of the view and four stored procedures that ship as part of the pubs sample. By clicking Tables in the Objects bar for the Database window, you can display the names of all the tables in the pubs database. All the other object classes for the Access project will be empty because the pubs database does not ship with a database diagram, and the project has no client-side objects such as forms and reports.

Click To expand
Figure 11.7: The Data Link Properties dialog box and Database window for an Access project connected to the pubs database.

One of the best ways to learn about a database is through a database diagram that indicates all the tables in the database and their relationships with other tables. You can create such a diagram for the pubs database by selecting Database Diagrams in the Objects bar and then clicking New on the Database window. This opens the Add Table dialog box. Successively click the Add button on the dialog box until all the tables appear in the diagram, and then close the Add Table dialog box. As an option, you can drag and rearrange the icons representing tables to improve the ease of viewing all tables in one database diagram display.

Figure 11-8 shows a diagram for the pubs database after it's been saved with the name CustomizedDiagram. Notice that it includes the database's tables as well as the columns within them. In addition, the diagram also shows the relationships between tables. The line connecting the stores and sales tables indicates that any one store row can have many corresponding sales rows. The authors and titles tables have a many-to-many relationship. The titleauthor table serves as the junction between these two tables. The publishers and pub_info tables have a one-to-one relationship. As you gain familiarity with the database diagram UI, you can probe relationships and table definitions. We'll discuss database diagrams in more detail in the next section. These diagrams provide a way to graphically perform data definition for tables. Database diagrams offer a means of circumventing programming tasks that do not require highly customized code development. This reserves the programming resources available to an assignment, saving them for the most demanding development tasks.

Click To expand
Figure 11.8: A database diagram for the pubs database.

The following code sample comes from example B for the AVG function in Books Online. The Books Online example, which is for the pubs database, demonstrates the behavior of the AVG and SUM aggregate functions with a GROUP BY clause. The code computes the average advance and the total advance by type of book.

USE pubs

SELECT type, AVG(advance), SUM(ytd_sales)
FROM titles
GROUP BY type
ORDER BY type

Copying the sample to the SQL Server database can result in a change or two. First, I copied the T-SQL script to a new stored procedure template. Next, I edited the code so that it would reference the pubs database even while running from a stored procedure in another database, such as Chapter11SQL. I could have copied the sample directly into the Forpubs Access project, in which case it would run exactly as is, except for removing the USE statement. In this instance, I wanted to provide as much code as possible in the chapter's Access project.

Figure 11-9 shows the edited T-SQL script in a stored procedure template. In addition to the modification to the FROM clause argument, the sample starts with an ALTER PROCEDURE keyword followed by the procedure's name and the AS keyword to signal the start of the procedure's T-SQL. The result set from the stored procedure's SELECT statement appears in the window on the right. You can flip to this view by selecting Datasheet View for the stored procedure.

Click To expand
Figure 11.9: An adapted T-SQL sample (along with its return values) from Books Online for the pubs database in an Access project.

Team LiB
Previous Section Next Section