Creating and Running Action Queries with ADOX

Inserting, deleting, and updating records in a record source are classic database tasks . From its inception, Access offered special queries to facilitate these tasks; these queries are known as action queries. Although you can specify these tasks with SQL text each time you perform them, the operations perform faster when you run them from a stored query. This is because Jet already has the SQL statement compiled when it runs a stored query; Jet doesn't have to compile the statement each time as it would if you passed it a SQL string.

Stored action queries are most versatile when you design them with parameters. The parameters allow users to reuse stored action queries in more circumstances. For example, you can re-use the same insert parameter query to add many different records to a database. This, in turn , reduces the number of queries in a database file, which can help your custom applications load faster.

This section discusses different ways to use parameters with stored action queries. It shows you the Jet SQL statements for adding, deleting, and updating records and demonstrates how to use parameters with those statements. It covers how to save the action queries and how to assign parameters when you are ready to run those queries.

An Insert Parameter Query

The key to saving an insert action query with one or more parameters is to understand the structure of the INSERT INTO statement in SQL. After the INSERT INTO keywords, designate the name of the table to which you will add a record. Follow the table name with a list in parentheses of field names. The field names denote those fields for which your statement specifies values. Although using the list is optional when you specify values for all fields in the order in which the fields exist in the record source, it is good practice to use the list in all instances. Follow the list of field names with the VALUES keyword. This keyword signals the beginning of a list in parentheses of values that matches the order of the field name list. When creating a parameter query to let users enter the values to insert at run time, specify parameter names instead of actual values. After designing the Jet SQL statement for an action query, save it by assigning the statement to a Command object and appending the command to the Procedures collection of a database's catalog.

Note	Jet SQL supports copying a group of records from one table to another. This approach follows the format for a typical append query with the Query Designer. You can use the Query Designer's SQL View for any typical append query to review the Jet SQL syntax for that design.

The following procedure, ParamQInsertsShippers , demonstrates the syntax for saving an insert action query. The stored query inserts one new record into the Shippers table in the Northwind database. The procedure begins by assigning the connection string to a string variable, strSrc . Then, it assigns the SQL statement with the parameters to a second string variable, strSQL . Next , the procedure instantiates a command and assigns the strSQL string to its CommandText property. It names the command InsertAShipper . Finally, the sample instantiates a catalog and assigns the strSrc string to its ActiveConnection property.

Instead of using a labeled error trap to recover from an attempt to save over an existing procedure in the catalog, the sample uses an in-line trap design. (The RemoveAView procedure, which we examined earlier in this chapter, uses a similar approach to recover from an attempt to delete a view that's not in a catalog.)

SubParamQInsertsShippers()
DimstrSrcAsString
DimstrSQLAsString
Dimcmd1AsADODB.Command
Dimcat1AsADOX.Catalog
   
'AssignstrSrcvalueforconnectionstringtodatabase
'thatwillgainthenewprocedure
strsrc="Provider=Microsoft.Jet.OLEDB.4.0;"&_
"DataSource=C:\ProgramFiles\MicrosoftOffice\"&_
"Office11\Samples\Northwind.mdb"
   
'Instantiateacommandbasedonaparameterquery.
strSQL="INSERTINTOShippers(CompanyName,Phone)"&_
"VALUES(CNameIn,PhoneIn)"
Setcmd1=NewADODB.Command
cmd1.Name="InsertAShipper"
cmd1.CommandText=strSQL
   
'Instantiateacatalog,andpointitatthecommand's
'databaseconnection.
Setcat1=NewADOX.Catalog
cat1.ActiveConnection=strSrc
   
'Createanewprocedurethatappearsinthe
'Databasewindowasanewquery;embedprocessin
'anerrortrapthatreliesontheErrobject
'tocheckforanalreadyexistingquery
Err.Clear
OnErrorResumeNext
cat1.Procedures.Appendcmd1.Name,cmd1
IfErr.Number=-2147217816Then
cat1.Procedures.Deletecmd1.Name
cat1.Procedures.Appendcmd1.Name,cmd1
ElseIfErr.Number<>0Then
Debug.PrintErr.Number,Err.Description
MsgBox"Error;seeImmediate window diagnostics.",_
vbCritical,"ProgrammingMicrosoftAccessVersion2003"
ExitSub
EndIf

'Cleanupobjects.
Setcmd1=Nothing
Setcat1=Nothing
   
EndSub

Although this sample saves a parameter query, it does not run the query. Therefore, you still need to invoke the parameter query to add a record to the Shippers table. To do so, you must run the Execute method using a local reference to the stored action query's command. The following procedure illustrates how to run a stored parameter query for inserting new records into the Shippers table. The procedure starts by instantiating a catalog for the Northwind database. Next, it creates a reference named cmd1 to the Command property for the InsertAShipper query. Then, the sample invokes the CreateParameter method a couple of times to create parameters for the cmd1 object. The CreateParameter method adds a new parameter, but assigning a value to the parameter with this method is optional. This sample sets the parameter values directly using a couple of InputBox function statements. After a user responds to the InputBox dialog boxes, the procedure appends the parameters to the Parameters collection for the cmd1 object. Finally, the procedure executes the cmd1 object's command.

SubRunParamQInsertsShippers() 
Dimcat1AsADOX.Catalog
Dimcmd1AsADODB.Command
Dimprm1AsADODB.Parameter
Dimprm2AsADODB.Parameter
   
'InstantiatecatalogforNorthwinddatabase.
Setcat1=NewADOX.Catalog
cat1.ActiveConnection="Provider=Microsoft.Jet.OLEDB.4.0;"&_
"DataSource=C:\ProgramFiles\MicrosoftOffice\"&_
"Office11\Samples\Northwind.mdb"
   
'AssignareferencetotheInsertAShipperquery's
'Commandproperty.
Setcmd1=cat1.Procedures("InsertAShipper").Command
   
'Createparametersandassignvaluesforthem.
'Appendparameterstocommand.
Setprm1=cmd1.CreateParameter("CnameIn",adVarWChar,_
adParamInput,40)
Setprm2=cmd1.CreateParameter("Phone",adVarWChar,_
adParamInput,24)
   
prm1.Value=Left(InputBox("CompanyName:"),40)
prm2.Value=Left(InputBox("Phone:"),24)
cmd1.Parameters.Appendprm1
cmd1.Parameters.Appendprm2
   
'Runtheinsertactionquery.
cmd1.Execute
   
'Cleanupobjects.
Setcmd1=Nothing
Setcat1=Nothing
   
EndSub

You do not have to repeat all the setup steps to run a stored action query each time you add a new record. If an application adds more than one new record at a time, you can just change the parameter value assignments ”don't append the parameters a second time ”and re-execute the cmd1 object.

A Delete Parameter Query

The sample for adding an insert action query that you just saw is easy to follow, but you might want a more general model for saving action queries or any type of query that can be a member of the Procedures collection in the ADOX object library. The next sample you'll see for saving a delete action query provides a more general design. While the upcoming sample is suitable for the delete action query that it presents , you can readily adapt the code for use with any action query, including the InsertAShipper action query you saw in the previous code sample. Of course, even if you have a general routine for saving an action query with parameters, you still need a specific VBA procedure to invoke the action query that passes the query the appropriate parameters.

Together, the next two procedures create a delete action query. The first procedure, CallAddParamQuery , assigns three string constants ”one for the database in which to store the action query, another for the SQL statement defining the action query, and a third with the name of the action query. By changing the SQL string, this sample could create an insert or update action query just as easily as it creates a delete action query. The WHERE clause in the SQL statement for strSQL contains a parameter named CNameOut . When your application invokes the query, Access will delete all records from the Shippers table for the shipper indicated by the CNameOut parameter. Here's the CallAddParamQuery procedure:

SubCallAddParamQuery()
DimstrSrcAsString
DimstrSQLAsString
DimstrQryAsString
   
'AssignstrSrcforconnectiontodatabase
'thatwillgainthenewquery
strsrc="Provider=Microsoft.Jet.OLEDB.4.0;"&_
"DataSource=C:\ProgramFiles\MicrosoftOffice\"&_
"Office11\Samples\Northwind.mdb"
'AssigntheSQLstatement
strSQL="DELETE*FROMShippers"&_
"WHERECompanyName=CNameOut"
strQry="RemoveAShipper"
   
'Passarguments.
AddParamQuerystrSrc,strSQL,strQry
   
EndSub

The actual process of adding the query to a database file takes place in the second procedure. The steps for performing this task should be familiar by now. Their presentation here illustrates two points: how to use the steps with an action query, and how to make the steps more general so that they are suitable for saving any query that belongs to the Procedures collection. Here's the AddParamQuery procedure:

SubAddParamQuery(strSrcAsString,_
strSQLAsString,strQryAsString)
Dimcmd1AsADODB.Command
Dimcat1AsADOX.Catalog
   
'Instantiateacommandbasedontheparameterquery.
Setcmd1=NewADODB.Command
cmd1.Name=strQry
cmd1.CommandText=strSQL
   
'Instantiateacatalog,andpointitatthecommand's
'databaseconnection.
Setcat1=NewADOX.Catalog
cat1.ActiveConnection=strSrc
   
'Createanewprocedurethatappearsinthe
'Databasewindowasanewquery;embedprocessin
'anerrortrapthatreliesontheErrobject
'tocheckforanalreadyexistingquery
   
Err.Clear
OnErrorResumeNext
cat1.Procedures.Appendcmd1.Name,cmd1
IfErr.Number=-2147217816Then
cat1.Procedures.Deletecmd1.Name
Err.Clear
cat1.Procedures.Appendcmd1.Name,cmd1
ElseIfErr.Number<>0Then
Debug.PrintErr.Number,Err.Description
MsgBox"Error;seeImmediate window diagnostics.",_
vbCritical,"ProgrammingMicrosoftAccessVersion2003"
ExitSub
EndIf
   
'Cleanupobjects.
Setcmd1=Nothing
Setcat1=Nothing
   
EndSub

Once you save the action query to delete records, you still need a routine to set the parameters and invoke the query. The RunRemoveShippers procedure that appears next provides that model. The overall process is straightforward. First, create a catalog that points at the database file with the action query. Second, create a reference to the action query's Command property. Third, create a parameter for the query, assign it a value, and append the parameter to the Parameters collection for the Command object. Finally, execute the local reference to the command.

Designating the parameter value is the only part of the process for running the RemoveAShipper query that isn't generic. You should prompt for a value that is appropriate for the specific parameter query with which you are working. In this case, the sample uses an InputBox function to prompt for a company name to use as the CNameOut parameter:

SubRunRemoveShippers()
Dimcat1AsADOX.Catalog
Dimcmd1AsADODB.Command
Dimprm1AsADODB.Parameter
   
'InstantiatecatalogforNorthwinddatabase.
Setcat1=NewADOX.Catalog
cat1.ActiveConnection="Provider=Microsoft.Jet.OLEDB.4.0;"&_
"DataSource=C:\ProgramFiles\MicrosoftOffice\"&_
"Office11\Samples\Northwind.mdb"
   
'AssignreferencetotheRemoveAShipperquery's
'Commandproperty.
Setcmd1=cat1.Procedures("RemoveAShipper").Command
   
'Createparameter,andassignvalueforit.
'Appendparametertocommand.
Setprm1=cmd1.CreateParameter("CNameOut",adVarWChar,_
adParamInput,40)
   
prm1.Value=Left(InputBox("CompanyName:"),40)
cmd1.Parameters.Appendprm1
   
'Runthedeleteactionquery.
cmd1.Execute
   
'Cleanupobjects.
Setcmd1=Nothing
Setcat1=Nothing
   
EndSub

An Update Parameter Query

The UPDATE statement in SQL lets you change an old field value to a new one. By using parameters, you can let users designate the old and new values at run time. An UPDATE statement contains three clauses. In the first clause, the UPDATE keyword designates a record source for updating. The SET keyword, the second clause in an UPDATE statement, assigns a new value for a field name. Without the optional third clause, the UPDATE statement transforms the field value for each row within a record source to the new value designated by the SET clause. If your update includes an optional WHERE clause, it can restrict which rows change to the new value. This WHERE clause can take any expression appropriate for a WHERE clause in a normal SELECT statement.

The following sample creates an update action query in the Northwind database. Notice that the sample calls the AddParamQuery procedure from the previous section. This reuse of the AddParamQuery procedure demonstrates its versatility with another type of parameter action query.

The Jet SQL statement for the update action query is saved in the strSQL string variable. The statement has two parameters for the CompanyName field in the Shippers table of the Northwind database. The CNameNew parameter specifies the new name that you want to set in the CompanyName field. The CNameOld parameter signifies which records in the Shippers table to transform to the new value. Only rows with a CompanyName field equal to the value of the CNameOld parameter get their CompanyName field transformed to the value of the CNameNew parameter.

SubCreateParamQForUpdating() 
DimstrSrcAsString
DimstrSQLAsString
DimstrQryAsString
   
'AssignstrSrcvalueforconnectiontodatabase
'thatwillgainthenewquery.
strsrc="Provider=Microsoft.Jet.OLEDB.4.0;"&_
"DataSource=C:\ProgramFiles\MicrosoftOffice\"&_
"Office11\Samples\Northwind.mdb"
'AssignSQLstatement.
strSQL="UPDATEShippersSETCompanyName=CNameNew"&_
"WHERECompanyName=CNameOld"
strQry="UpdateOldToNewShipper"
   
'Passarguments.
AddParamQuerystrSrc,strSQL,strQry
   
EndSub

After running CreateParamQForUpdating , you will have an update action query in the Northwind database. The next pair of procedures allows you to see the behavior of the update action query. The AddFooRecord procedure adds a record to the Shippers table with a CompanyName of foo . You should run the procedure and then open the Northwind database to verify the addition of the new record before running the second procedure. The second procedure, RunUpdateFooToBoo , transforms any CompanyName with a value of foo to boo. After running the second procedure, you will see a new company named boo in the Shippers table.

The RunUpdateFooToBoo procedure applies standard techniques for running a parameter query. First, it creates a catalog that points at the database file containing the parameter query. Second, it creates a reference to the parameter query's Command property. Third, it creates parameters for the command that correspond to those for the stored parameter query. This involves assigning values to the parameters and appending them to the Command object. Finally, it executes the local Command object.

SubAddFooRecord()

Dimcmd1AsADODB.Command
DimstrSrcAsString
DimstrSQLAsString
   
strsrc="Provider=Microsoft.Jet.OLEDB.4.0;"&_
"DataSource=C:\ProgramFiles\MicrosoftOffice\"&_
"Office11\Samples\Northwind.mdb"
strSQL="INSERTINTOShippers(CompanyName)"&_
"Values('foo')"

Setcmd1=NewADODB.Command
cmd1.ActiveConnection=strSrc
cmd1.CommandText=strSQL
cmd1.Execute
   
EndSub
   
SubRunUpdateFooToBoo()
DimstrSrcAsString
DimstrSQLAsString
Dimcat1AsADOX.Catalog
Dimcmd1AsADODB.Command
Dimprm1AsADODB.Parameter
Dimprm2AsADODB.Parameter
   
'AssignstrSrcvalueforconnectionstringtodatabase
'thatcontainsthequeryyouwanttorun.
strsrc="Provider=Microsoft.Jet.OLEDB.4.0;"&_
"DataSource=C:\ProgramFiles\MicrosoftOffice\"&_
"Office11\Samples\Northwind.mdb"
   
'InstantiatecatalogforNorthwinddatabase.
Setcat1=NewADOX.Catalog
cat1.ActiveConnection=strSr