Command

The command object is used to execute a single query against the database.
The query can perform actions like creating, adding, retrieving, deleting or updating records.
When data is retrieved it is returned as a Recordset object
Using the Command object also gives you the ability to pass parameters to your query.

Creating a Command

dbADOCommand = New ADODB.Command

Note: You must use the command.execute method to substitute the parameters for the "?" placeholders

Command.Execute [ RecordsAffected ] [, Parameters ] [, Options]
RecordsAffected -
Parameters -
Options -

Command.CreateParameter [Name] [,Type][, Direction][, Size][, Value]
Name - Name of the parameter
Type - The data type of the parameter. If you choose a variable length data type you must provide a size in the size parameter. The default is adEmpty
Direction -
Size - Only necessary if the datatype is variable length
Value -

Command.Parameters.Append [ ..............]

Connection Properties and Methods

Name	Type	Description
CommandText	Property	Sets or returns a provider command
CommandType	Property	Sets or returns the type of the command object
Cancel	Method	Cancels an execution of a method
ActiveConnection	Property	Sets or returns the definition for a connection
CommandTimeOut	Property	Sets or returns the number of seconds to wait while attempting to execute the command
Prepared	Property	Sets or returns
Execute	Method	Executes the query, SQL statement or procedure in the CommandText property
CreateParameter	Method	Creates a new Parameter object. This is not automatically added to the parameters collection and must be done seperately using the Parameters.Append property
Name	Property	Sets or returns the name of the command object
State	Property	Returns a value indicating if the command is open, closed, connecting, executing or retrieving

Note: If you specify an adDecimal or adNumeric data type you will also have to set the NumericScale and Precision properties of the Parameter object

Command Type	adCmdUnknown (8)
	adCmdText (1) - source is an SQL statement
	adCmdTable (default) (2) - source is a table name (analgous to a DAO dynaset record)
	adCmdTableDirect (512) - source is a table name (analygous to a DAO dbpentable recordset
	adCmdStoredProc (4) - source is a stored procedure or function (analgous to a DAO dynaset on a sorted query)
	adCmdFile (256) - source is a name of a file that has persisted
	adExecuteNoRecords - source is a command or stored procedure that does not return rows
??	adSyncConnect
	adSyncExecute
	adSyncFetch
	adSyncFetchNonBlocking

Example - Executing a SQL Statement

When returning a single value from a SQL statement use the To_Char() function to ensure that you return a string

Public dbADOConnect as ADODB.Connection 
Public dbADOCommand as ADODB.Command 
Public dbADORecordset as ADODB.Recordset 
Public sSQLQuery as String 
Public sConnectionStr as String 

sConnectionStr = "------------------------" 

Set dbADOConnect = New ADODB.Connection       
dbADOConnect.Open sConnectionStr, sUserID, sPassword, adConnectUnspecified 
dbADOConnect.CursorLocation = ADODB.adUseClient 

Set dbADOCommand = New ADODB.Command      

sSQLQuery = "-------------------------" 

dbADOCommand.CommandText = sSQLQuery 
dbADOCommand.CommandType = ADODB.dbCmdText 

Set dbADORecordSet = New ADODB.Recordset            
Set dbADORecordSet = dbADOCommand.Execute 

Set dbADOCommand = Nothing 
Set dbADORecordset = Nothing 
Set dbADOConnect = Nothing

Example - A Dynamic Cursor batching any changes to the Recordset)

dbADORecordSet = New ADODB.Recordset 
dbADORecordSet.Open dbADOCommand, _ 
                      dbADOConnection, _ 
                      adOpenDymanic, _ 
                      adLockBatchOptimistic