|
|
The Command object within the ADODB library delivers three major benefits. First, it can be used to perform a select query to return a set of rows from a data source. Second, it can be used to execute a parameter query so that users can input run-time search criteria. And finally, it supports action queries against a data source to perform operations such as updating, deleting, and adding records.
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 you have 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. The default value for this property is 30 seconds. 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. Although this property has the same name as the CommandTimeout property of the Command object, it is independent of that property; the Command object's CommandTimeout property does not inherit the setting of the Connection object's CommandTimeout property.
There are actually several types for the Command object. The CommandType property sets the type of the Command object and lets ADO know how to interpret the object's CommandText property. You can base your command on a SQL statement, a table, or a stored procedure by setting the CommandType property to one of the values shown in Table 2-1. When working with an Access database, a stored procedure is equivalent to either a stored action query or a stored select query with parameters. Basing your command on a SQL statement keeps your query out of the Database window, although performance isn't as good as basing the command on a stored query.
|
Constant |
Value |
Behavior |
|---|---|---|
|
-1 |
Informs ADO that no CommandText property setting exists. Do not use this setting with the OLE DB provider for AS/400 and VSAM or the OLE DB provider for DB 2. |
|
|
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. This is the only legitimate setting for the OLE DB provider for AS/400 and VSAM or the OLE DB provider for DB 2. |
|
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 |
Informs ADO that there is no specification of the type of command. This is the default, except when MSPersist serves as the provider. |
|
adCmdFile |
256 |
Evaluates a source argument for a recordset's Open method based on the filename for a persistent recordset. This is the default when MSPersist is the provider. This setting is not appropriate for Command objects. |
|
adCmdTableDirect |
512 |
Evaluates a command as a table name. Returns all columns in a table without any intermediate SQL code. Use with the Seek method for a recordset. |
The default setting for the CommandType property is adCmdUnknown. Changing the CommandType property from its default setting can speed up the operation of a command by reducing the need for ADO to call your provider to properly interpret a command's CommandText setting.
| Note |
You can further improve the performance of commands that do not return records (such as action queries) by adding adExecuteNoRecords to a command's CommandType specification. Be sure to always use adExecuteNoRecords, which has the value 128, with another setting, as shown here: cmd1.CommandType = adCmdText + adExecuteNoRecords |
The CommandText setting lets you designate what the command should do. You can assign a SQL string to define an operation for the command to perform. Use SQL syntax compatible with the provider for your command's ActiveConnection setting. You can also set the CommandText property to the name of a stored query or the name of a table. You should update the CommandType property to synchronize it with the setting of the CommandText property. For example, if a CommandText property points at a stored action query, designate adCmdStoredProc for the CommandType property.
If an application stays open for extended periods of time during which it executes a command repeatedly, you can improve the command's performance by using the Prepared property with a SQL statement. If the Prepared property is set to True, the SQL statement is compiled and saved on the database server the first time the statement is used. This slows the first execution of the command but speeds up subsequent ones.
The Execute method for a Command object invokes the code behind the Command object (for example, a stored query or a SQL statement). You can specify up to three optional arguments for the Execute method. The first argument allows the Command object to tell the procedure invoking it how many records it has affected. This argument pertains exclusively to action queries with Jet data sources or stored procedures on other types of databases, such as Microsoft SQL Server. (Use the RecordCount property on the resulting recordset to determine the number of records returned by a row-returning select query.) The second argument is a Variant array containing parameters to drive the command. This approach to designating parameters is for input parameters only. The third argument tells ADO how to evaluate the source. This argument can be any appropriate CommandTypeEnum member listed in Table 2-1, possibly combined with the adExecuteNoRecords intrinsic constant.
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 of a command. When running a parameter query, you must assign a value to the parameter. Using the CreateParameter method works for input parameters, output parameters, and return values from stored procedures. You can also make programs interactive at run time by using input from a user in a SQL string expression as the CommandText property for a Command object or the source argument for the Open method of a Recordset object. While many Access developers may find it somewhat more complicated to code parameters than SQL string expressions, I generally prefer working with parameters because it is more robust and secure. When invoking a Command object with parameters, the syntax automatically assures the input variable is of the right data type. Using a SQL string expression does not automatically provide such assurances. In addition, using parameters is more secure than using string inputs from users for a SQL string expression. This is because a user can input a constant string value that extends the SQL statement to perform tasks not originally intended for an application. This security issue is especially relevant to client/server databases with rich SQL languages, such as SQL Server.
| Note |
You can specify parameter values using either an array as an argument for the Execute method of a Command object or with parameter value assignment statements before invoking the Execute method. The Array argument for the Execute method overrides any prior settings for your parameter values. Output parameters do not return valid values in the Array argument for the Execute method. This issue only pertains to databases that can return output parameters. Jet is not such a database; SQL Server does offer output parameters. |
One of the most straightforward tasks you can perform with a Command object is to create a recordset based on a select query. The Execute method of the Command object runs the select query and returns the resulting recordset. The SelectCommand procedure that follows shows how to do this. This procedure has two parts: the first part creates the Command object and a connection for it to relate to a database, and the second part processes a recordset returned from the Command object.
| Note |
Before running the following procedure, make sure that MyTable in the Chapter02.mdb sample file contains some data (The Chapter02.mdb file is included with the companion content for this book). If the file is empty, run the InsertRecords procedure to populate MyTable with values automatically. |
Sub SelectCommand() Dim cmd1 As ADODB.Command Dim rst1 As ADODB.Recordset Dim str1 As String Dim fld1 As ADODB.Field 'Define and execute command Set cmd1 = New ADODB.Command With cmd1 .ActiveConnection = CurrentProject.Connection .CommandText = "SELECT MyTable.* FROM MyTable" .CommandType = adCmdText End With 'Set rst1 to the recordset returned by the command's 'Execute method, and print the recordset Set rst1 = cmd1.Execute Do Until rst1.EOF str1 = "" For Each fld1 In rst1.Fields str1 = str1 & fld1.Value & vbTab Next fld1 Debug.Print str1 rst1.MoveNext Loop 'Clean up objects rst1.Close Set fld1 = Nothing Set rst1 = Nothing Set cmd1 = Nothing End Sub
The first part of the procedure declares cmd1 as a Command object and then sets three critical properties. Every command must have an ActiveConnection property in order to run against a database. The Command object in this sample relies on a SQL statement to represent its select query. The third assignment in the With…End With block sets the CommandType property to adCmdText. You can substitute a saved query for the SQL statement, as shown in the following example:
.CommandText = "CustomerID" .CommandType = adCmdStoredProc
The second part of the procedure saves a reference to the recordset returned by the Execute method. This is necessary if you want to view the rows returned by the SQL string in the CommandText property because commands offer no methods for navigating through a recordset. Notice that you don't need to instantiate the Recordset object before assigning the return from the command's Execute method to it. The assignment instantiates the Recordset object. After saving a reference to the result, the procedure prints each record in the recordset with tab delimiters (vbTab) between fields in the Immediate window. The procedure can handle any number of columns in any number of rows.
The following code demonstrates the syntax for a parameter query. This procedure also has two major parts to its design. The parameter query in the first part has some extra lines of ADO code and a different SQL statement syntax from that of the previous select query. The second part of this procedure, which assigns the result of the command to a recordset and prints the recordset, is the same as that of the previous select query.
Sub ParameterQCommand()
Dim cmd1 As ADODB.Command
Dim rst1 As ADODB.Recordset
Dim str1 As String
Dim fld1 As ADODB.Field
Dim prm1 As ADODB.Parameter
Dim 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 2003"))
prm1.Value = int1
'Open recordset on return from cmd1 and print
'the recordset
Set rst1 = cmd1.Execute
Do Until rst1.EOF
str1 = ""
For Each fld1 In rst1.Fields
str1 = str1 & fld1.Value & vbTab
Next fld1
Debug.Print str1
rst1.MoveNext
Loop
'Clean up objects
rst1.Close
Set fld1 = Nothing
Set rst1 = Nothing
Set cmd1 = Nothing
End Sub
The SQL statement syntax uses a 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 results set. By themselves, these adjustments to the SQL syntax statement are not sufficient to make the parameter query work—you must create the parameter and append it to the command using ADO code.
To create the parameter, invoke the CreateParameter method. The previous code 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 the input/output direction for the parameter. The adParamInput intrinsic 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. These other parameter types are appropriate for databases other than Jet, such as SQL Server. After creating a parameter, you must append it to the Parameters collection of the command.
| Note |
When creating a string parameter with the CreateParameter method, you must specify the number of characters after designating the data type. The parameters denoting the number of characters should designate the largest possible size for a parameter. For example, if the DefinedSize of a field corresponds to a text data type column of up to 10 characters, then denote 10 as the number of characters for the parameter. It is not necessary to designate the length of numeric data types. |
After adding a parameter, you must assign a value to it to make the command's parameter query function properly. The previous code uses an InputBox function to gather input from a user. The procedure then invokes the Command object's Execute method in an assignment statement to save a reference to the resulting recordset so that it can be printed.
As mentioned earlier, you can use the Command object to delete, update, and add records to a data source. Command objects offer a programmatic means to maintain a data source. The DeleteARecord and DeleteAllRecords procedures that follow prune records from a record source, such as the MyTable table in Chapter02.mdb. The DeleteARecord procedure removes at most a single row from the table with the Column1 value equal to 13. The DeleteAllRecords procedure removes all rows from the table no matter what values they have. These samples designate the record source and the criteria for selecting records using a SQL DELETE statement.
With the Microsoft Access Database window's query designer, you can graphically design a query and then copy the code from the query's SQL view to the CommandText property of a command. Normally, it is possible to improve the readability of the SQL code from the Access query designer by editing it slightly. For example, examine the SQL statement for extra parentheses that you can remove. If your query operates on a single table, you can remove the table prefix shown before field names. Compare for readability the following SQL statements with those that you generate automatically with the Access query designer.
Sub DeleteARecord() Dim cmd1 As ADODB.Command Set cmd1 = New ADODB.Command With cmd1 .ActiveConnection = CurrentProject.Connection .CommandText = "DELETE Column1 FROM " & _ "MyTable WHERE 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 * FROM MyTable" .CommandType = adCmdText .Execute End With End Sub
When you develop an application, you might want to delete all the records from a table and then reset its contents. The InsertRecords procedure, shown next, 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 ADODB.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')" .Execute .CommandText = "INSERT INTO MyTable(Column1, " & _ "Column2, Column3) VALUES (7,8,'9')" .Execute .CommandText = "INSERT INTO MyTable(Column1, " & _ "Column2, Column3) VALUES (10,11,'12')" .Execute .CommandText = "INSERT INTO MyTable(Column1, " & _ "Column2, Column3) VALUES (13,14,'15')" .Execute .CommandText = "INSERT INTO MyTable(Column1, " & _ "Column2, Column3) VALUES (16,17,'18')" .Execute End With End Sub
The general elements of the InsertRecords procedure are shared with other applications of the Command object and do not depend on the design of a particular table. You must create a reference to the Command object and set its ActiveConnection property. Three lines are required for each row that you add to a recordset: 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 (though you don't need to reset CommandType unless you are changing the type). If you specify an updateable dynaset as the target, these steps can concurrently add records to two or more tables at the same time. When you add records to a table, you must consider the field data types. In the previous code, the CommandText settings are tailored specifically for the structure of the MyTable table. You can determine the data types for the columns in MyTable by running the FieldNameType sub procedure described at the end of Chapter 1. Just change the connection string so that it points to the Access database file holding MyTable, Chapter02.mdb. If MyTable is in the current database, you can denote the connection with the expression CurrentProject.Connection. The first two columns in MyTable have Long Integer data types, and the third column has a Text data type.
The syntax of the CommandText SQL statement in this sample has three parts. (This syntax is not available from the SQL view of the Access query designer.) First, the statement 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 list the field names, the values in the next step will be appended in sequential order, which could be a problem if the data source design changes over time. Third, the VALUES keyword appears before the field values for the new record.
You will frequently want to add a new row to a table with some values that you acquire from another source, such as a form. The following code shows two procedures. The first procedure assigns values to three string variables and invokes the second procedure, which actually inserts the values into the table. In actual practice, you can assign the strings in the first procedure's value from a form or even with an InputBox function. The second procedure splices the string values into the INSERT INTO SQL statement. While we could make this more complicated by using parameters, this simple design gets the job done. Invoking a parameter query lets users type input values for the parameters (for example, with an InputBox function). The simple design of the following procedure uses string data types for all fields on input and then splices them into the INSERT INTO statement so that strings for numeric fields become numbers and others remain strings.
Sub CallInsertARecord()
Dim str1 As String
Dim str2 As String
Dim str3 As String
str1 = "19"
str2 = "20"
str3 = "21"
InsertARecord str1, str2, str3
End Sub
Sub InsertARecord(str1 As String, _
str2 As String, str3 As String)
Dim cmd1 As ADODB.Command
Set cmd1 = New Command
With cmd1
.ActiveConnection = CurrentProject.Connection
.CommandText = "INSERT INTO MyTable(Column1, " & _
"Column2, Column3) VALUES (" & str1 & "," & _
str2 & ",'" & str3 & "')"
.CommandType = adCmdText
.Execute
End With
End Sub
The OddToEven and EvenToOdd procedures that follow update the data source values of Column1 using the Command object. Make sure that MyTable has row values before running the procedures in this section. If MyTable does not have row values, invoke both the InsertRecords procedure and the CallInsertARecord procedure.
Notice from the InsertRecords procedure that the Column1 values alternate between odd and even numbers. The first row has the value 1 in Column1, and the second row has the value 4 in Column1. The rows of Column1 continue to alternate between odd and even values throughout MyTable. The procedures in this next code sample use this information to manage the contents of the table.
Sub OddToEven() Dim cmd1 As ADODB.Command Dim intRowsChanged As Integer Set cmd1 = New ADODB.Command With cmd1 .ActiveConnection = CurrentProject.Connection .CommandText = "UPDATE MyTable SET Column1 = " & _ "Column1+1 WHERE ((-1*(Column1 Mod 2))=True)" .CommandType = adCmdText .Execute intRowsChanged MsgBox intRowsChanged & " rows were affected.", _ vbInformation, "Programming Microsoft Access Version 2003" End With End Sub Sub EvenToOdd() Dim cmd1 As ADODB.Command Dim intRowsChanged As Integer Set cmd1 = New ADODB.Command With cmd1 .ActiveConnection = CurrentProject.Connection .CommandText = "UPDATE MyTable SET Column1 = " & _ "Column1-1 WHERE ((-1*(Column2 Mod 2))=False)" .CommandType = adCmdText .Execute intRowsChanged MsgBox intRowsChanged & " rows were affected.", _ vbInformation, "Programming Microsoft Access Version 2003" End With End Sub
The overall design of these procedures should be familiar to you by now. The most significant difference between these code samples and the ones you saw earlier in the chapter 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 where the Column1 value is odd. The UPDATE part of the syntax adds 1 to the value to convert it from an odd number to an even number. The statement invoking the Execute method takes advantage of the method's first argument to determine the number of rows that the command changes. A simple MsgBox function reports the result to the user.
The EvenToOdd procedure reverses the effect of running the OddToEven procedure. The latter 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.
|
|