The ActiveConnection property specifies the Connection object for the current Command object.

String or Variant (containing the current Connection object)

The ActiveConnection property can be set to either a String, representing a connection string, or a Variant, representing an open Connection object.

The ActiveConnection property can be set to either a String or a reference to a Connection object. If a String value is used, then it should be a valid connection string that could be passed to the ConnectionString property of a Connection object. You must set the ActiveConnection property before you attempt to execute a Command object. Before it is set, the value of the ActiveConnection property is a Null object reference, by default.

If you set the ActiveConnection property to a closed Connection object, ADO will generate an error. If you set it to Nothing, ADO will disassociate the Command object from the Connection object and release any resources that are associated with the data source. If the ActiveConnection property is set to either Nothing or a different Connection object, those Parameter objects that were supplied by the data provider, residing in the Command's Parameters collection class, will be lost. However, those Parameter objects that were manually added to the Parameters collection class will not.

If you close the Connection object associated with a Command object, the Command object's ActiveConnection will automatically be set to Nothing.

Connection.ConnectionString Property

The Cancel method cancels the pending asynchronous command that is executing.

The Cancel method cancels an asynchronous executing command that was initiated with the Execute method using the adAsyncExecute option.

If the Cancel method is called on a command that was not executed with the adAsyncExecute option set, an error will occur.

Command.Execute Method

The CommandStream property sets or returns the data provider-specific stream that is used as the Command's input just as the CommandText property identifies the command to use as the input.

Variant

The CommandStream property is used to specify the stream used as a Command's input. If this property is set, then the CommandText property is set to an empty String ("). Similarly, if the CommandText property is set, the CommandStream property is set to Nothing.

If a Command object whose source is a stream is used to create a Recordset object, reading the Recordset object's Source property would return an empty String ("), because it returns the value of the Command object's CommandText property.

You must use either the adCmdText or adCmdUnknown enumeration values for the CommandType property if the CommandStream property is set.

The format of the stream being set to the CommandStream property is data provider-specific, as is the behavior of both the Command.Parameters.Refresh and Command.Prepare methods.

Command.CommandText Property, Comand.CommandDialect Property, CommandTypeEnum Enumeration, Recordset.Source

The CommandText property sets or returns a valid SQL statement, table name, stored procedure name, relative URL, or any other command text that the data provider may recognize.

String

The CommandText property contains a String value that by default contains a zero-length string ("). This string can be set to a SQL statement, table name, stored procedure call, relative URL, or any other command text that the data provider may recognize.

The CommandType property is used to indicate to ADO what type of information resides within the CommandText property. If the CommandType property is set to adCmdText, it indicates to the provider that the CommandText property value contains a text command that the provider will understand. This will usually be a SQL statement, but not necessarily.

If you set the CommandText property, ADO prepares the specified query on the Execute or Open methods whenever the Prepared property of the Command object is set to True, and it is bound to an open connection.

The value of CommandText may be changed by ADO, depending on the value of the CommandType property. Retrieve the value of the CommandText property at any time if you question its value.

If the CommandStream property is set to a value, then the CommandText property is automatically set to an empty string (").

Command.CommandStream, Command.CommandType Property, Command.Dialect Property, Command.Prepared Property

The CommandTimeout property indicates how long ADO waits, in seconds, before it generates an error when executing an asynchronous command.

Long

The CommandTimeout property is read- and write-enabled. With the CommandTimeout property, you can specify how long ADO will wait for a command to execute. The setting for the CommandTimeout property is represented in seconds, and the default value is 30. By setting this property to zero, you are allowing ADO to wait indefinitely for a specified command to execute. If a command does timeout, an error is generated.

The CommandTimeout property of the Command object is unrelated to the CommandTimeout property of the Connection object.

Not all providers support the CommandTimeout property. Check your data provider's documentation to see if it supports this property. When developing an application with ADO, it might be a good idea to go as far as checking the capabilities of your planned data provider to see whether it matches your needs before you write most of your code -- and it becomes too late.

Command.Execute Method, Connection.CommandTimeout Property

The CommandType property indicates the current type of Command object.

CommandTypeEnum

The CommandType property indicates the type of Command object you are using. The value of this property can be set to one of the CommandTypeEnum enumeration values. If the source of the Command object is a stream (set via the Command.CommandStream property), the CommandType property must be set to either adCmdText or adCmdUnknown.

In addition, the ExecuteOptionEnum value of adExecuteNoRecords can be combined with the adCmdText or adCmdStoredProc constants to improve performance. This value cannot be used with the Open method of the Recordset object.

If you know the type of Command object that you are using, set this property manually to prevent unwanted delays in performance when ADO tries to figure it out for itself. If the property is set incorrectly, an error is generated upon a call to the Execute method.

Command.CommandStream, Command.CommandText Property, CommandTypeEnum Enumeration

The CreateParameter method creates and returns a new parameter with the information supplied.

Name (String)

Optional. Contains the desired name of the new parameter. This argument is mapped to the Parameter.Name property.

Type (DataTypeEnum)

Optional. Indicates the desired datatype of the new parameter. This argument is mapped to the Parameter.Type property. The default value is adEmpty.

Direction (ParameterDirectionEnum)

Optional. Indicates the desired direction of the new parameter. This argument is mapped to the Parameter.Direction property. The default value is adParamImput.

Size (Long)

Optional. Specifies the maximum length of the newly created parameter in either characters or bytes if the Type parameter is set to a variable-length datatype. This argument is mapped to the Parameter.Size property.

Value (Variant)

Optional. Is used to initialize the value of the newly created Parameter object. This argument is mapped to the Parameter.Value property.

Parameter object

With the CreateParameter method of the Command object, you can create a new Parameter object with the information that you specify, including name, type, direction, size, and value. When you create a new Parameter object with this method, the parameter is not automatically added to the Parameters collection of the chosen Command object. The Append method of the Command.Parameters property must be used to do this.

When assigning variable-length datatypes, you must set the Size property at either creation or some other time before appending it to the Parameters collection to avoid an error.

If the Type property is set to either adNumeric or adDecimal, the Parameter.NumericScale and Parameter.Precision properties must be set to fully specify the parameter's datatype. For instance, a NumericScale of 3 would indicate that there are three digits after the decimal point and a Precision of 5 would mean that there are five digits, total, representing the number.

DataTypeEnum Enumeration, ParameterDirectionEnum Enumeration, Parameter.Direction Property, Parameter.Name Property, Parameter.NumericScale Property, Parameter.Precision Property, Parameter.Size Property, Parameter.Type Property, Parameter.Value Property

The Dialect property is used with the CommandText and the CommandStream properties to indicate the data provider-specific syntax of the Command's source.

String

The default value for the Dialect property is {C8B521FB-5CF3-11CE-ADE5-00AA0044773D}, which indicates to ADO that the provider will attempt to figure out how to interpret the Command's source specified in either the CommandStream or the CommandText property.

You must check your data provider's documentation to see what the valid values of the Dialect property must be specified as, depending upon the value in the CommandStream or CommandText properties.

Command.CommandStream Property, Command.CommandText Property

The Execute method executes a SQL statement, query, or stored procedure specified in the Command.CommandText property value.

RecordsAffected (Long)

Optional. Populated, by the data provider, with the number of records that were affected by the action SQL statement, query, or stored procedure (result-returning commands do not populate this parameter).

Parameters (Variant Array)

Optional. Contains the values to be passed to the SQL statement, query, or stored procedure for the parameters in which it holds.

Options (Long)

Optional. A combination of one or more CommandTypeEnum and ExecuteOptionEnum values indicating how the data provider should treat the command. The default value is -1 (no options set). The CommandTypeEnum values can also be set with the Command.CommandType property.

The Execute method executes a stored procedure, SQL statement, or query against a data source. The command that is executed is dependent upon the value of the Command.CommandText property. The way in which ADO evaluates this CommandText is dependent upon the Options parameter to the Execute method.

If the CommandText value causes the Execute method to return records from the data source, these records are returned in the form of a Recordset object from the Execute method. A returned Recordset object from the Command.Execute method is always a read-only, forward-only cursor.

When the Execute method's Options parameter includes the adAsyncExecute, adAsyncFetch, or adAsyncFetchNonBlocking values, operations continue in the background of the application flow. While these operations are continuing, the Command.Cancel method can be called to cancel all pending asynchronous operations.

Command.Cancel Method, Command.CommadText Property, Command.CommandType Property, CommandTypeEnum Enumeration, ExecuteOptionEnum Enumeration, ExecuteOptionEnum Enumeration

The Name property specifies the name for the current Command object.

String

The Name property is both read- and write-enabled for the Command object. The Name property is used to assign a name to the associated Command object or retrieve a Command object from the Commands collection.

Names do not have to be unique within collections.

The NamedParameters property indicates whether the data provider should use parameter names to match the Command object's parameters or if it should match them in the order that they appear.

Boolean

If the NamedParameters property is set to True, the name properties of each Parameter object in the current Command objects, Parameters collection, will be passed to the data provider so that they can be used to map the parameters to the values in either the CommandText or the CommandStream Property.

If this property is set to False, then the parameters are not matched by name, but rather by the order in which they appear.

Command.CommandText Property, Command.CommandStream Property, Parameters Collection

The Parameters collection returns a collection of parameters for the given Command object.

Parameters (Collection object)

Command object

The Parameters property of the Command object is read-only. It returns a reference to the Parameters collection object, which can contain zero or many Parameter objects that represent parameters for the given command.

The Prepared property indicates whether a prepared statement should be created by the current Command object before it is executed.

Boolean

If the value of the Prepared property is set to True, then before the first execution of a query defined by the CommandText property of the Command object, the query is compiled and saved. From then on the Execute statement will refer to the compiled version of the query to perform. This may slow the initial call to Execute, but all calls that follow will benefit from this.

If the value of the Prepared property is set to False, then the query is never compiled; instead the query is done directly from the Command object.

Not all providers support the Prepared property. Some providers raise an error as soon as the Prepared property is set to True, while others do not raise an error and simply replace the Prepared property's value with False.

The Properties property returns a Properties Collection class that contains characteristics specific to the Command object for the currently used provider.

Properties (Collection object)

Command object

The Properties collection class contains a Property class instance for each property specific to the Command object for the data provider.

The Properties collection of the Command object is not populated until the ActiveConnection property of the Command object has been set to an open Connection object or a valid connection string value.

Command.ActiveConnection Property

The Status property is used to determine the status of the current record when using batch updates or bulk operations.

RecordStatusEnum

The Type property is used to check the changes that are pending for records that have changed during a batch update. In addition, you can check the status of records that have failed during bulk operations such as what might happen with a call to the Delete, Resync, UpdateBatch, or CancelBatch methods or when a Filter property is set to an array of bookmarks.

The Type property returns a sum of one or more of the RecordStatusEnum enumerations.

Recordset.CancelBatch, Recordset.Delete Method, Recordset.Filter Property, Recordset.Resync Method, Recordset.UpdateBatch, RecordStatusEnum Enumeration, ObjectStateEnum Enumeration