Managing Row-Returning Queries with ADOX

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 and Removing Queries

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.

Note  

After you append a view to the Views collection of a catalog to create a stored query for the current database, the view will not appear in the database file's Database window ”although you can enumerate it programmatically. When you append a view (or a procedure) to another database file, the view will be visible in the Database window of the other file ”after the window refreshes.

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.

 SubCallAddAView() DimstrSrcAsString DimstrSQLAsString DimstrQryAsString     'SetstrSrctothepathandfilenameofthedatabaseinwhich 'youwanttocreateaview,strSQLtotheSQLstring,andstrQry 'tothenameoftheviewthatyouwanttoadd. strsrc="C:\ProgramFiles\MicrosoftOffice\"&_ "Office11\Samples\Northwind.mdb" strSQL="SELECT*FROMOrders" strQry="AllOrders"     'PassargumentstoAddAView. AddAViewstrSrc,strSQL,strQry     EndSub     SubAddAView(strSrcAsString,strSQLAsString,_ strQryAsString) OnErrorGoToAddAView_Trap Dimcat1AsADOX.Catalog Dimcmd1AsADODB.Command     'InstantiateacatalogfortheAccessdata 'sourcenamedinstrSrc. Setcat1=NewADOX.Catalog cat1.ActiveConnection=_ "Provider=Microsoft.Jet.OLEDB.4.0;"&_ "DataSource="&strSrc     'InstantiateacommandwiththeSQLstatement 'instrSQL. Setcmd1=NewADODB.Command cmd1.CommandText=strSQL     'AppendtheviewwiththenameinstrQryandthe 'CommandTextincmd1. cat1.Views.AppendstrQry,cmd1     'Cleanupobjects. AddAView_Exit: Setcmd1=Nothing Setcat1=Nothing ExitSub     AddAView_Trap: IfErr.Number=-2147217816Then 'Ifviewalreadyexists,removeoldversionandresume. RemoveAViewstrSrc,strQry Resume Else 'Otherwise,printerrormessageandexitgracefully. Debug.PrintErr.Number,Err.Description EndIf     EndSub 

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:

 SubCallRemoveAView() DimstrSrcAsString DimstrQryAsString     'SetstrSrctothepathandfilenameofthedatabaseinwhich 'youwanttodeleteaviewandstrQrytothenameoftheview 'thatyouwanttoremove. strsrc="C:\Access11Files\Samples\Northwind.mdb" strQry="AllOrders"     'PassargumentstoRemoveAView. RemoveAViewstrSrc,strQry     EndSub     SubRemoveAView(strSrcAsString,strQryAsString) Dimcat1AsADOX.Catalog Dimvew1AsADOX.View Dimcmd1AsADODB.Command     'InstantiateacatalogfortheAccessdata 'sourcenamedinstrSrc. Setcat1=NewADOX.Catalog cat1.ActiveConnection=_ "Provider=Microsoft.Jet.OLEDB.4.0;"&_ "DataSource="&strSrc     'RemovetheviewnamedinstrQry,andreport 'successorfailureofattemptinamessagebox Err.Clear OnErrorResumeNext cat1.Views.Deletecat1.Views(strQry).Name IfErr<>0Then MsgBox"Deletionofviewfailed.",vbCritical,_ "ProgrammingMicrosoftAccessVersion2003" ExitSub Else MsgBox"Deletionofviewsucceeded.",vbInformation,_ "ProgrammingMicrosoftAccessVersion2003" EndIf Setcat1=Nothing     EndSub 

Creating and Using Parameter Queries

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.

Note  

Developers who are more familiar with Access data type names than they are with ADO names might find declaring parameter variables with the PARAMETERS keyword inside the query preferable to adding parameters outside the Jet SQL statement. Using the PARAMETERS keyword requires the use of Access data type designations.

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:

 SubSelectInClauseParameters() OnErrorGoToInParameters_Trap DimstrSrcAsString DimstrSQLAsString Dimcmd1AsADODB.Command Dimcat1AsADOX.Catalog Dimprm1AsADODB.Parameter Dimprm2AsADODB.Parameter     'AssignstrSrcvalueforconnectionstringtodatabase 'thatwillgainthenewprocedure strsrc="Provider=Microsoft.Jet.OLEDB.4.0;"&_ "DataSource=C:\ProgramFiles\MicrosoftOffice\"&_ "Office11\Samples\Northwind.mdb"     'QuerydemonstratesuseofINkeywordtoreturncustomers 'matchingtwoparameters(Country1andCountry2). 'UseofaPARAMETERSdeclarationisoptional(seecommented strSQL).   strSQL="SELECTCompanyName,ContactName,CountryFROMCustomers"&_ "WHERECountryIN(Country1,Country2)" 'strSQL="PARAMETERS"&_ 'Country1Text(255),Country2Text(255);"&_ '"SELECTCompanyName,ContactName,CountryFROMCustomers"&_ '"WHERECountryIN(Country1,Country2)"     'Instantiateacommandbasedontheparameterquery. Setcmd1=NewADODB.Command cmd1.Name="TwoParametersIn" cmd1.CommandText=strSQL     'Instantiateacatalog,andappendcommandtothe 'catalogasaprocedure Setcat1=NewADOX.Catalog cat1.ActiveConnection=strSrc cat1.Procedures.Appendcmd1.Name,cmd1     InParameters_Exit: Setcmd1=Nothing Setcat1=Nothing ExitSub     InParameters_Trap: IfErr.Number=-2147217816Then 'Ifprocedureexists,deleteit. cat1.Procedures.Deletecmd1.Name Resume Else Debug.PrintErr.Number,Err.Description MsgBox"Programabortedforunanticipatedreasons.",_ vbCritical,"ProgrammingMicrosoftAccessVersion2003" EndIf     EndSub 

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.

 SubRunTwoParametersIn() Dimcat1AsADOX.Catalog Dimcmd1AsADODB.Command Dimrst1AsADODB.Recordset Dimprm1AsADODB.Parameter Dimprm2AsADODB.Parameter Dimint1AsInteger Dimfld1AsADODB.Field     'InstantiatecatalogforNorthwinddatabase Setcat1=NewADOX.Catalog cat1.ActiveConnection="Provider=Microsoft.Jet.OLEDB.4.0;"&_ "DataSource=C:\ProgramFiles\MicrosoftOffice\"&_ "Office11\Samples\Northwind.mdb"     'AssignareferencetoTwoParametersInCommandproperty Setcmd1=cat1.Procedures("TwoParametersIn").Command     'Createparameters,andassignvaluesforthem. 'Appendparameterstocommand. Setprm1=cmd1.CreateParameter("Country1",adVarWChar,_ adParamInput,255,"UK") Setprm2=cmd1.CreateParameter("Country2",adVarWChar,_ adParamInput,255,"USA") cmd1.Parameters.Appendprm1 cmd1.Parameters.Appendprm2     'Executeandsavereferencetoresultingrecordset. 'Printfirst10recordstoImmediatewindow. Setrst1=cmd1.Execute int1=1 DoUntilrst1.EOF Debug.Print"Outputforrecord:"&int1 ForEachfld1Inrst1.Fields IfNot(fld1.Type=adLongVarBinary)Then_ Debug.PrintString(5,"")&_ fld1.Name&"="&fld1.Value Nextfld1 rst1.MoveNext Ifint1>=10Then ExitDo Else int1=int1+1 Debug.Print EndIf Loop     'Cleanupobjects. Setcat1=Nothing rst1.Close Setrst1=Nothing Setcmd1=Nothing     EndSub 

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