Simplifying Queries

<< Click to Display Table of Contents >>

Navigation:  Queries >

Simplifying Queries

Russell Sinclair          

Rather than define every query that your users might require, why not let your users make up their queries as they need them–provided that they're not going to be overwhelmed by the options available to them. Russell Sinclair discusses how to create a simplified query interface for Access users.

If you're reading this article, chances are that you have a reasonably good idea of how to work with queries in Access databases. You know how to use the query designer to get at the data you want and how to use that data in your applications. However, can you say that your users have those same skills?

One of the companies I'm working with right now–M7 Database Services at www.m7database.com–specializes in developing Access solutions for small to mid-sized companies or branch offices for large companies. These solutions tend to be aimed at a small group of end users who generally have little or no database design experience. Many of these people don't know how to create queries or work with tables and other Access objects (that's why they hire the experts). So when the users wanted to start running their own queries, M7 needed a tool that could simplify the query process.

The first design conversations we had about creating a simplified query tool resulted in all sorts of suggestions, including the ability to limit fields, group fields, and calculate totals. We realized, however, that with too many features, the query builder would simply be a copy of the Access query designer. Apart from the fact that it might get too complicated for users to work with, we didn't want to reinvent the wheel. If the users were sophisticated enough to use these features, they were probably capable of using the Access query designer.

We finally settled on the features that we knew would be required:

• The query builder would have to base the data it worked with on a query that we pre-created for the users. This would insulate the users from having to use the designers to create their queries.

• The users would have to have the ability to filter columns in the query to specific values they would choose.

• The users would need to be able to select the fields that get output, or output all fields.

• The users would need the ability to view the results and export them to other applications.

It also wouldn't hurt if the application worked with both MDBs and ADPs.

Reading the queries

When I created the query building application, I knew I'd want to be able to allow users to use only a select set of queries with the tool. I didn't want them to be able to use just any query in the database because that would probably make the tool much harder to use. Instead, I came up with a naming convention for these queries: Queries that had a prefix of "qbf" could be used with our tool. In the code module fdlgQueryBuilder in the sample database, the ListQueries function in that form returns a list of all of the qbf queries. The code loops through the objects in the AllViews, AllFunctions, and AllQueries properties for the CurrentData object. When it runs across a query that starts with qbf, it adds the query to a local table tblQuery with the name of the query, the query type, and the name without the prefix as the name displayed for the query to an end user. This data is used to populate the Query dropdown in the builder form shown in Figure 1.

200511_RS1

Figure 1

Once a user selects a query on this form, the code analyzes it to see what fields it contains and gathers information on the data contained in those fields. The way that this is done is different for SQL Server queries and Access queries. In fact, it's probably the first time I can think of when I could justify using both DAO and ADO in the same procedure. If you look at the code tied to the AfterUpdate event of the cboQuery combo box, you'll see both of these methods.

When working with the Access objects, I referenced the QueryDef object that represented that query. This object allowed me to easily loop through the available fields.

For the SQL Server objects, I had to the use the OpenSchema function on the ADO Connection object (see the sidebar, "Connection.OpenSchema") calling for the adSchemaColumns, or adSchemaProcedureColumns recordset. I then used the data in this recordset to populate the data in tblField. Although I would have liked to standardize the code for Access and SQL databases, not all data providers support all of the schemas this function can return. This is the case with Access and the adSchemaProcedureColumns enumeration values.

For each field, I stored the name, position, and type. This information is used in the criteria sub-form to allow users to pick the fields from the dropdowns and to help me format and validate data the user enters.

The user interface is reasonably simple. A user can select the query from the list and then select a field to use in the sub-form. In order to maintain the ease of use of the application, the only comparison operators I chose to implement were equals, does not equal, greater than, and less than. The user can select one of these operators and enter up to three criteria that are ORed together. I didn't provide users the ability to define how criteria related to each other. The main reason behind this decision was to avoid confusion around the order of operations when mixing ANDs and ORs. What I did, instead, was to treat each criterion specified as cumulative so that it's ANDed with other criteria. All of these criteria are stored in tblCriteria and used when the user clicks the View Results button to generate the SQL statement for the resulting data.

Viewing the results

Generating SQL is probably something we've all tried at some point or another. The code in basQueryBuilder is the result of many lessons learned through past projects. The code in this module splits the task of building the SQL string into two units: building the SELECT string, and building the WHERE clause. The SELECT string is easily defined by the selected fields in tblCriteria or all fields. A simple comma-delimited list of fields is built or a wildcard is used.

The WHERE clause is slightly more complicated. Whenever one of the value fields is filled in, the WhereClause function calls the CriteriaString function to build a criteria string. This function determines the right operators to use, handles text formatting to use for dates, number, and Boolean fields, and performs wildcard conversion. The path that it takes through the function is very much dependent on the data type of the field that's being analyzed.

With the SQL statement complete, the application is ready to submit the statement, retrieve the data, and present the data to the user. However, I didn't want to have to go to an external interface for the users to be able to see the data. I wanted them to be able to open a form and preview the data in place, with all of the functionality available that Access can provide. This meant that I had to modify the design of a form on the fly.

The click event of cmdViewResults in the query builder dialog takes care of this for me. The SQL statement is used to open an ADO recordset object. The code opens the sub-form fsfrQueryResults (which will display the results) in design view but hidden. The code then removes any existing controls on the form and then adds a checkbox for each Boolean field or a textbox for any other field. This is handled through the Application.CreateControl method. The form is displayed in datasheet view so I don't need to worry about the layout of the controls–they automatically show up in the dataset in the order in which they're created on the form. With all the controls created, the form is closed and saved. After that, the parent form for this sub-form is opened and the ADO recordset I got earlier to the Recordset property of the sub-form is assigned to its Recordset property. This allows me to use either SQL Server or Access data without having the change the code. The form is shown in Figure 2.

Figure 2

The results form allows the user to preview the data or copy it from Access to some other application. The data can also be exported from the form by a button click. The button simply calls DoCmd.OutputTo to export the data.

How to use it

The resulting application is available in the accompanying download. The application is designed as an add-in, which allows it to work with both SQL Server and Access data without changing your code. However, if you would rather integrate the code right into an application, you can import all of the objects from the sample database into your own project. All you need to do to start the application is open the query builder dialog.

Sometimes simplicity in design can lead to a better user experience. This tool empowers beginner users with their data in much the same way that the Access query designer can empower more advanced users. The feedback we've had on this tool so far has been extremely favorable. Think about including something like this in your next project and see how much you can improve the usability of your application.

 

Your download file is called 511SINCLAIR.ZIP in the file SA2005-11down.zip

This can be purchased with all the other downloads on this page

 

Before using as an Add-in, you may wish to edit the "qbf" prefix to suit your query name conventions. This is defined by the mstrcPrefix constant in the ListQueries function in the form fdlgQueryBuilder .
 
To install an Access Add-IN, use the menu in Access 2003 - Tools then Add-Ins then locate the
"M7 Query Builder.mda" file.  Once a file has been setup as an Add-In, a copy of the file is moved to the Access AddIns folder.

Sidebar: Connection.OpenSchema

The OpenSchema method of the ADO Connection object returns information about the data store defined in the connection. It allows you, among other things, to list tables, queries, constraints, and indexes. It can also be used to list users in a database and many other things. This function takes three parameters. The first parameter is a value of the SchemaEnum enumeration that defines the information you want to get. The second parameter is an optional array of restrictions you want to place on the data. Each member of the array corresponds to a particular column in the result set. The Access Help has information on what restrictions can be used with each schema. The final parameter, SchemaID, is a GUID value that's only used if the schema requested is adSchemaProviderSpecific. This special schema type allows you to request information that's custom tailored to the provider, such as the value "{947bb102-5d43-11d1-bdbf-00c04fb92675}," which will cause the function to return the list of users connected to an Access database. The result of the OpenSchema call is a read-only ADO Recordset.

 

Other Pages On The site You Might Like To Read

Handling Visual Complexity

Simplifying Complex SQL

Making the Grade

 

ourProducts