These commands can be action queries, such as updates, inserts or deletes, as well as commands that return a recordset. You might wonder what use this is, since we have the Recordset object, but it's all part of the flexibility of ADO, that allows you to use whichever object is the most convenient, and most suited to the task in hand.

The commands run from the Connection object are generally action queries, but it's useful to know that you can get recordsets returned too.

Returning a Recordset

To return a recordset from the Connection object you use the Execute method. The syntax of this method is:

Connection.Execute CommandText, [RecordsAffected], [Options] 

The arguments are:

The Execute method optionally returns a recordset, in which case you simply assign the Recordset variable as the return value. For example:

Set conPubs = Server.CreateObject("ADODB.Connection")
conPubs.Open strConn
Set rsAuthors = conPubs.Execute ("Authors")

You might wonder what the difference is between using the Execute method of the Connection object and the Open method of the Recordset object. It may not seem that there's much difference, but remember that with the Open method of the Recordset you have the ability to change the cursor type and lock type of the resulting recordset. These options are not available for the Execute method of the connection, so you will always get a forward-only, read-only recordset.

Action Commands

If you are running action commands, such as a SQL UPDATE statement, then you can use the RecordsAffected argument to find out how many records were affected by the command. For example:

Dim strSQL As String
Dim lngRecs As Long

strSQL = "UPDATE Titles SET Price = Price * 1.10" & _
    " WHERE Type='Business'"

conPubs.Execute strSQL, lngRecs, adCmdText

Response.Write lngRecs & " records were updated."

This updates the price for all business books by 10%. Once the Execute command has completed, the number of titles affected by the update will be available in lngRecs - this is the RecordsAffected argument.

Notice that we've specified adCmdText for the options of this command - this tells ADO that the command text is a text command. Whilst this isn't strictly necessary, it does allow ADO to know ahead of time what sort of command is being executed, and therefore improves performance. Remember that this value can be one or more of the values from the CommandTypeEnum values.

No Recordset Returned

If no recordset is being returned, as in the example above, then it's also best to add another option to the Execute statement:

conPubs.Execute strSQL, lngRecs, adCmdText + adExecuteNoRecords

Using adExecuteNoRecords tells ADO that the command being executed does not return any records. ADO therefore doesn't bother building a recordset. If you omit this option then ADO builds an empty recordset, which is clearly a waste of time, so adding the option will speed up the statement.