The Execute Method

This method executes the command text provided to its CommandText argument. The Execute method has the following syntax for an action query (one that does not return a resultset):

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

And for a select query:

Set Recordset = connection.Execute(CommandText, _

[RecordsAffected], [Options])

The CommandText argument can contain any executable string recognized by the OLE DB provider. However, it will most commonly contain a SQL statement. The optional RecordsAffected argument is a return value that tells you how many records the CommandText operation has affected. It's a good idea to check this value against the number of records that you expected to be affected, so you can detect potential errors in your command text.

The Options argument is crucial to optimizing the execution efficiency of your command. Therefore, you should always use it even though it's nominally optional. The Options argument allows you to relay two different types of information to your OLE DB provider: what type of command is contained in the CommandText argument, and how the provider should execute the contents of the CommandText argument.

To execute the CommandText, the OLE DB provider must know what type of command it contains. If you don't specify the type, the provider will have to determine that information for itself. This will slow down the execution of your query. You can avoid this by specifying the CommandText type using one of the following CommandTypeEnum values:

□ adCmdText — The CommandText is a raw SQL string.

□ adCmdTable —The CommandText is the name of a table. This sends an internally generated SQL statement to the provider that looks something like "SELECT * FROM table_name".

□ adCmdStoredProc — The CommandText is the name of a stored procedure (stored procedures are covered in the section "Using ADO with Microsoft SQL Server").

□ adCmdTableDirect — The CommandText is the name of a table. However, unlike adCmdTable, this option does not generate a SQL statement and therefore returns the contents of the table more efficiently. Use this option if your provider supports it.

You can provide specific execution instructions to the provider by including one or more of the ExecuteOptionEnum constants:

□ adAsyncExecute —Tells the provider to execute the command asynchronously, which returns execution to your code immediately.

□ adExecuteNoRecords —Tells the provider not to construct a Recordset object. ADO will always construct a recordset in response to a command, even if your CommandText argument is not a row-returning query. To avoid the overhead required to create an unnecessary recordset, use this value in the Options argument whenever you execute a non-row-returning query.

The CommandTypeEnum and ExecuteOptionEnum values are bit masks that can be combined together in the Options argument using the logical Or operator. For example, to execute a plain text SQL command and tell ADO not to construct a Recordset object, you would use the following syntax:

szSQL = "DELETE FROM Customers WHERE CustomerID = 'XXXX'" objConn.Execute szSQL, lNumAffected, adCmdText Or adExecuteNoRecords If lNumAffected <> 1 Then MsgBox "Error executing SQL statement."

0 0

Post a comment