SQL Server/Office Integration: SQL Server Views with Access 2000

Add Access capabilities to your toolkit

With the release of Access 2000, the time is right to start using Access to develop solutions for SQL Server. SQL Server/Office Integration will cover SQL Server development for Access developers, particularly those using Access 2000. I'll occasionally expand the column's scope to consider broader issues that pertain to the interplay between Office 2000 and SQL Server.

With Access 2000, developers can use Access's rapid prototyping tools to take advantage of Access projects (a new file type) to build SQL Server solutions. (See "Defining Database Tables with Access Projects," page 45, for information on creating databases and tables within Access projects.) Let's look at some techniques for managing SQL Server views from Access projects.

Designing Views

Access developers who decide to add SQL Server to their toolkits need to learn some new terms. Views, which are virtual tables based on SQL code, identify objects that correspond roughly to stored queries. Views are similar to SELECT queries from Access but can't include the ORDER BY clause or the keyword INTO. (For a more detailed definition, see SQL by Design, "Views and Stored Procedures," September 1999.)

The Access project user interface (UI) offers a three-paned Query Designer Window for graphically defining views, as Screen 1 shows. The Show Table window is a graphical tool for selecting tables and previously defined views as data sources for a view. To open the Show Table window, right-click in the top pane and choose Show Table. Select the Views collection in the database window to prepare to open the Query Designer Window. Drag tables and views from the Show Table window to the top pane to add them as sources for a view.

Insert a view's field names into the first column of the grid you see in the middle pane in Screen 1. You use the middle pane to define aliases and designate search or grouping criteria. Click the Group By button on the Design View toolbar to group records for the operation of aggregate functions. Move your cursor over a toolbar button to see a title describing its purpose.

The bottom pane shows the SQL code underlying the view. As you edit this code, Access automatically updates the other two panes. Buttons on the toolbar let you toggle each pane's visibility. In some cases, such as views based on union queries or those using the OPENROWSET keyword, you must define a view with SQL code. Use the Verify SQL Syntax button on the toolbar to check your code before attempting to run a query.

Creating and Listing Views

In addition to supporting graphical design of views, Access 2000 supports programmatic design and enumeration of views through Visual Basic for Applications (VBA) and ADO. To create a view programmatically, first ensure that the Visual Basic Editor (VBE) window for your Access project includes a reference to the Microsoft ActiveX Data Objects 2.1 Library. This library contains Command and Connection objects. Assign the SQL code for the view to the Command object. The Connection object specifies the database in which the command operates.

After the object declarations, the code in Listing 1 assigns the current project's connection to the cnn1 pointer. Next, the code attempts to drop the previous version of the vewTitles view. If the view doesn't exist, the subprocedure ignores the resulting error. The listing shows how to create a view with an ADO Command object. Note that you must set the CommandText property for the object. Your code runs more efficiently when you also set the CommandType property for the Command object. The first On Error statement ignores any error and runs the next line of code. The second On Error statement disables the first On Error statement. Listing 1 uses the CommandText property to specify the SQL code for the view. Invoking the command's Execute method creates the view. The procedure terminates after freeing the resources for the connection pointer.

The OpenSchema method for a connection can enumerate views. As Listing 2 shows, you can use an argument of adSchemaTables to return a record set that includes records for views and other table types. The TABLE_TYPE field of the return set from the OpenSchema method is VIEW for all records that denote views. The TABLE_NAME field within those records identifies the name of a member in the Access project's Views collection. The sample code in Listing 2 takes advantage of these relationships to create a list of the project's views in the VBE Immediate window.

Using Views

Just as you can design SQL Server views in Access 2000 through its UI or with VBA, you can also open views either way. Double-clicking a view in the database window displays its datasheet, which enables all the classic Access tools for manipulating a query. Screen 2 displays a view opened from the Access project UI. The controls at the bottom of the window offer the usual functions for moving between records and adding records, plus a couple of new features. From left to right, the controls let you move to first record, move to previous record, move to next record, move to last record, and add a new record. The text box between the controls for the move-previous and move-next functions lets you type a record number and jump to that record in the virtual table. The last two controls are unique to Access projects. The control with an X cancels further data downloading for a view and returns control to Access. The final control lets you specify the maximum number of records that a view downloads. Enter a number in the default max records text box on the Advanced tab in the Tools, Options dialog box to set a default maximum record setting for all views. The maximum record setting on an individual view overrides the global default on the Advanced tab.

The Datasheet toolbar on the Access window offers additional controls for manipulating the view's appearance but not its contents. Two controls, labeled A to Z and Z to A, enable ascending and descending record sorts in a view's display. These controls sort on the column where the cursor rests. A Filter by Selection control filters the display to show only those records that match the currently selected value in a column. Clicking the Remove Filter control restores the view of all records in the virtual table. The control with a pair of binoculars on it lets you jump to the first or next record that contains a specific value. The New Object control especially underscores the power of interfacing Access and SQL Server technology—it automates the building of forms, reports, and data-access pages based on the view.

Opening a view programmatically with VBA is straightforward. When you use VBA to open a view, the Access project attempts to obtain all the records unless you program it to do otherwise. Enhance the basic model for opening a view by setting the MaxRecords property to limit the number of records downloaded.

The basic model for programmatically enumerating the records in a view involves two steps. The first step is to define a record set based on the view. Designate adCmdTable as the Options setting when opening the record set. The following code segment opens a record set based on a view named vewQtyByTitle. Notice that it references the current project's connection, telling the ADO interpreter to look in the current project for the view.

rst1.Open "vewQtyByTitle",    CurrentProject.Connection, _   , , adCmdTable

The second step for enumerating a view's records is as simple as looping through the records and printing to the Immediate window.

Listing 3 shows two procedures that programmatically open a view and use the MaxRecords property to limit the number of records returned. The code sample references a view, vewMyTemp, based on a table with 30,000 rows and 2 columns. The first procedure, sub CallPrint1000Milestones, passes a maximum number of records to extract (20,000 in this case) as it invokes the second procedure, sub Print1000-Milestones. The second procedure assigns the passed parameter value to the MaxRecords property for the record set that holds the return set from the view vewMyTemp. After opening the record set, the loop for printing the milestones processes the records until it reaches the record set's end of file (EOF) or its MaxRecords property setting, whichever is smaller. The procedure prints a milestone record to the Immediate window by counting the rows and using the Mod operator to detect each successive thousandth record. Because the passed parameter is 20,000, the procedure prints 20 milestone markers, even though the record set has 30,000 rows.

Knowing the techniques for designing and using SQL Server views inside of Access projects can help both Access and traditional SQL Server developers. First, these techniques show how Access developers can transfer many of their existing skills for working with SQL Server databases. Second, they demonstrate rapid application development features available with Access 2000 that traditional SQL Server developers might want to apply to shorten development cycles.