Special Edition Using Microsoft Office Access 2003

Using the ADODB.Command Object

The primary purpose of the Command object is to execute parameterized stored procedures, either in the form of the default temporary prepared statements or persistent, precompiled T-SQL statements in SQL Server databases. MSDE and SQL Server create temporary prepared statements that exist only for the lifetime of the current client connection. Precompiled SQL statements are procedures stored in the database file; their more common name is stored procedure. When creating Recordset objects from ad hoc SQL statements, the more efficient approach is to bypass the Command object and use the Recordset.Open method.

Command Properties

The Command object has relatively few properties, many of which duplicate those of the Connection object. Table 30.9 lists the names and descriptions of the Command object's properties. Like the Connection object, the Command object has its own provider-specific Properties collection, which you can print to the Immediate window using statements similar to those for Command objects described in the earlier "Provider-Specific Properties and Their Values" section.

Tip

The Command object is required to take advantage of ADO 2.6+'s Stream object, which contains data in the form of a continuous stream of binary data or text. Text streams often contain XML documents or document fragments returned from SQL Server 2000 XML AUTO queries. The Microsoft OLE DB Provider for Internet Publishing (MSDAIPP) enables Connection, Recordset, Record, and Stream objects to bind to a URL and retrieve data into a Stream object. Windows XP/2000+'s Internet Information Server (IIS) 5.0+ adds the MSDAIPP provider.

Tip

Always set the CommandType property to the appropriate adCmd... constant value. If you accept the default adCmdUnknown value, the data provider must test the value of CommandText to determine whether it is the name of a stored procedure, a table, or an SQL statement before executing the query. If the targeted database contains a large number of objects, testing the CommandText value for each Command object you execute can significantly reduce performance.

The initial execution of a prepared statement often is slower than for a conventional SQL query because some data sources must compile, rather than interpret, the statement. Thus, you should limit use of prepared statements to parameterized queries in which the query is executed multiple times with differing parameter values.

Parameters Collection

To supply and accept parameter values, the Command object uses the Parameters collection, which is similar to the DAO and ODBCDirect Parameters collections. ADODB.Parameters is independent of its parent, ADODB.Command, but you must associate the Parameters collection with a Command object before defining or using Parameter objects.

The Parameters collection has a read-only Long property, Count, an Item property that returns a Parameter object, and the methods listed in Table 30.10. The syntax for the Count and Item properties property is

lngNumParms = cmmName.Parameters.Count prmParamName = cmmName.Parameters.Item(lngIndex)

You gain a performance improvement for the initial execution of your stored procedure or query if you use the cmmName.CreateParameter method to predefine the required Parameter objects. The Refresh method makes a round-trip to the server to retrieve the properties of each Parameter.

Parameter Object

One Parameter object must exist in the Parameters collection for each parameter of the stored procedure, prepared statement, or parameterized query. Table 30.11 lists the property names and descriptions of the Parameter object. The syntax for getting and setting Parameter property values is

typPropValue = cmmName.Parameters({strName|lngIndex}).PropertyName cmmName.Parameters({strName|lngIndex}).PropertyName = typPropValue

You don't need to use the Index property of the Parameters collection; Index is the default property of Parameters.

The Type property has the largest collection of constants of any ADO enumeration; you can review the entire list of data types by selecting the DataTypeEnum class in Object Browser. Most of the data types aren't available to VBA programmers, so Table 30.14 shows only the most commonly used DataTypeEnum constants. In most cases, you only need to choose among adChar (for String values), adInteger (for Long values), and adCurrency (for Currency values). You use the adDate data type to pass Date/Time parameter values to Jet databases, but not to most stored procedures. Stored procedures generally accept datetime parameter values as the adChar data type, with a format, such as mm/dd/yyyy, acceptable to the RDBMS.

Note

The Parameter object has a single method, AppendChunk, which you use to append long text (adLongText) or long binary (adLongVarBinary) Variant data as a parameter value. The syntax of the AppendChunk method call is

cmmName. Parameters({strName|lngIndex}).AppendChunk = varChunk

The adParamLong flag of the prmName. Attributes property must be set to apply the AppendChunk method. If you call AppendChunk more than once on a single Parameter, the second and later calls append the current value of varChunk to the parameter value.

Command Methods

Command objects have only three methods: Cancel, CreateParameter and Execute. Executing Command.Cancel terminates an asynchronous command opened with the adAsyncConnect, adAsyncExecute, or adAsyncFetch option.

You must declare an ADODB.Parameter object, prmName, prior to executing CreateParameter. The syntax of the CreateParameter method call is

Set prmName = cmmName.CreateParameter [strName[, lngType[, _ lngDirection[, lngSize[, varValue]]]]] cmmName. Parameters.Append prmName

The arguments of CreateParameter are optional only if you subsequently set the required Parameter property values before executing the Command. For example, if you supply only the strName argument, you must set the remaining properties, as in the following example:

Set prmName = cmmName. CreateParameter strName cmmName. Parameters.Append prmName With prmName .Type = adChar .Direction = adParamInput .Size = Len(varValue) .Value = varValue End With

The syntax of the Command.Execute method is similar to that for the Connection.Execute method except for the argument list. The following syntax is for Command objects that return Recordset objects:

Set rstName = cmmName.Execute([lngRowsAffected[, _ avarParameters[, lngOptions]]])

For Command objects that don't return rows, use this form:

cmmName.Execute [lngRowsAffected[, avarParameters[, lngOptions]]]

All the arguments of the Execute method are optional if you set the required Command property values before applying the Execute method. Listing 30.2 later in this chapter gives an example of the use of the Command.Execute method without arguments.

TIP

Presetting all property values of the Command object, rather than supplying argument values to the Execute method, makes your VBA code easier for others to comprehend.

Like the Connection.Execute method, the returned value of lngRowsAffected is 0 for SELECT and DDL queries and the number of rows modified by execution of INSERT, UPDATE, and DELETE queries. (For SQL Server, lngRowsAffected is 0 if the SQL statement includes SET NOCOUNT ON.) The avarParameters argument is an optional Variant array of parameter values. Using the Parameters collection is a better practice than using the avarParameters argument because output parameters don't return correct values to the array. For lngOptions constant values, refer to Table 30.7.

Code to Pass Parameter Values to a Stored Procedure

Most stored procedures that return Recordset objects require input parameters to supply values to WHERE clause criteria to limit the number of rows returned. The code of Listing 30.2 executes a simple SQL Server 2000 stored procedure with a Command object. The Sales by Year stored procedure of the NorthwindCS project has two datetime input parameters, @Beginning_Date and @Ending_Date, the values for which are supplied by strBegDate and strEndDate, respectively. The stored procedure, whose SQL statement follows, returns the ShippedDate and OrderID columns of the Orders table, the Subtotal column of the Order Subtotals view, and a calculated Year value. The stored procedure returns rows for values of the OrderDate field between strBegDate and strEndDate.

ALTER PROCEDURE "Sales by Year" @Beginning_Date datetime, @Ending_Date datetime AS SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal, DATENAME(yy,ShippedDate) AS Year FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date

Listing 30.2 Code Using a Command Object to Execute a Parameterized Stored Procedure

Option Explicit Option Compare Database Private cnnOrders As New ADODB.Connection Private cmmOrders As New ADODB.Command Private prmBegDate As New ADODB.Parameter Private prmEndDate As New ADODB.Parameter Private rstOrders As New ADODB.Recordset Private Sub Form_Load() Dim strBegDate As String Dim strEndDate As String Dim strFile As String strBegDate = "1/1/1997" strEndDate = "12/31/1997" strFile = CurrentProject.Path & "Orders.rst" 'Specify the OLE DB provider and open the connection With cnnOrders .Provider = "SQLOLEDB.1" On Error Resume Next .Open "Data Source=(local);" & _ "UID=sa;PWD=;Initial Catalog=NorthwindCS" If Err.Number Then .Open "Data Source=(local);" & _ "Integrated Security=SSPI;Initial Catalog=NorthwindCS" End if On Error GoTo 0 End With With cmmOrders 'Create and append the BeginningDate parameter Set prmBegDate = .CreateParameter("BegDate", adChar, _ adParamInput, Len(strBegDate), strBegDate) .Parameters.Append prmBegDate 'Create and append the endingDate parameter Set prmEndDate = .CreateParameter("EndDate", adChar, _ adParamInput, Len(strEndDate), strEndDate) .Parameters.Append prmEndDate Set .ActiveConnection = cnnOrders 'Specify a stored procedure .CommandType = adCmdStoredProc 'Brackets must surround stored procedure names with spaces .CommandText = "[Sales By Year]" 'Receive the Recordset Set rstOrders = .Execute 'returns a "firehose" Recordset End With With rstOrders 'Save (persist) the forward-only Recordset to a file On Error Resume Next 'Delete the file, if it exists Kill strFile On Error GoTo 0 .Save strFile .Close .Open strFile, "Provider=MSPersist", , , adCmdFile End With 'Assign rstOrders to the Recordset of the form Set Me.Recordset = rstOrders Me.txtShippedDate.ControlSource = "ShippedDate" Me.txtOrderID.ControlSource = "OrderID" Me.txtSubtotal.ControlSource = "Subtotal" Me.txtYear.ControlSource = "Year" End Sub

Caution

When used in ADO code, you must enclose names of stored procedures and views having spaces with square brackets. Including spaces in database object names, especially in client/server environments, isn't a recommended practice. Microsoft developers insist on adding spaces in names of views and stored procedures, perhaps because SQL Server 2000 supports this dubious feature. Use underscores to make object names more readable if necessary.

Note

The code of Listing 30.2 uses an ADO 2.5+ feature, persisted (saved) Recordset objects. Stored procedures return forward-only ("firehose") Recordset objects, which you can't assign to the Recordset property of a form. To create a Recordset with a cursor acceptable to Access forms, you must persist the Recordset as a file and then close and reopen the Recordset with the MSPersist OLE DB provider as the ActiveConnection property value. The "Recordset Methods" section, later in the chapter, provides the complete syntax for the Save and Open methods of the Recordset object.