Extend Microsoft Access Applications to the Cloud: Creating Data Sources by Using Queries

Adding parameters to queries

Parameters enable you to create a query that has a placeholder that can be populated at runtime to filter the data. Unlike a desktop database, where a user can directly open a query (or an object using the query) and enter a value for the parameter, in a web app you need to provide the values for any parameters before opening a view that has been constructed to use the parameterized query.

You start by creating a query from the advanced menu. For this example, select all fields from the Customers table, select the City field, and clear the Show check box for this field. Then click on the Parameters icon on the design ribbon, select a name for your parameter (CityFilter), and specify an appropriate data type (Short Text), as shown in Figure 6-9. Click OK to dismiss the Query Parameters dialog when you finish defining the new parameter.

As you start to type the parameter name in the criteria below the City field, IntelliSense will assist in selecting the parameter. Once you are done, the parameter name is shown in square brackets, as you can see in Figure 6-10.

If you look at this using SQL Server Management Studio (SSMS) in the Azure SQL Database, you would find that your query is saved using a special type of function called a table-valued function. This is shown in Figure 6-11. (I deleted some fields in the select statement to make this clearer.)

After creating the parameterized query, you then construct a View that uses the parameterized query as a record source, as shown in Figure 6-12. However, you will not be able to directly open the view from the table selector, because we must use the OpenPopup macro command to supply a value for the parameter. This means you should create this as a standalone view using the Advanced Menu. Choose to create a datasheet view.

Figure 6-13 shows the Field List on the right being used to select the fields ID, CustomerID, CompanyName, Region, and PostalCode to display on the datasheet. The view has been saved with the name CustomersDatasheetForCity.

You have now created a parameterized query and associated view. Next, you need to provide a point from which to display the data. Normally, a blank form with selection controls is a good choice of starting point, but here you will add a button to the Customers datasheet. Clicking the button displays your view, which shows customers in the same city for the currently displayed customer.

Figure 6-14 shows a button on the Customers datasheet that will be used to open the new parameterized view.

By clicking the On Click event for the button, you can then add the OnPopup macro action and select your CustomersDatasheetForCity view. As shown in Figure 6-15, a great feature of a parameterized view when combined with the OpenPopup action is that it automatically identifies the parameters and displays a list of the parameters on the macro design surface, which can then be set to reference a control or field on the view. In our example, I refer to the control called [CityTextBox].

In Figure 6-16, you can see the related records displayed in the browser window.

Looking back at Figure 6-15, you’ll notice that the OpenPopup macro action also supports a Where argument for filtering. This can be used either as an alternative to using a parameterized query or to provide additional filtering. Figure 6-17 shows the equivalent macro argument expression used to filter by city without using a parameterized query.

It is up to you whether you want to use parameterized queries or where clauses. By choosing the where clause approach, you can use the target view in several parts of an application, by using different where clauses. The parameterized query is fixed in terms of the parameters. But the parameterized query can seem simpler to work with, because it automatically detects the parameters and provides separate options for each parameter.

In the Customers table, you assume that all customers have a city, but sometimes a customer also has a region. The question is how you can make your parameters conditional so that there is additional filtering by region when the customer has a value for the Region field. You want to construct optional parameter filtering.

The choice of whether to use a parameterized query to solve this or a where clause depends on how confident you are at writing where clauses, and how much you prefer to have popup boxes and graphical assistance. You should also consider, if you start to add more conditional logic, which method would be simpler for you to remember and understand.

The where clause approach with the OpenPopup macro action is shown in Figure 6-18 and uses the where clause:

[Customers].[City]=[CityTextBox] And Coalesce([Customers].[Region],'')=
Coalesce([RegionTextBox],[Customers].[Region],'')

Remember that the COALESCE function returns the first non-null value. So if a for a customer record you have a value in [RegionTextBox], this is compared against Coalesce([Customers].[Region],''), which will either match the region value or return an empty string that will not match the region value.

If the current record has no value or NULL in [RegionTextBox], the expression Coalesce([RegionTextBox],[Customers].[Region],'') returns either the region value [Customers].[Region] or an empty string. This is equivalent to comparing [Customers].[Region]= [Customers].[Region] or ” ”.

You might be wondering why you create the comparison '' =''. The reason is that you cannot allow the comparison NULL = NULL, because this expression is never true in a database or web app and would not match any records.

Before I show how to create a parameterized query approach to this problem, you need to understand that you are not allowed to have a parameter that can have a value of NULL, because Variant type parameters are not supported. This means that in a later step you will force the values provided to your parameterized query to have a safe value, such as an empty string, when the value is NULL.

Figure 6-19 shows a parameter query with two parameters, [CityFilter] and [RegionFilter], which are both Short Text data types. The [CityFilter] has already been explained earlier in this section. For the region filter, you create a column expression Coalesce([Region],''). (Remember you can’t compare NULL values, and you stated that NULL values will be converted to empty strings.) Then you compare this in the where clause against the expression IIf([RegionFilter]='',Coalesce([Region],''),[RegionFilter]). The explanation of this logic is the same as previously described for the where clause example.

If you look in the Azure SQL Database, you would see the following table-valued function. (I removed the list of fields to focus on the important part of the SQL—the parameter data types and the where clause.)

CREATE FUNCTION [Access].[qryCustomersForCityAndRegion]
(
    @CityFilter NVarChar(4000),
    @RegionFilter NVarChar(4000)
)
RETURNS TABLE
AS
RETURN
(    
    SELECT
        ...
    FROM
        [Access].[Customers]
    WHERE
        [Customers].[City] = @CityFilter
        AND COALESCE([Region], N'') =
        IIF(@RegionFilter = N'', COALESCE([Region], N''), @RegionFilter)

Next construct a new view based on our parameterized query called CustomersDatasheetForCityRegion; this method for constructing a standalone datasheet was described earlier in this section. Then add a button on your customers datasheet view to open the parameterized view as shown in Figure 6-20. Note that the RegionFilter parameter uses the expression IIf([RegionTextBox] Is Null,'',[RegionTextBox]); remember you indicated earlier that you cannot pass NULL as a parameter, so you convert that value to an empty string.