Command and Parameter objects

Within the ADODB library, Command objects deliver three major benefits:

They can perform a select query to return a set of rows from a data source.

They execute a parameter query so that you can input run-time search criteria.

They support action queries against a data source to perform such operations as the updating, deleting, and adding of records.

The Command object can serve additional roles with other libraries, as discussed in later sections.

You must designate a Connection object on which to run a command. You can either implicitly create a Connection object when you specify a command or explicitly assign an existing Connection object to a command. These are the same options as for recordsets.

The CommandTimeout property determines how long ADO waits for the execution of a command to conclude. This property takes a Long value that specifies the maximum wait time in seconds. Its default value is 30. If the timeout interval elapses before the Command object completes execution, ADO cancels the command and returns an error. The Connection object also supports a CommandTimeout property. It has the same name, but it is independent of the Command object's CommandTimeout property. The Command object's CommandTimeout property does not inherit the setting of the Connection object's CommandTimeout property.

The CommandType property There are actually several different types of Command objects. The CommandType property sets the type of Command object. You can base your command on a SQL statement, a table, or a stored procedure, as shown in the following table. One main reason for resorting to a CommandType property setting is to enable the creation of a Command object based on a SQL statement. Changing the CommandType constant from its default setting can speed up the operation of a command. Therefore, if you know the source, you should set this constant.

The CommandText property To write a SQL statement for the command to execute, you use the Command object's CommandText setting. You can also set this property to the name of a stored procedure. When you run a SQL statement, you can use the Prepared property to indicate that the statement is to be compiled and stored on the database server. This slows the first execution of the command but speeds up subsequent executions. You assign True to the Prepared property to invoke compilation of a SQL statement.

CommandType Constants

*Constant*	*Value*	*Behavior*
adCmdText	1	Lets you run a command based on a SQL statement, a stored procedure, or even a table. Usually, you reserve this setting for a SQL statement.
adCmdTable	2	Bases the return set on a previously designed table. Returns all columns from a table based on an internally generated SQL statement.
adCmdStoredProc	4	Runs a command based on text for a stored procedure.
adCmdUnknown	8	There is no specification of the type of command text. This is the default.
adCmdFile	256	Evaluates a command based on the filename for a persistent recordset.
adCmdTableDirect	512	Evaluates a command as a table name. Returns all columns in a table without any intermediate SQL code.

The Execute method The Execute method for a Command object invokes the code behind the Command object (a query, a SQL statement, or a stored procedure). You can specify up to three arguments for the Execute method. The first argument allows the Command object to tell the procedure invoking it how many records it has affected. The second argument can be a Variant array with parameters to drive the command. The third argument tells ADO how to evaluate the source. It can be any of the constant names listed in the table above.

The CreateParameter method The Command object's CreateParameter method creates a new parameter for a command. After creating the parameter, you can use the Append method to add the parameter to the Parameters collection for a command. Before running a parameter query, you also have to assign a value to the parameter.

Creating a recordset with a select query One of the most straightforward tasks you can perform with a Command object is to create a recordset based on a select query. The Command object runs the select query and represents its return set. Your code can then open a Recordset object based on the return set from the Command object. The SelectCommand procedure below accomplishes this. It has two parts: One part creates the Command object and a connection for it to relate to a database, and the second part processes a recordset based on the return set from the Command object.

Sub SelectCommand()

Dim cmd1 As Command

Dim rs1 As Recordset, str1 As String

Dim fldLoop As ADODB.Field

'Define and execute command.

    Set cmd1 = New ADODB.Command

    With cmd1

        .ActiveConnection = CurrentProject.Connection

        .CommandText = "SELECT MyTable.* FROM MyTable"

        .CommandType = adCmdText

        .Execute

    End With

'Open and print recordset.

    Set rs1 = New ADODB.Recordset

    rs1.Open cmd1

    Do Until rs1.EOF

        str1 = ""

        For Each fldLoop In rs1.Fields

            str1 = str1 & fldLoop.Value & Chr(9)

        Next fldLoop

        Debug.Print str1

        rs1.MoveNext

    Loop

End Sub

The first part declares cmd1 as a Command object and then sets three critical properties of the object. Every command must have an ActiveConnection property in order to run against a database. The Command object relies on a SQL statement to represent its select query. You can substitute a saved query. An Execute statement runs the select query. After the Execute method runs, cmd1 contains a reference to a recordset.

The second part of the procedure opens a Recordset object based on cmd1 and prints the return set with tab delimiters (Chr(9)) in the Immediate window. The procedure can handle any number of columns in any number of rows.

Creating a recordset with a parameter query The following code is an example of a parameter query. This code also has a two-part design. The parameter query in the first part has some extra ADO code lines and a different SQL statement syntax than that of the previous select query. The second part that prints the return set is the same as the previous select query.

Sub ParameterQCommand()

Dim cmd1 As Command

Dim rs1 As Recordset, str1 As String

Dim fldLoop As ADODB.Field

Dim prm1 As ADODB.Parameter, int1 As Integer

'Create and define command.

    Set cmd1 = New ADODB.Command

    With cmd1

        .ActiveConnection = CurrentProject.Connection

        .CommandText = "Parameters [Lowest] Long;" & _

            "SELECT Column1, Column2, Column3 " & _

            "FROM MyTable " & _

            "WHERE Column1>=[Lowest]"

        .CommandType = adCmdText

    End With

'Create and define parameter.

    Set prm1 = cmd1.CreateParameter("[Lowest]", _

        adInteger, adParamInput)

    cmd1.Parameters.Append prm1

    int1 = Trim(InputBox("Lowest value?", _

        "Programming Microsoft Access 2000"))

    prm1.Value = int1

'Run parameter query.

    cmd1.Execute

'Open recordset on cmd1 and print it out.

    Set rs1 = New ADODB.Recordset

    rs1.Open cmd1

    Do Until rs1.EOF

        str1 = ""

        For Each fldLoop In rs1.Fields

            str1 = str1 & fldLoop.Value & Chr(9)

        Next fldLoop

        Debug.Print str1

        rs1.MoveNext

    Loop

End Sub

The SQL statement syntax uses a new Parameters declaration line that specifies the parameter's name and data type. The WHERE clause should also reference one or more parameters so that the parameters can affect the return set. These SQL syntax statement adjustments are not by themselves sufficient to make the parameter query work-you must add the parameter and append it to the command using ADO code.

You invoke the CreateParameter method to add the parameter. The code above uses three arguments with the CreateParameter method. The first one names the parameter, the second designates a data type for the parameter, and the third declares a direction for the parameter. The adParamInput constant is actually the default that declares the parameter an input to the query. Other constants let you designate output, input/output, and return value parameters. After creating a parameter, you must append it to the Parameters collection for the command.

After writing the code to add a parameter, you must assign a value to the parameter to make the parameter query command function properly: The code above uses an InputBox function to gather input from a user. The procedure then invokes the Command object's Execute method to generate a return set.

Deleting records You can use the Command object to delete, update, and add records to a data source. Command objects offer a programmatic means of maintaining a data source. The DeleteARecord and DeleteAllRecords procedures below prune records from a data source. You designate the data source and the criteria for selecting records using the SQL DELETE statement. The SQL view in the Access query window lets you graphically design a query and then copy the code to the CommandText property of a command. You typically want to edit the SQL code from the Access query designer to remove extra parentheses. If your query operates on a single table, you can remove the table prefix before field names. As you can see, the difference between the two delete queries is simply the syntax of the SQL statement.

Sub DeleteARecord()

Dim cmd1 As ADODB.Command

    Set cmd1 = New ADODB.Command

    With cmd1

        .ActiveConnection = CurrentProject.Connection

        .CommandText = "DELETE MyTable.Column1 FROM " & _

            "MyTable WHERE (((MyTable.Column1)=13));"

        .CommandType = adCmdText

        .Execute

    End With

End Sub

Sub DeleteAllRecords()

Dim cmd1 As ADODB.Command

    Set cmd1 = New ADODB.Command

    With cmd1

        .ActiveConnection = CurrentProject.Connection

        .CommandText = "DELETE MyTable.* FROM MyTable"

        .CommandType = adCmdText

        .Execute

    End With

End Sub

Inserting records When you develop an application, you might want the ability to delete all the records from a table and then reset its contents. The InsertRecords procedure below uses the Command object to stock a table with values. You can use this procedure in conjunction with the DeleteAllRecords procedure to refresh a table with a small base set of records.

Sub InsertRecords()

Dim cmd1 As ADODB.Command

    Set cmd1 = New Command

    With cmd1

        .ActiveConnection = CurrentProject.Connection

        .CommandText = "INSERT INTO MyTable(Column1, " & _

            "Column2, Column3) VALUES (1,2,'3')"

        .CommandType = adCmdText

        .Execute

        .CommandText = "INSERT INTO MyTable(Column1, " & _

            "Column2, Column3) VALUES (4,5,'6')"

        .CommandType = adCmdText

        .Execute

        .CommandText = "INSERT INTO MyTable(Column1, " & _

            "Column2, Column3) VALUES (7,8,'9')"

        .CommandType = adCmdText

        .Execute

        .CommandText = "INSERT INTO MyTable(Column1, " & _

            "Column2, Column3) VALUES (10,11,'12')"

        .CommandType = adCmdText

        .Execute

        .CommandText = "INSERT INTO MyTable(Column1, " & _

            "Column2, Column3) VALUES (13,14,'15')"

        .CommandType = adCmdText

        .Execute

        .CommandText = "INSERT INTO MyTable(Column1, " & _

            "Column2, Column3) VALUES (16,17,'18')"

        .CommandType = adCmdText

        .Execute

    End With

End Sub

The InsertRecords procedure has general and specific elements. The general elements do not depend on the design of a particular table. In the code above, the specific elements tailor the general elements for the MyTable table. Figure 2-7 shows MyTable in Design view. It has three columns, named Column1, Column2, and Column3. The first two columns have Long Integer data types, and the third column has a Text data type. (When you add records to a table, you must consider the field data types.)

Figure 2-7. The Design view of the table to which the InsertRecords procedure adds records.

The general elements of the InsertRecords procedure are shared with other applications of the Command object. You create a reference to the Command object and set its connection property. For each row that you need to add to a record, three lines are required: the CommandText property setting, which indicates what the command will do; the CommandType property setting, which designates the format of the instruction; and the Execute method, which launches the addition of the new record. You can repeat these three lines for each row added to the data source. If you specify an updatable dynaset as the target, these steps can concurrently add records to two or more tables at the same time.

The syntax of the CommandText SQL statement has three features. (This syntax is not available from the SQL view of the Access query designer.) First, it uses the INSERT INTO keyword, which is followed by the name of the data source to which you want to add records. Second, it takes the optional step of listing the field names for which it submits values. If you do not take this step, your values in the third step will append in sequential order, which can be a problem if the data source design changes over time. Third, the VALUES keyword appears before the field values for the new record.

Updating record values The OddToEven and EvenToOdd procedures below update data source values of Column1 using the Command object. Figure 2-8 shows a fresh view of the table immediately after the DeleteAllRecords and InsertRecords procedures run. Notice that in Figure 2-8 the Column1 values alternate between odd and even: If the value in Column1 is odd, the value in Column2 is even. The procedures use this information to manage the contents of the table.

Figure 2-8. A Datasheet view of the table that the OddToEven and EvenToOdd procedures update.

Sub OddToEven()

Dim cmdO2E As ADODB.Command

Dim intRowsChanged As Integer

    Set cmdO2E = New ADODB.Command

    With cmdO2E

        .ActiveConnection = CurrentProject.Connection

        .CommandText = "UPDATE MyTable SET Column1 = " & _

            "Column1+1 WHERE ((-1*(Column1 Mod 2))=True)"

        .CommandType = adCmdText

        .Execute intRowsChanged

        Debug.Print intRowsChanged & " rows were affected."

    End With

End Sub

Sub EvenToOdd()

Dim cmdE2O As ADODB.Command

    Set cmdE2O = New ADODB.Command

    With cmdE2O

        .ActiveConnection = CurrentProject.Connection

        .CommandText = "UPDATE MyTable SET Column1 = " & _

            "Column1-1 WHERE ((-1*(Column2 Mod 2))=False)"

        .CommandType = adCmdText

        .Execute

    End With

End Sub

The overall design of these procedures should be familiar by now. The most significant difference between these examples and earlier ones is in the syntax of the SQL statement for the CommandText property. In this case, you can easily derive that general syntax from the Access query designer. The WHERE clause in the OddToEven procedure selects records whose Column1 value is odd. The UPDATE part of the syntax adds 1 to the value to convert the value from an odd to an even number. The Execute method uses one of its built-in arguments to return the number of rows that a command changes. A simple Print method sends this value to the Immediate window for viewing.

The EvenToOdd procedure examines the entry in Column2 to determine whether it should subtract 1 from the value in Column1. When the entry in Column2 is not odd, the SQL statement operates on the value in Column1. This restores the entries in Column1 to their initial values if EvenToOdd runs immediately after the OddToEven procedure runs.