When you’re designing a web database that will be published to a Microsoft SharePoint server running Access Services, you must create all queries that you want to run on the server from scratch. Access 2010 is the first version of Access to include the new features of web support with Access Services running on top of SharePoint server, which means web queries are a completely new object type in Access databases. You cannot convert client queries used in existing databases into web queries and make them run on the server. You can, however, still run your saved client queries in a published application from Access client.
Throughout this chapter and the previous chapter, you’ve been learning how to create simple queries using one table and complex queries joining more than one table. The process of creating a web query is essentially the same as creating a client query, which means you don’t have to learn a new design process when creating web queries. To create a web query, you must be working in a web database. If you started with a blank web database or used one of the installed web templates that ship with Access 2010, you can immediately begin to create web queries for your web tables. If you are using a client database, however, you cannot create web queries in that database until you first publish the database to a SharePoint server running Access Services. After you publish your client database to the server, your local client tables become web tables and your database is now a web database. You’ll learn about the publishing process to SharePoint server in Chapter 22.
To illustrate the process of creating web queries, open the Back Office Software System data copy (BOSSDataCopy.accdb). Note that when you open the database, you see a copyright notice for a few seconds and then Access closes the notice. After the database opens, click the Navigation Pane menu, click Object Type under Navigate To Category, and then click Queries under Filter By Group to see the list of available queries, as shown in Figure 10-68. Note that Figure 10-68 shows only a few of the saved query objects in this database.
In Figure 10-68, you can see most of the query icons in the Navigation pane have a globe icon, but some do not. Any query in a web database that has a globe icon is a web query and all others are client queries. You can see in Figure 10-58 that the first query, qryDailyLaborChart, is a client query and the second query, qryAppointmentList, is a web query. A web database that includes both client and web objects is also referred to as a hybrid application.
In a web database, the commands in the various groups on the Create tab on the ribbon are organized so the web objects are listed on the left side of each group and the corresponding client objects are listed on the right side of the group. For example, to create a new web query in a web database, you can click the Query button in the Queries group on the Create tab, as shown in Figure 10-69. Notice that the Query button has a globe icon on it to indicate that this command creates a web object. If you click the Client Queries button in the Queries group, Access displays two options for client queries—Query Wizard and Query Design. The Client Queries button is your entry point for creating client queries in web databases.
Note
You cannot use the Query Wizard when creating web queries; you must create all your web queries manually within the Query window.
When you click the Query button in the Queries group in a web database, Access opens the familiar Query Design window that you have been working with throughout this chapter and the previous chapter. The process of creating a web query is essentially the same as a client query—you select the tables or queries you want to use in the Show Table dialog box, adjust any join lines between the tables or queries, select the fields you want to return, and optionally define parameters, calculated fields, sorting, and criteria. When you open the Show Table dialog box and click the Queries tab, Access shows you only a list of the web queries in the web database. You cannot use a client query as a source for a web query, nor can you use a linked table to other data sources besides the published web tables. In Figure 10-70, you can see the query design window for a new web query after we selected the tblAppointments web table in the Show Table dialog box.
Web queries are much more limited in their capabilities compared to their client query counterparts. You can create inner and outer joins with web queries, but you cannot create Cartesian product web queries—queries with no join lines between the web tables. You also cannot create union, data definition, pass-through, or action web queries and you cannot use subdatasheets in web queries. You’ll learn all about client action queries in Chapter 11. PivotTables and PivotCharts views are not supported in web queries. You also cannot use the DISTINCT keyword with web queries. Essentially, you can only create select queries for use on the web. When you’re designing web queries, Access also disables the ability to open the web query in SQL view so you must do all your design work within the Query window.
Inside Out: Using Select * for Web Queries
When you create a web query with the asterisk (*) shorthand symbol to select all fields in the table or query, Access returns additional SharePoint fields in the query results after you publish the database to the SharePoint server. Each SharePoint list includes system fields showing who created or modified a record and when it was created and last modified. If you don’t want these extra SharePoint fields showing up in your web queries after publishing, do not use the shorthand asterisk symbol for the field output.
Web queries do not support any aggregate functions, so you cannot create any totals queries that will run on the web. You can still create client queries with aggregate functions or use the summarization facilities in web reports to calculate totals. You can also use data macros to calculate aggregates at the table level if you need this functionality.
Web queries support less functions in expressions than client queries. You can find a list of the supported functions and their descriptions you can use in web queries in Article 4, “Function Reference,” on the companion CD. When you’re designing any expressions in the Query window, Access provides IntelliSense options for the supported web functions. If you try to use an unsupported function in a web query, Access prompts you that the expression is not supported on the web. You cannot save your changes to the web query design until you remove the unsupported function.
Inside Out: Sorting on a Calculated Field
When you create a calculated expression in a web query, Access disables the sort options for that field because you cannot sort on a calculated field on the web. You can, however, work around this limitation by creating a second query and using the base query with the calculated field as its source. When you place the calculated field from the base query onto the bottom half of the Query window, Access allows you to sort on the calculated field in this second query.
When you’re designing parameters for web queries, you must use explicit parameters; web queries do not support implicit parameters. For example, you cannot have a web query prompt you for an implicit parameter when executing the web query on the web or use a form control reference, such as the following, in a Criteria line:
[Forms]![NameOfForm]![NameOfFormControl]
Parameter values must be pushed into the query when executing on the web; they cannot directly pull values during execution. You can push parameter values into a web query through data macros or user interface macros. You’ll learn how to push parameter values from web forms into web queries through user interface macros in Chapter 21. The one exception to this rule is with combo box row sources. You can use an implicit parameter in the Criteria of a field for a combo box row source. You can use this, for example, for a cascading combo box where the values of one combo box depend on the values displayed in another combo box on a form. You’ll learn how to build combo boxes in Chapter 13.
Access Services has several configurable server settings concerning web queries that you should be aware of when designing queries that will run on the web. Table 10-4 summarizes these server settings with a description of each setting as well as the default values. You should carefully design your web queries to stay under the default settings because you might not have appropriate permissions to change these settings on the server. If you need to increase the default values, you’ll need to talk with your SharePoint server administrator.
Table 10-4. Server Settings for Web Queries
Setting | Description | Default Value | Range |
|---|---|---|---|
Maximum Columns Per Query | The maximum number of columns that can be referenced in a query. Note that some columns may automatically be referenced by the query engine and will be included in this limit. | 40 | 1 to 255 |
Maximum Rows Per Query | The maximum number of rows that a list used in a query can have, or that the output of the query can have. | 25,000 | 1 to 200,000 |
Maximum Sources Per Query | The maximum number of lists that may be used as input to one query. | 12 | 1 to 20 |
Maximum Calculated Columns Per Query | The maximum number of inline calculated columns that can be included in a query, either in the query itself or in any sub-query on which it is based. Calculated columns in the underlying Microsoft SharePoint Foundation list are not included. | 10 | 0 to 32 |
Maximum Order By Clauses Per Query | The maximum number of Order By clauses in the query. | 4 | 0 to 8 |
Allow Outer Joins | Allows left and right outer joins in a query. Inner joins are always allowed. | True | True or False |
Allow Non Remoteable Queries | Allow queries that cannot be remoted to the database tier to run. | True | True or False |
Maximum Records Per Table | The maximum number of records that a table can contain in an application. | 500,000 | -1 (indicates no limit), any positive integer |
Now that you understand the fundamentals of building complex select queries, working with PivotTables and PivotCharts, and designing web queries with Access, you’re ready to move on to updating sets of data with client action queries in Chapter 11.