March 1, 2021
Hot Topics:

Book Review: Beginning Visual Basic 6 Databases

  • By John Percival
  • Send Email »
  • More Articles »

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


' 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.

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:

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.

Page 14 of 23

This article was originally published on November 20, 2002

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date