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

CommandTextPropertySets or returns a provider command
CommandTypePropertySets or returns the type of the command object
CancelMethodCancels an execution of a method
ActiveConnectionPropertySets or returns the definition for a connection
CommandTimeOutPropertySets or returns the number of seconds to wait while attempting to execute the command
PreparedPropertySets or returns
ExecuteMethodExecutes the query, SQL statement or procedure in the CommandText property
CreateParameterMethodCreates a new Parameter object. This is not automatically added to the parameters collection and must be done seperately using the Parameters.Append property
NamePropertySets or returns the name of the command object
StatePropertyReturns 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 TypeadCmdUnknown (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

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, _

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext