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.
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 cmdExecute button's Click event:
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 Sub3. Run the program and press the Text Execute button. You'll see this message box appear:
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:
myConnection.Open
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 Titles table:
' 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:
MsgBox myRecordSet("Title")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.