The Command Object The Command object is designed specifically to deal with commands of any sort, but especially those that require parameters. Like the Connection object, the Command object can run both commands that return recordsets as well as those that don't. In fact, if your command doesn't have parameters, then it really doesn't matter whether you use a Connection, a Command, or a Recordset.
For a recordset-returning command you would use the Execute method. However, unlike the Connection object, you do not specify the command text in the Execute method itself - you have to use the CommandText property:
This is the simplest way to tell the Command object to run a simple command that returns a read-only recordset.
The Execute method also has some optional arguments:
The RecordsAffected and Options are as previously explained, although you can set the command type by using the CommandType property:
You can also set this on the Execute line itself - if you're not setting the other arguments though, you must still include the commas for them:
We'll look at the use of the Parameters argument a little later in the chapter, when we deal with stored procedures and parameters.
Changing the Cursor Type
One important thing to note about recordsets that are returned from the Execute method is that they have the default cursor and lock types. This means they are forward-only, read-only recordsets. There's no way to change this using the Execute method, but there is a way around this problem.
If you need to use a command, and require a different cursor or lock type, then you should use the Open method of the Recordset, but use the Command as the source of the Recordset. For example:
Notice that the connection details on the Open line have been omitted, because the connection is set in the Command. The connection details could also have been set in the ActiveConnection property of the Command before it is opened.
For action commands, such as those that update data without returning any records, the procedure is similar - you just leave off the bits that set the recordset:
Notice that we set the command type in one place and then add any extra options in the Execute line. This runs the UPDATE command and ensures that no recordset is created.