Saving, updating, and deleting queries that return rows are some of the most common programmatic database design changes you'll want to implement. Chapter 1 and Chapter 2 examined techniques for running row-returning queries. Earlier in this chapter, the section "Jet SQL Syntax for Select Queries" presented Jet SQL rules for expressing row-returning queries. In this section we'll focus on techniques for programmatically building and managing query objects that return rows. In particular, you'll learn how to use an ADOX View object to add or remove a query from a database. Next, we'll discuss designing and running a query that accepts parameters. Parameters are especially important because they permit run-time control over the rows that a query returns. The samples illustrating this topic will increase your understanding of how to use parameters with the ADOX and ADODB libraries. The closing samples in this section demonstrate techniques for adding queries to a database's schema even when you are disconnected from it. This code outlines a process for persisting a database design to disk, modifying the design while you are disconnected from the database, and then updating the original database with the queries you added or changed while disconnected.
Adding a query to a database programmatically is a simple process. You start by defining a new view. This merely involves instantiating an ADOX View object and assigning its properties. Command is among the most important of these properties. To set the view's Command property, you can create a local command, assign its properties, and then assign the local command to the view's Command property. Creating a local Command object enables you to set its CommandText property, through which you specify the Jet SQL that defines the query. To finish adding the query to a database, you append the command to the Views collection of the catalog for the target database file.
The next sample details the process of adding a view to a database as a stored query. In the first procedure, CallAddAView, the sample defines three string variables. The first variable is the path and filename for the Access database file to which you will add a query. The second variable, strSQL, is the Jet SQL statement for the query. The third variable, strQry, designates a name for the new query.
The AddAView procedure contains just three essential steps. First, it instantiates a catalog and points it at the database filename passed to it from CallAddAView. Second, it instantiates a command. The command does not require an ActiveConnection property setting because we're just using it as a container for a Jet SQL statement represented as a string. The command's CommandText property holds the Jet SQL expression for the query. The third step for adding a view to a database's schema as a query is to append the view to the catalog. To do so, you invoke the Append method for the catalog's Views collection. The Append method requires two arguments: a string to name the query and a Command object that defines the query. If you repeatedly work with a catalog's schema, you might encounter a situation in which you attempt to save a view using a name that already exists. This will generate a run-time error. The following sample addresses this issue by using an error trap that calls the RemoveAView procedure, which removes a targeted query from a database. I will discuss this procedure momentarily, after we examine the logic for adding a view. Once the error trap removes the prior version of the query, the AddAView procedure resumes the attempt to save the local Command object to the database as a query.
Sub CallAddAView() Dim strSrc As String Dim strSQL As String Dim strQry As String 'Set strSrc to the path and filename of the database in which 'you want to create a view, strSQL to the SQL string, and strQry 'to the name of the view that you want to add. strSrc = "C:\Program Files\Microsoft Office\" & _ "Office11\Samples\Northwind.mdb" strSQL = "SELECT * FROM Orders" strQry = "AllOrders" 'Pass arguments to AddAView. AddAView strSrc, strSQL, strQry End Sub Sub AddAView(strSrc As String, strSQL As String, _ strQry As String) On Error GoTo AddAView_Trap Dim cat1 As ADOX.Catalog Dim cmd1 As ADODB.Command 'Instantiate a catalog for the Access data 'source named in strSrc. Set cat1 = New ADOX.Catalog cat1.ActiveConnection = _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & strSrc 'Instantiate a command with the SQL statement 'in strSQL. Set cmd1 = New ADODB.Command cmd1.CommandText = strSQL 'Append the view with the name in strQry and the 'CommandText in cmd1. cat1.Views.Append strQry, cmd1 'Clean up objects. AddAView_Exit: Set cmd1 = Nothing Set cat1 = Nothing Exit Sub AddAView_Trap: If Err.Number = -2147217816 Then 'If view already exists, remove old version and resume. RemoveAView strSrc, strQry Resume Else 'Otherwise, print error message and exit gracefully. Debug.Print Err.Number, Err.Description End If End Sub
As you work with a database's design, you will encounter situations in which you need to remove database objects. The preceding sample's error trap shows one scenario in which this need might arise. Normal database maintenance requires you to remove obsolete queries from a database file periodically. Databases load faster and are easier to manage when they contain just the objects that are necessary for an application. To remove a query from a database programmatically, you invoke the Delete method. The following sample demonstrates how to perform this process for a view, but the technique is the same for a procedure. You simply invoke the Delete method for the Views (or Procedures) collection of a catalog pointing at the appropriate database. This next sample contains two procedures. The first assigns one string for the path and filename for the Access database file with the obsolete query and a second string for the name of the query to remove.
The second procedure starts by instantiating a catalog and pointing it at the database with the query to remove. If the specified query is not found in the database, a run-time error occurs when the procedure invokes the Delete method for the Views collection. Instead of implementing a labeled error trap to catch these mistakes, the sample implements an in-line error trap. The code first clears the Err object to make sure no entries from prior errors exist. Then, it instructs the VBA processor to respond to an error by resuming at the line after the line that generated the error. When it invokes the Delete method for the Views collection, if all goes well, the method completes successfully, and the Err object retains its 0 value. The program then exits with a confirmation message and clears the catalog from memory. If the Delete method does not execute successfully, Access updates the Err object with the number for the error and the procedure issues a message that the deletion failed.
The RemoveAView design is convenient because it provides interactive user feedback through a message box. However, when you want a procedure to run automatically without user interaction, this feedback can actually stall a process. You can handle this issue in any of a number of ways, including by making the feedback available only when a calling procedure explicitly requests it. Here's the syntax for the CallRemoveAView and RemoveAView procedures:
Sub CallRemoveAView() Dim strSrc As String Dim strQry As String 'Set strSrc to the path and filename of the database in which 'you want to delete a view and strQry to the name of the view 'that you want to remove. strSrc = "C:\Access11Files\Samples\Northwind.mdb" strQry = "AllOrders" 'Pass arguments to RemoveAView. RemoveAView strSrc, strQry End Sub Sub RemoveAView(strSrc As String, strQry As String) Dim cat1 As ADOX.Catalog Dim vew1 As ADOX.View Dim cmd1 As ADODB.Command 'Instantiate a catalog for the Access data 'source named in strSrc. Set cat1 = New ADOX.Catalog cat1.ActiveConnection = _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & strSrc 'Remove the view named in strQry, and report 'success or failure of attempt in a message box Err.Clear On Error Resume Next cat1.Views.Delete cat1.Views(strQry).Name If Err <> 0 Then MsgBox "Deletion of view failed.", vbCritical, _ "Programming Microsoft Access Version 2003" Exit Sub Else MsgBox "Deletion of view succeeded.", vbInformation, _ "Programming Microsoft Access Version 2003" End If Set cat1 = Nothing End Sub
With parameters, you can allow users to vary the result set of a query at run time. You add parameter queries through the Procedures collection instead of through the Views collection. ADOX lets you save a parameter query with or without a PARAMETERS declaration. However, keep in mind that the parameter query statement itself is a Jet SQL statement—not an ADOX statement. Omitting the PARAMETERS declaration can prevent you from correctly enumerating the members of a query's Parameters collection. On the other hand, failing to specify a PARAMETERS declaration does not stop your application from running the query and supplying parameter values at run time.
The SelectInClauseParameters sample demonstrates how to add a parameter query named TwoParametersIn to the Northwind database file. The sample starts by defining two strings: strSrc for the connection string to the database file, and strSQL for the Jet SQL statement defining the parameter query. The procedure actually includes two versions of the Jet SQL statement for the parameter query; the uncommented version does not have a PARAMETERS declaration, but the commented version does. Either version will enable you to save the parameter query and run it successfully. If you want to, you can use the version that doesn't contain the PARAMETERS declaration and then use ADOX to add a Parameters collection to the Command object for the query before saving it. This achieves nearly the same result as including a PARAMETERS declaration in the Jet SQL statement for the command. The only difference between adding a Parameters collection before saving a parameter query and saving the query without the collection is the effect it has on your ability to enumerate the parameters. Refer back to the EnumerateCommandTextAndParameters procedure we examined earlier in this chapter to see how to enumerate the Parameters collection members.
After defining the strings for the database and the Jet SQL statement, the SelectInClauseParameters procedure instantiates a command and assigns properties to it. Just as with adding a command to the Views collection, you do not need to assign the ActiveConnection property for a command you append to the Procedures collection; this is because the Catalog object contains the connection information. Simply set the Name and CommandText properties for the command. Then, instantiate a catalog that points at the database to which you want to add the procedure as a stored query. Conclude the task by invoking the Append method for the catalog's Procedures collection. A simple error trap recovers from cases in which a user attempts to append a procedure with the same name as a query that already exists in the database. The error trap in this routine works just like the one in the AddAView procedure. Here's the code:
Sub SelectInClauseParameters() On Error GoTo InParameters_Trap Dim strSrc As String Dim strSQL As String Dim cmd1 As ADODB.Command Dim cat1 As ADOX.Catalog Dim prm1 As ADODB.Parameter Dim prm2 As ADODB.Parameter 'Assign strSrc value for connection string to database 'that will gain the new procedure strSrc = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source= C:\Program Files\Microsoft Office\" & _ "Office11\Samples\Northwind.mdb" 'Query demonstrates use of IN keyword to return customers 'matching two parameters (Country1 and Country2). 'Use of a PARAMETERS declaration is optional (see commented strSQL). strSQL = "SELECT CompanyName, ContactName, Country FROM Customers " & _ "WHERE Country IN (Country1, Country2)" 'strSQL = "PARAMETERS " & _ ' Country1 Text ( 255 ), Country2 Text ( 255 ); " & _ ' "SELECT CompanyName, ContactName, Country FROM Customers " & _ ' "WHERE Country IN (Country1, Country2)" 'Instantiate a command based on the parameter query. Set cmd1 = New ADODB.Command cmd1.Name = "TwoParametersIn" cmd1.CommandText = strSQL 'Instantiate a catalog, and append command to the 'catalog as a procedure Set cat1 = New ADOX.Catalog cat1.ActiveConnection = strSrc cat1.Procedures.Append cmd1.Name, cmd1 InParameters_Exit: Set cmd1 = Nothing Set cat1 = Nothing Exit Sub InParameters_Trap: If Err.Number = -2147217816 Then 'If procedure exists, delete it. cat1.Procedures.Delete cmd1.Name Resume Else Debug.Print Err.Number, Err.Description MsgBox "Program aborted for unanticipated reasons.", _ vbCritical, "Programming Microsoft Access Version 2003" End If End Sub
Once a parameter query has been added to a database, running it differs only slightly from running a Command object with parameters in the CommandText property's SQL string. (See "Creating a Recordset with a Parameter Query" in Chapter 2 for a sample that demonstrates how to run a Command object with parameters.) The following procedure, RunTwoParametersIn, illustrates how to run a parameter query that has been added to a database. It runs the parameter query added in the preceding code sample.
The RunTwoParametersIn procedure has three sections. First, it instantiates a catalog and points it at the database with the stored parameter query. Second, it assigns a reference to the Command property of the stored query, TwoParametersIn, to a local variable. Then, it assigns parameter values for the Country1 and Country2 parameters. Before executing the command, the VBA procedure must append the parameters to the Command object. In the third part, the procedure executes the query and saves a reference to the resulting recordset, and then prints the first 10 records.
Sub RunTwoParametersIn() Dim cat1 As ADOX.Catalog Dim cmd1 As ADODB.Command Dim rst1 As ADODB.Recordset Dim prm1 As ADODB.Parameter Dim prm2 As ADODB.Parameter Dim int1 As Integer Dim fld1 As ADODB.Field 'Instantiate catalog for Northwind database Set cat1 = New ADOX.Catalog cat1.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source= C:\Program Files\Microsoft Office\" & _ "Office11\Samples\Northwind.mdb" 'Assign a reference to TwoParametersIn Command property Set cmd1 = cat1.Procedures("TwoParametersIn").Command 'Create parameters, and assign values for them. 'Append parameters to command. Set prm1 = cmd1.CreateParameter("Country1", adVarWChar, _ adParamInput, 255, "UK") Set prm2 = cmd1.CreateParameter("Country2", adVarWChar, _ adParamInput, 255, "USA") cmd1.Parameters.Append prm1 cmd1.Parameters.Append prm2 'Execute and save reference to resulting recordset. 'Print first 10 records to Immediate window. Set rst1 = cmd1.Execute int1 = 1 Do Until rst1.EOF Debug.Print "Output for record: " & int1 For Each fld1 In rst1.Fields If Not (fld1.Type = adLongVarBinary) Then _ Debug.Print String(5, " ") & _ fld1.Name & " = " & fld1.Value Next fld1 rst1.MoveNext If int1 >= 10 Then Exit Do Else int1 = int1 + 1 Debug.Print End If Loop 'Clean up objects. Set cat1 = Nothing rst1.Close Set rst1 = Nothing Set cmd1 = Nothing End Sub
You have to invoke the stored query with a Command object because recordsets do not support parameters. Also note that the stored query includes parameters, but you have to set specific values for those parameters. You can use the CreateParameter method to do this. In addition, when you execute the command, you need to save a reference to the resulting recordset so t