Using the Connection Object's Execute Method
We can use the
Execute method to send a command (typically an SQL statement, but it might be other text) to the data source. If our SQL statement returns rows (instead of, say, updating some records) then a Recordset is created. The Execute method in reality always returns a Recordset. However, it is a closed Recordset if the command doesn't return results.
Let's see an example of the Execute method in action.
Try It Out - Testing the Execute Method
1. Add another button to the
frmDSN form to test the execute method. Name the new command button
cmdExecute and give it the caption Text Execute, as shown here:
2. Add the following code to the
Private Sub cmdExecute_Click() Dim myConnection As ADODB.Connection Dim myRecordSet As ADODB.Recordset Set myConnection = New ADODB.Connection myConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=C:\BegDB\Biblio.mdb" myConnection.Open ' Create a Recordset by executing a SQL statement Set myRecordSet = myConnection.Execute("Select * From Titles") ' Show the first title in the recordset. MsgBox myRecordSet("Title") ' Close the recordset and connection. myRecordSet.Close myConnection.Close End Sub
3. Run the program and press the Text Execute button. You'll see this message box appear:
How It Works
We learned a few interesting things in this example. First, we added the connection string directly to the connection object. In prior examples we first assigned the connection string to a string variable, then passed in the string variable to the
connection.ConnectionString property. This current example is a bare, minimalist approach to a connection string. We just pass the provider and the data source:
myConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=C:\BegDB\Biblio.mdb"
Then, once the connection string property is set, we open the connection:
Once the connection is open, we now want to issue an SQL statement. We do this by using the
.Execute method of the connection object. Here we are selecting all of the records from the
' Create a Recordset by executing a SQL statement Set myRecordSet = myConnection.Execute("Select * From Titles")
And finally, we simply display the contents of the
Title field. Notice that we access the field by using the name of the field. Of course, we could have used the "!" bang operator or the ordinal position as we have done in the past:
And since the current record is the first record in the recordset, the title of the first book in the first record is displayed.
One thing to keep in mind is that the returned Recordset will always be a read-only, forward-only cursor. This means you can't edit or scroll backwards. If you need a Recordset object with a bit more functionality, then create a Recordset object with the desired property settings. After the settings are in place, use the Recordset object's
Open method to execute the query that will return the desired cursor type. We'll talk some more about using cursors in conjunction with recordsets later in the chapter.
Now, let's discuss in more detail how we can interact with the recordset. The logical place to start is with how we open a recordset.