Creating and Modifying DAO QueryDefs

team lib

You will probably remember from the previous chapter that a QueryDef is the name given to a query object variable in VBA. Just as we can create a String -type variable to hold a piece of text, so we can create a QueryDef object to hold a query definition. Note that a QueryDef object holds the definition of a query, not the results. Query results, as we saw in the previous chapter, are held in Recordset objects. If you want, you can think of the QueryDef as being like a query in design view and a Recordset as representing a query in datasheet view.

click to expand

As you can see from the diagram above which shows a section of the DAO hierarchy, the QueryDefs collection belongs to the Database object. This is fairly logical really - after all, a database can have multiple queries in it, but a query can only exist in one database.

Try It Out-Creating and Modifying a QueryDef

  1. Create a new standard code module and call it Chapter 7 Code .

  2. Type in the procedure as it appears below.

       Function MakeQueryDef(ByVal strSQL As String) As Boolean     Dim qdf As QueryDef     Dim boolResultCode As Boolean     If strSQL = "" Then     boolResultCode = False            'no query string to use!     Else     On Error Resume Next              'get ready to handle any errors     'Create the new query object     Set qdf = CurrentDb.CreateQueryDef("qryExample")     If Err.Number <> 0 Then     boolResultCode = False      'failed for some reason so cannot continue     Else     qdf.SQL = strSQL            'succeeded so load the query object with     'the query string     qdf.Close                   'finished with the query object so close it     RefreshDatabaseWindow           'update the Query window     boolResultCode = True           'report a success     End If     On Error GoTo 0                   'reset error handler     End If     MakeQueryDef = boolResultCode       'report what happened     End Function   

    To run the procedure, open the Immediate window. Type in the following and hit Enter :

       ?MakeQueryDef("SELECT * FROM tblCompany WHERE CompanyID = 10")   
  3. The word True should appear in the Immediate window, indicating that the function completed successfully.

    If you try to execute this procedure for a second time it will return False indicating that it has failed. This is because there is already a query called qryExample in the database. We will look at how to handle this situation later on in the chapter. You can check this by commenting out the On Error Resume Next line in the code then running the procedure again. This time the error will not be trapped and instead you will see the Access error message "Object 'qryExample' already exists."

    Important 

    You don't have to worry if you don't quite understand the error handling techniques used in this chapter. We will be revisiting this code in more detail in Chapter 12 when we look at error handling. For the moment, you only need to understand that the use of On Error allows us to handle run-time errors gracefully without confusing messages popping up in front of the user .

  4. Press Alt+F11 to view the Database window and change to the Queries tab. You should see a query there called qryExample .

  5. Open qryExample in design view. The design of the query should match the criteria that you passed as an argument to the MakeQueryDef function.

  6. Now go back to the VBA IDE and type the following procedure in the code window for the Chapter 7 Code module:

       Function ChangeQueryDef(ByVal strQuery As String, ByVal strSQL As String) As Boolean     Dim qdf As QueryDef     Dim boolResultCode As Boolean     If (strQuery = "") Or (strSQL = "") Then     boolResultCode = False            'no query name or query string to use!     Else     On Error Resume Next              'get ready to handle any errors     'modify the query object with the name given     Set qdf = CurrentDb.QueryDefs(strQuery)     If Err.Number <> 0 Then     boolResultCode = False      'failed for some reason so cannot continue     Else     qdf.SQL = strSQL            'succeeded so load the query object with     'the query string     qdf.Close                   'finished with the query object so close it     RefreshDatabaseWindow           'update the Query window     boolResultCode = True           'Report a success     End If     On Error GoTo 0                   'reset error handler     End If     ChangeQueryDef = boolResultCode     'report what happened     End Function   
  7. Run the procedure by typing the following in the Immediate window and hitting Enter :

       ?ChangeQueryDef("qryExample", "SELECT * FROM tblCompany ORDER BY CompanyName")   
  8. The word True should appear in the Immediate window, indicating that the function completed successfully.

  9. Press Alt+F11 to view the Database window and change to the Queries tab. Open qryExample in design view. The design of the query should now have changed to match the new SQL you passed to the ChangeQueryDef function.

How It Works

Creating a QueryDef is very simple.

 Dim qdf As QueryDef Set qdf = CurrentDb.CreateQueryDef("qryExample") 

First, we create an empty QueryDef object. The CreateQueryDef method both creates a QueryDef object and assigns it the name that it will have when it is saved. If you don't intend to save it and will only use it in the current procedure, you can give it an empty string ( "" ) as its name and it won't be saved. However, we do want to save our QueryDef so we can use it again later and so we'll call it qryExample .

Important 

If you try to create a QueryDef object with a name that is the same as a saved query that already exists in the database, Access will generate a run-time error. In our example we have decided to trap errors and return a false value (which we would then have to deal with appropriately) rather than let Access fill the screen with its error message and kill the application. Notice that we also handle the possibility of sending the function an empty SQL string, which would not actually cause an error but it would obviously make no sense to create a query which didn't do anything.

 qdf.SQL = strSQL 

Next, we assign the SQL property of the QueryDef object. If you aren't too sure about how to write SQL, you can always try designing the query normally in the Query Designer and then switching the query to SQL view to see the SQL created. You can then copy the SQL to the clipboard and paste it into your procedure from there.

Finally, we close the QueryDef , with the following line:

 qdf.Close 

The act of closing the QueryDef saves it to the database. Then we refresh the database window to show the changes:

 RefreshDatabaseWindow 

The RefreshDatabaseWindow method causes any changes to database objects such as forms, reports , and queries to be immediately reflected in the database window.

Modifying the QueryDef is just as simple as creating it. First you use a variable to reference the QueryDef called qryExample in the current database.

 Dim qdf As QueryDef Set qdf = CurrentDb.QueryDefs(strQuery) 

And then you modify its SQL property, save it, and refresh the database window like we did in the previous function.

Working with SQL

A QueryDef object has over a dozen different properties, but you will find that there is one property in particular that you will use more than any other; the SQL property. As we saw above, the SQL property is used to set the SQL statement that will be run when the QueryDef object is executed.

If the frmCriteria form is to be used to allow users to frame their queries in a manner of their own choosing, we will then need to convert the entries that the user makes on the form into an SQL statement and use that SQL statement to select the records that should be displayed.

SQL SELECT statements generally consist of three clauses:

Important 

SELECT fields FROM source table AS alias nameWHERE criteria

The SELECT clause indicates the columns or fields that will be displayed in the result set of the query.

The FROM clause indicates the base table or tables from which the results are drawn. The AS keyword is optional but if used gives the alias name to the table or tables specified. For example SELECT tblSales AS s means that you can use s instead of the longer tblSales throughout the rest of the query. Using aliases can save a lot of typing and if used carefully can make queries much easier to read and, therefore, to maintain. It is also possible to abbreviate this clause by omitting the AS keyword but retaining the alias name, for example: SELECT tblSales s

The WHERE clause indicates the criteria for determining which rows or records will be represented in the result set of the query.

Put another way, if you think of the table as a kind of spreadsheet, the SELECT clause is a form of vertical partitioning, and the WHERE clause a method of horizontal partitioning:

click to expand

So let's see how this works when we apply it to the frmCriteria form in the Ice Cream database.

Well, we know which fields we want displayed. We know that the user is to be shown details of each sale and so we will have to display all of the fields from the tblSales table. That will be fixed and won't change, irrespective of the criteria we select on frmCriteria . In other words, the SELECT clause will not change and will always be:

   SELECT tblSales.*   

which selects all of the fields from the tblSales table.

Things get slightly more complicated with the FROM clause. Most of the time we will only need to use the tblSales table. This is because most of the criteria on the frmCriteria form represent columns in the tblSales table. In fact, if we look at the following table, we can see which columns are represented by which criteria on the frmCriteria form.

This criterion

relates to this field

Company is

tblSales.fkCompanyID

Ice Cream is

tblSales.fkIceCreamID

Ice Cream contains

tblIceCreamIngredient.fkIngredientID

Ordered between

tblSales.DateOrdered

Payment delay is

tblSales.DatePaid - tblSales.DateOrdered

Dispatch delay

tblSales.DateDispatched - tblSales.DateOrdered

As you can see, however, one of these criteria is dependent on a field in a separate table. If we want to view sales of ice creams that contain specific ingredients , then we need to join the tblSales table with the tblIceCreamIngredient table and then search for records with an fkIngredientID matching the one selected on the criteria form.

Important 

Remember, although the combo box on frmCriteria displays the ingredient's name, the bound column contains the ingredient's fkIngredientID . Without this fkIngredientID , we would have needed to join the tblIngredient table as well to search on the Name column. This would have been slower to run as well as more complicated to implement.

So, we can see that most of the time our FROM clause will look like this:

 FROM tblSales 

The only variation is when the user chooses to restrict by ingredient, when the FROM clause will look like this:

 FROM tblSales INNER JOIN tblIceCreamIngredient   ON tblSales.fkIceCreamID = tblIceCreamIngredient.fkIceCreamID 

This FROM clause implements the join between the two tables. The two tables to be joined are tblSales and tblIceCreamIgredients :

   FROM tblSales INNER JOIN tblIceCreamIngredient   

And the fields participating on either side of the join are both called fkIceCreamID :

   ON tblSales.fkIceCreamID = tblIceCreamIngredient.fkIceCreamID   

Now for the WHERE clause, this one is certainly going to change. What we are interested in is building up a SQL statement which will restrict the rows selected to those which meet our criteria and as we know, it is the WHERE clause which restricts the rows that are returned. Each of the criteria selected by the user will add at least one extra element to the WHERE clause. So, in the specific example we have been looking at so far, the following WHERE clause would be generated.

   WHERE tblCompany.CompanyID=10     AND tblSales.DateOrdered>=#11/01/02#     AND tblSales.DateOrdered<=#11/30/02#     AND [DateDispatched]-[DateOrdered]>7   

Anyway, enough of the theory; let's get on with writing a procedure which converts our criteria into a SQL string.

Try It Out-Building a SQL String

  1. Open your version of the IceCream.mdb database and open the code module for the frmCriteria form.

  2. Now create a new function called BuildSQLString and add the following code to it:

       Function BuildSQLString(ByRef strSQL As String) As Boolean     Dim strSELECT As String     Dim strFROM As String     Dim strWHERE As String     strSELECT = _     "SELECT s.*.* "                  'set the columns to return results from     '(all columns in this case)     'set an alias "s" for tblSales and "i" for tblIceCreamIngredient     strFROM = "FROM tblSales AS s "     If chkIngredientID Then     strFROM = strFROM & " INNER JOIN tblIceCreamIngredient i " & _     "ON s.fkIceCreamID = i.fkIceCreamID "     'check for 2nd or more WHERE term     If strWHERE <> """" Then strWHERE = strWHERE & " AND "     strWHERE = "i.fkIngredientID = " & cboIngredientID     End If     If chkCompanyID Then     'check for 2nd or more WHERE term     If strWHERE <> "" Then strWHERE = strWHERE & " AND "     strWHERE = strWHERE & "s.fkCompanyID = " & cboCompanyID     End If     If chkIceCreamID Then     'check for 2nd or more WHERE term     If strWHERE <> "" Then strWHERE = strWHERE & " AND "     strWHERE = strWHERE & "s.fkIceCreamID = " & cboIceCreamID     End If     If chkDateOrdered Then     If Not IsNull(txtDateFrom) Then     'Check for 2nd or more WHERE term     If strWHERE <> "" Then strWHERE = strWHERE & " AND "     strWHERE = strWHERE & "s.DateOrdered >= " & _     "#" & Format$(txtDateFrom, "mm/dd/yy") & "#"     End If     If Not IsNull(txtDateTo) Then     'Check for 2nd or more WHERE term     If strWHERE <> "" Then strWHERE = strWHERE & " AND "     strWHERE = strWHERE & "s.DateOrdered <= " & _     "#" & Format$(txtDateTo, "mm/dd/yy") & "#"     End If     End If     If chkPaymentDelay Then     'check for 2nd or more WHERE term     If strWHERE <> "" Then strWHERE = strWHERE & " AND "     strWHERE = strWHERE & "(s.DatePaid - s.DateOrdered) " & _     cboPaymentDelay & txtPaymentDelay     End If     If chkDispatchDelay Then     'check for 2nd or more WHERE term     If strWHERE <> "" Then strWHERE = strWHERE & " AND "     strWHERE = strWHERE & "(s.DateDispatched - s.DateOrdered) " & _     cboDispatchDelay & txtDispatchDelay     End If     strSQL = strSELECT & strFROM     If strWHERE <> "" Then strSQL = strSQL & "WHERE " & strWHERE     BuildSQLString = True     End Function   
  3. Next, amend the event handler for the Click event of the cmdFind button so that it looks like this:

       Private Sub cmdFind_Click()     Dim strSQL As String     Dim strTableName As String     If Not EntriesValid(strTableName) Then Exit Sub     If Not BuildSQLString(strSQL) Then     MsgBox "There was a problem building the SQL string"     Exit Sub     End If     MsgBox strSQL     CurrentDb.QueryDefs("qryExample").SQL = strSQL     End Sub   
  4. Now save the changes you have made to the Form_frmCriteria module and switch back to Access by hitting Alt+F11 .

  5. Open the frmCriteria form and enter the following criteria.

    Field

    Value

    Company is

    Jane's Diner

    Ordered between

    01-Nov-02 and 30-Nov-02

    Dispatch delay is

    More than 7 days

  6. When you hit the Find Matches button, a message box will appear displaying the SQL string that has been constructed .

    click to expand
  7. Hit the OK button and then open the qryExample query in design view. It should reflect the criteria that you entered.

    click to expand
  8. When you run the query it displays the records that meet your criteria.

    click to expand

How It Works

The principle behind this procedure is simple enough, although it may look a bit daunting at first! The general idea is that a variable of type String , strSQL , is passed into the procedure by reference. We do this rather than passing the variable by value as we would normally do so that the function can modify it. Note that we can't simply return the string using the function's return value as we are already using this to indicate success or failure of the operation. The procedure eventually puts into that variable a SQL statement that reflects the choices entered by the user on frmCriteria .

In fact, there is nothing in this exercise that we have not already covered in this chapter. The first step was to create a procedure that generates an SQL string from the criteria that were selected. The heart of this procedure is three variables that correspond to the three parts of the SQL string.

 Dim strSELECT As String Dim strFROM As String Dim strWHERE As String 

As we said earlier, the SELECT clause is invariant, so we can populate that immediately.

 strSELECT = "SELECT s.* " 

The next step is to complete the FROM clause. Again, we looked at this earlier and decided that it should contain just the tblSales table unless the user chose to restrict by ingredient, in which case the tblIceCreamIngredient table should be joined to it. We also alias tblsales to s and tblIceCreamIngredient to i . This simply helps to make the rest of the string shorter and easier to read.

 strFROM = "tblSales AS s "   If chkIngredientID Then     strFROM = strFROM & " INNER JOIN tblIceCreamIngredient i " & _       "ON s.fkIceCreamID = i.fkIceCreamID "     If strWHERE <> """" Then strWHERE = strWHERE & " AND "     strWHERE = " AND i.fkIngredientID = " & cboIngredientID End If 

You can use the "+" (addition) operator instead of the " & " (concatenation) operator to build strings with instead but the " & " operator is able to perform automatic type casting and so is preferable. For example, if you had a string called strTestString and an integer called intTestInt then the equivalent of " strTestString & intTestInt " is " strTestString + Str(intTestInt) " - if you don't explicitly cast the integer variable by using the Str() function then you get a "Type Mismatch" error.

Next we construct the WHERE clause. To build the WHERE clause, we look at each of the textboxes in frmCriteria in turn . If the user has checked the checkbox for a criterion and has entered something in the relevant textbox, we add that criterion to the WHERE clause, after first checking to see if we need to add an and or not as whenever we add a new section to the end of the WHERE clause we must also add an AND in front of it to link the sections together.

Remember that the variable that is being populated with the SQL string is the one that was passed into the function at the start.

 Function BuildSQLString(strSQL As String) As Boolean ...   strSQL = strSELECT & strFROM   If strWHERE <> "" Then strSQL = strSQL & "WHERE " & strWHERE   BuildSQLString = True End Function 

This variable was passed in by reference which means that we can amend the contents of the variable and those changes will persist when the function exits.

All that is left in this procedure is to return True to indicate that the procedure completed successfully. Strictly speaking this is unnecessary as we don't actually perform any validation checks on the string we build. We could have just used a subroutine rather than a function, that is, we don't strictly need to return a value here and so we don't need to use a function. However, it is standard practice to use a function that returns a Boolean value to indicate success or failure, and if at a later date we decide to modify the function to include checking code then we will not need to change any code that calls the function.

Having built the function, we then need to make sure that it is invoked when the user presses the Find Matches button on the criteria form. So we modify the cmdFind_Click procedure by declaring a variable to hold the SQL string and then passing it into the BuildSQLString procedure.

 Dim strSQL As String   Dim strTableName As String   If Not EntriesValid(strTableName) Then Exit Sub   If Not BuildSQLString(strSQL) Then     MsgBox "There was a problem building the SQL string"     Exit Sub   End If 

Note how we check the return value of the BuildSQLString procedure and display an error message if it's False . If it is True , however, we display the string in a message box and then modify the qryExample query to contain the SQL string that we built.

 MsgBox strSQL   CurrentDb.QueryDefs("qryExample").SQL = strSQL 

That is fine as far as it goes and we are well on the way to achieving the functionality that we originally stated. If you remember, we stated earlier that when they run the query, the users want to know how many sales match the criteria that they have specified and should then be presented with the opportunity of viewing:

  • the matching results in detail on screen

  • a report containing the matching results

Well, so far we have a way of determining the criteria that the user wants to use to restrict the sales records that he or she wants to view, and we have a method of programmatically modifying a query in Access to express the user's choice. So, where do we go from here?

 
team lib


Beginning Access 2002 VBA
Beginning Access 2002 VBA (Programmer to Programmer)
ISBN: 0764544020
EAN: 2147483647
Year: 2003
Pages: 256

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net