Book Review: Beginning Visual Basic 6 Databases
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:BegDBBiblio.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:BegDBBiblio.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 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:
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.
Page 14 of 23