The Execute Direct model assumes the following steps:
-
You make an on-the-fly request to the provider.
-
The provider interprets your request.
-
The provider executes the interpreted request.
-
The provider returns the result to you.
-
When you make the same request in the future, the provider reruns steps 2–5
again, re-interpreting the request each time.
In ADO, you can use the Execute Direct model to implement a request to a data
provider in one of several ways:
-
Call a Connection object's Execute method with a single argument that is the
text of the SQL statement that you want the provider to execute.
-
Call a Command object's Execute method with the text of the SQL statement
in the Command object's CommandText property.
-
Call a Recordset object's Open method with the text of the SQL statement.
Listing 9.1 shows examples of these different types of Execute Direct calls.
LISTING 9.1
EXAMPLES OF THE EXECUTE DIRECT MODEL 'INITIALIZING THE RECORDSET
WITH THE 'EXECUTE METHOD OF A CONNECTION OBJECT
'Assumes connNWind was already initialized
'as an ADODB.Connection object
Dim rsEmployees As ADODB.Recordset
Dim sExecuteString As String
sExecuteString = "SELECT * FROM employees " & _
"WHERE LastName = '" & _
txtLastName & "'" & _
"AND FirstName = '" & _
txtFirstName & "'"
Set rsEmployees = connNWind.Execute(sExecuteString)
sExecuteString = "SELECT * FROM employees " & _
"WHERE LastName LIKE '" & _
txtLastName & "%'" & _
"AND FirstName LIKE '" & _
txtFirstName & "%'"
'INITIALIZING THE RECORDSET WITH THE 'EXECUTE METHOD OF A COMMAND OBJECT
Dim comNWind As ADODB.Command
Set comNWind = New ADODB.Command
With comNWind
Set .ActiveConnection = connNWind
.CommandType = adCmdText
.CommandText = sExecuteString
.Prepared = True
Set rsEmployees = .Execute(sExecuteString)
End With
'INITIALIZING THE RECORDSET WITH 'ITS OWN OPEN METHOD
Set rs = New ADODB.Recordset
With rs
.CursorLocation = adUseClient
.LockType = adLockBatchOptimistic
.Source = "Select * from employees"
Set .ActiveConnection = connNWind
.Open
End With
