ADO object model
Each Connection, Command, Recordset, and Field objects have a Properties collection.
Command object
The Command object defines the specified commands that will be performed on the data source.
Description
Use the Command object to query the database and return a record in the Recordset object to perform a large number of operations or processes the database structure. Depending on the functionality of the provider, some Command collections, methods, or attributes may generate errors when they are referenced.
You can use the collection, method, and attribute of the Command object.
Use the CommandText property to define the executable of the command (for example, SQL statement). Define parameterized query or stored procedure parameters through Parameter objects and Parameters set. You can use the Execute method to execute the command and return the Recordset object when appropriate. Before execution, use the CommandType property to specify the command type to optimize performance. Using the Prepared property determines if the provider saves the ready (or compiled) command version before execution. Set the number of seconds that the provider waited for the command using the COMMANDTIMEOUT property. The open connection is associated with the Command object by setting the ActiveConnection property. Set the Name property to identify the Command as a method associated with the Connection object. Transfer your COMMAND object to the Source property of the Recordset to get the data.
Note If you do not want to use a Command object to perform a query, transfer the query string to the EXECUTE method of the Connection object or the OPEN method for the Recordset object. However, when you need to make the command text with persistence and re-execute it, or when using the query parameter, you must use the Command object.
To create a Command object independently of the previously defined Connection object, set its ActiveConnection property to a valid connection string. The ADO will still create a Connection object, but it does not assign the object to the object variable. However, if you are associating multiple Command objects with the same connection, you must explicitly create and open the Connection object so that the Connection object can be assigned to the object variable. If the ActiveConnection property of the Command object is not set to the object variable, the ADO will create a new Connection object for each Command object even if the same connection string is used.
To perform Command, simply call it by simply calling the Name property of the Connection object it associated. The Command's ActiveConnection property must be set to the Connection object. If the Command has parameters, the value of these parameters is transmitted to the method.
If two or more Command objects are performed on the same connection, and a Command object is a stored procedure with output parameters, an error occurs. To perform individual Command objects, use separate connections or disconnect all other Command objects.
Connection object
The Connection object represents the connection to the data source.
Description
The Connection object represents the only session that is performed with the data source. If it is a client / server database system, the object is equivalent to the actual network connection to the server. Depending on the functions supported by the provider, certain collections, methods, or attributes of the Connection object may be invalid.
Use the collection, method, and attributes of the Connection object to do the following:
Connect the connection before opening the connection. Connect the connection. Set the CURSORLocation property to call the "Client Tour Provider" that supports the batch update. Use the defaultDatabase property to set the default database for the connection. Use the ISOLATIONLVEL attribute to set the isolation level on the transaction on which the connection is opened. Use the Provider property to specify the OLE DB provider. Use the Open method to establish a physical connection to the data source. Disconnect it using the Close method. Use the Execute method to execute the connection to the connection and configure it using the CommandTimeout property using the COMMANDTIMEOUT property. You can use the BEGINTRANS, COMMITTRANS, and ROLLBACKTRANS methods and attributes property to manage transactions on the open connection (if the provider includes nested transactions). Check the errors returned by the data source using an Errors collection. Read the ADO execution version in use through the Version property. Use the OpenSchema method to get database mode information. Note If you do not use a Command object, send a query string to the Execute method of the Connection object. However, when you need to make the command text with persistence and re-execute, or use the query parameter, you must use the Command object.
You can create a Connection object of any other object independent of previously defined objects.
Note that commands or stored procedures can be performed like the local method that performs the Connection object.
If you want to execute a command, you can specify a name to the command using the Name property of the Command object. Set the ActiveConnection property of the Command object to that connection. Then, like a statement using the command name like a method of issuing a Connection object, which can be taken with any parameters (if there is a return line, then with the Recordset object). Set the RecordSet property to customize the generated recordset. E.g:
DIM CNN As New Adodb.Connection
DIM CMD As New AdoDb.command
DIM RST As New Adodb.Recordset
...
CNN.Open "..."
cmd.name = "YourCommandName"
cmd.activeConnection = CNN
...
'Command Name, any parameter, and optional record set.
CNN.YourCommandname "parameter", RST
To perform a stored procedure, you can issue a statement using the stored procedure name as the method of issuing a Connection object, which can take any parameters. ADO will "best judgment" in the parameter type. E.g:
DIM CNN As New Adodb.Connection
...
'Store process name and any parameters.
CNN.sp_YourStoredProcedureName "parameter"
