Beginning Access 2002 VBA (Programmer to Programmer)

Now that we have upsized the database, we will look at the major objects your queries will be changed to.

Views

Think of a view as a window into your data. Views allow you to create 'tables' on the fly, usually to permit users to have access to a subset of data. Views can have many advantages:

• Hide complex SQL from users

• Used to limit direct access to tables

• Reduce data returned from the server

• Group date from different table together

For example, consider the following T-SQL statement:

SELECT dbo.tblCompany.CompanyName, dbo.tblCompany.Address, dbo.tblCompany.City, dbo.tblCompany.State, dbo.tblCompany.ZipCode, dbo.tblSales.fkIceCreamID, dbo.tblSales.Quantity, dbo.tblSales.DateOrdered, dbo.tblSales.DateDispatched, dbo.tblSales.DatePaid FROM dbo.tblCompany INNER JOIN dbo.tblSales ON dbo.tblCompany.CompanyID = dbo.tblSales.fkCompanyID

Look at the next bit of SQL:

SELECT dbo.vw_sales.* FROM dbo.vw_sales

This returns the same records as the above SQL, but this time we are using the view that will be created in the next example. Note that we don't have to worry about the complex SQL this time. A simple SELECT statement is all that is required.

Note the use of the prefix dbo before each table name . dbo is the table owner. It is possible for different users to create objects with the same name. For example, user Martin could create a table called Customer; user Patricia could also create a table of the same name. When referencing the tables, you would prefix the table name with the table owner name, for example, Martin.Customer . When creating Stored Procedures in an Access 2002 ADP, your table will have the prefix of dbo; in this case, this is the system administrator. It is good practice to have all tables owned by dbo rather than have multiple table owners .

Try It Out-Creating a View

Microsoft Access 2002 uses the "Query Builder" to create views in ADPs. This is a very handy tool when working with SQL Server and a big advance over Access 2000. To create a view in the ADP:

1. Select Queries New.

2. In the dialog, select Design View.

3. Click OK to open the Query Window.

4. Creating the view is a simple matter of selecting the tables and indicating the fields required. Select tblcompany and tblSales and close the dialog.

5. Select the fields you would like from both tables (click the checkbox beside the field names ).

   Your window should look as follows . In order to view the SQL statement being generated select View Show Panes SQL Pane from the main menu, or click the SQL button on the toolbar.

   You must now save the view.

6. Execute the code by clicking Run in the main menu.

7. Save the view as vw_SalesExample in response to the prompt. You must save the view before you can execute it.

When you come to use the view, you simply treat it as a table in the normal way. However, there is one important difference; usually this view cannot be updated as it uses multiple tables, so you need to be careful when using a view as the record source for a form. However, a view that is based on a single table is updateable once you change some of its properties.

Try It Out-Updating Data via a View

1. For this example, create a view using Microsoft Access which selects all the fields from tblCompany . To select all fields from a table, right-click the mouse button and select All Columns from the context menu. Simply follow the instructions from the earlier example, only this time select a single table. Save the view as vw_Customer . Once you have created the view, run the form wizard to create a simple form based on vw_Customer . On this occasion, the data can be updated via the view as the view is based on a single table.

   In order to permit a normal user to edit data via a view-based form, it is important that you change one of the view properties. When working with views, the usual setup is to grant permission to the users on the view only, without giving permission to the underlying table. That being the case, the user has no permissions to work directly with the table data. SQL Server will therefore not permit any updates to the table. When you try to update the data via the form, SQL Server will attempt to update the table directly. If your user has no permissions on the table, the update fails. However, by changing one of the view properties, we can force Access to update the table via the view instead.

2. To see the property sheet for the view click Queries , then select the View in the Database window.

3. Open the view in Design View.

4. Select View Properties using the main menu.

5. Check the box beside Update using View Rules to enable this option.

6. Close the dialog and save the changes.

The view can now be used as the record source of a form to display company data. Any user with access to the view, but not the base table, will now be able to use the customer form based on vw_company , even though they do not have permission on the actual customer table on SQL Server.

Again, views provide you with access to data without giving users permissions to actually access tables directly. We know several developers who base all ADP applications on views rather than tables. One additional benefit to this is that the user does not even see the table in the database window. As far as they are concerned the table doesn't exist. So are there any problems with views? Of course there are:

• Views do not allow ORDER BY because a view is a virtual table, and tables, like views, cannot be ordered.

  However, the use of the SQL TOP command can circumvent this restriction. For example, the following is the syntax to add an ORDER BY to one of the views created earlier:

  SELECT TOP 100 PERCENT dbo.tblCompany.CompanyID, dbo.tblCompany.CompanyName, dbo.tblCompany.Address, dbo.tblCompany.City, dbo.tblSales.Quantity, dbo.tblSales.DateOrdered, dbo.tblSales.DateDispatched FROM dbo.tblCompany INNER JOIN dbo.tblSales ON dbo.tblCompany.CompanyID = dbo.tblSales.fkCompanyID ORDER BY dbo.tblCompany.CompanyID

• Views do not permit parameters.

• If you try to add a parameter to a view within the graphical designer, then SQL Server responds with an error. Try it out in the Access Interface. As we shall see, User-Defined Functions offer you similar functionality, which replaces the need for parameter views.

User-Defined Functions (UDFs)

SQL Server supports two types of UDFs - Scalar Functions and Inline Table Valued Functions. As usual with Access, there are a number of ways to design the function, but for Inline Functions, you can use the graphical Query Builder. SQL Server also provides you with hundreds of built-in functions. Below is a brief sample of the more useful functions:

In addition to standard functions, SQL Server also provides the following System Functions:

SQL Server Books online provides copious information on the hundreds of functions available in SQL Server 2000.

Functions are classified as either deterministic or non deterministic. A deterministic function will always return the same value when called. A non-deterministic function on the other hand returns a different value when called. For example, GetDate() will always return a different value when you call it, as it will always include the date and time portion of a date which is always different. This distinction is important when creating your own functions as there are restrictions placed on function use. For example:

• Scalar FunctionsScalar functions return a single data value (not a table) with a RETURNS clause. Scalar functions can use all scalar data types, with exception of timestamp and user-defined data types

• Inline FunctionsInline functions are much like a SQL Server view, only on this occasion you can pass parameters to the function. Again, Access permits you to create the function using the graphical interface. An Inline Function returns a data type in the form of a table and you can then reference the function in the SELECT statement of a SQL statement.

Try It Out-Creating an Inline Function

From the Database window:

1. Select Queries New.

2. Select Design In-Line Function from the dialog.

3. Add the Company and Sales tables, using the Add Table dialog. At this point (once you select the fields), your function should look like the following:

4. Close and save the function as udf_Customerspaid. The u prefix indicates that this is a user created function.

The function is executed just as you would a standard Access query. Simply double-click on it in the Database window, or select the function and click Run on the Access tool bar.

The function can be used in place of a table. For example, the following stored procedure uses the function output as if it were a standard table:

CREATE PROCEDURE dbo.usp_CustPaid (@ID int) AS SELECT CompanyID, CompanyName, Address, City, Quantity, DateOrdered FROM dbo.udf_Customerspaid() udf_Customerspaid WHERE (CompanyID = @ID)

In addition, the function can also be used in the JOIN clause of an SQL Statement.

Multi-Line User-Defined Functions

In this case you get no help from the Query Builder, and must enter the code by hand. Multi-Line UDFs allow you to build up very sophisticated functions, which, in this case, return a table data type that you can define yourself. Our next example returns the sales from two customers whose IDs are passed to the function. Shortly we shall see this function type applied to one of the queries that failed to upsize in our IceCream database.

Another form you will have to amend is frmSalesFigures - those data sources are set to the old Access query. Simply change the form and the combo box record sources to usp_qrysalessummary to fix the form and all will be well. What? It didn't work! Let's see why

Try It Out-Fixing the Combo using a UDF

The combo box fails to work because of the VBA code used in the OnChange event. We will not show the entire code here, just the relevant lines:

If cboCompany = -1 Then strSQL = "qryxSalesSummary" Else strSQL = "SELECT * FROM qryxSalesSummary WHERE CompanyName=""" & _ cboCompany.Column(1) & """" End If

Note that again we are using the old Access query as the source for strSQL in the initial If statement, otherwise we are running a SELECT statement filtered by column 1 of the combo box. Later we will use a stored procedure to replace the sales summary query. On this occasion we are going to write a UDF that returns a table to carry out the same function. The following is the text of the UDF:

CREATE Function dbo.SalesSummary () Returns @salessummary TABLE (CompanyName NvarChar(50), MonthName nvarchar (20), Quantity Int, MonthNumber Int) as Begin INSERT @salessummary SELECT dbo.tblCompany.CompanyName, DATENAME(MONTH, dbo.tblSales.DateOrdered) AS MonthName, SUM(dbo.tblSales.Quantity) AS Quantity, MONTH(dbo.tblSales.DateOrdered) AS MonthNumber FROM dbo.tblCompany INNER JOIN dbo.tblSales ON dbo.tblCompany.CompanyID = dbo.tblSales.fkCompanyID GROUP BY dbo.tblCompany.CompanyName, DATENAME(MONTH, dbo.tblSales.DateOrdered), MONTH(dbo.tblSales.DateOrdered) ORDER BY dbo.tblCompany.CompanyName, DATEPART(m, dbo.tblSales.DateOrdered) RETURN END

To create the function in the Access interface:

1. From the database window, click Queries New

2. Select Create Text Table Valued Function in the dialog

3. The function template opens with some boilerplate structure already in place:

4. The easiest way to proceed is to delete the template text and enter the function shown above.

5. Save and close the function accepting the default name which you have already defined in the CREATE statement.

6. Double-click the function name in the Database window to execute it.

The date functions used are discussed following the next example Stored Procedures .

Stored Procedures

Stored Procedures form the backbone of many SQL Server applications and are widely used to provide data access to the tables. Using stored procedures, we can grant access to the procedure while protecting our table structure. All interaction between the user and the data is via the stored procedures the user is given permission to use. Another advance in Access is the ability to build simple stored procedures via the user interface. However, for more complex procedures, those that contain additional logic, you are still required to write them by hand.

What is a Stored Procedure?

A stored procedure is a SQL query that is stored on the database server, a bit like an Access Query or QueryDef object. However, SQL Server stored procedures differ in many ways, not least the fact that using T-SQL we can build conditional logic into the SQL, using CASE , IFTHENELSE and other programming structures. The first time a stored procedure is executed, SQL Server creates an execution plan, which is the optimal way to actually retrieve your data or perform the function of the procedure. This plan remains in memory and is reused by the procedure on each execution.

The Query Builder

The actual window will have changed slightly, but the basic operation remains the same. The window is still divided into two panes, the top pane is used to display the tables, while the bottom pane is used to display the fields of output and any criteria or sorting you wish to apply. You may also add a SQL pane by selecting View Show Panes SQL using the main menu or by clicking the SQL button on the toolbar.

You may also notice that beside each field/column name there is a checkbox. To select a field, simply check the box. If you wish to select all the fields, check the first box *(All Columns) .

There are also several properties you can set for a stored procedure. From the main menu, select View Properties to open the Properties dialog for this stored procedure.

There are three tabs available to you on the dialog:

• Stored Procedure

• Stored Procedure Parameters

Stored Procedure Tab

Using this page, we can set some properties that affect the stored procedure and the way in which it returns data:

Output all Columns

Selecting this option will return all the columns from all the tables available in the query windows .

DISTINCT Values

Removes duplicate values form the output.

Set NOCOUNT on (Remember to set it back to OFF when you are done with the query).

Indicates whether SQL Server returns a count of the records returned by the stored procedure to the client. Setting NOCOUNT to ON , the count is not returned. For stored procedures which do not return data, this can lead to large performance improvements when NOCOUNT is set to ON .

• TOP is used to select a group of records, for example, the top 10 customers. If we also click the PERCENT box, we would then return the top 10% of customers.

• Description and Comment

  This is a free text field to allow you to add notes to the procedure.

Stored Procedure Parameters Tab

This tab is only available once you have added a parameter to the procedure. However, once you have added a parameter, it is added to this tab.

You can also provide a Default parameter, which will be used if there is no input to the procedure by the application or the user.

Data Tab

There are several properties available under the Data tab:

• Filter

  When data is returned to the client, the filter will be applied. This is a WHERE clause without the WHERE keyword.

• Order By

  A field or fields used to order the result set on the client.

• Subdatasheet Name

  The help file recommends setting this file to Auto ; however, many users have seen performance fall as a result when using this setting in Access 2000 and have agreed within the Microsoft Newsgroups that this setting should be place at None .

• Link Child Fields

  Shows the fields used to link the subdatasheet selected in the property above.

• Link Master Fields

  Related field in the subdatasheet.

• Subdatasheet Height

  Height of the subdatasheet.

• Subdatasheet Expanded

  Expands the subdatasheet when viewed in data view.

• Default View

  The default view for the subdatasheet.

As you can see there are several stored procedure properties that you can set within Query Builder itself. Let's move on and create a procedure in our ADP

Try It Out-Creating a Stored Procedure

Access 2002 provides the Query Builder, which can be used to graphically build stored procedures. We also see examples of stored procedures in Chapter 21 on ADO. In this section, we will look at working with the Graphical Builder in a bit more detail.

1. From the Database window, select Queries New.

2. Select Design Stored Procedure from the dialog.

   This opens the graphical stored procedure tools. This is one of the major advances Access 2002 has made. The ability to create stored procedures is similar to that we used to create Access Queries. The window you use is very similar to the Query Builder in Access. You still have the Add Table Dialog, only on this occasion you will find two new items, Views and Functions . You can also use these server objects when building a stored procedure.

3. Select tblIceCream and tblCreamIngredient and tblIngredient.

4. Select any fields in addition to tblIngredient.IngredientID as the additional fields are not important to this example.

5. In the Sort Type , select tblIceCream.IceCream as the sorting field.

6. In the criteria cell for the Price value enter = @IngID.

7. Close and save the procedure as usp_ingredients.

Once the stored procedure has been saved, it can then be used as the record source for forms and reports . However, unlike an Access Query, it cannot be used as the basis for another stored procedure. However, it is possible to call one stored procedure from within another. If you do need this functionality, then create a view or a UDF instead.

1. Finally we have got here. Execute the query using a value of 31 for the parameter. One record should be returned.