Visual Basic 6 Black Book
(Publisher: The Coriolis Group)
Author(s): Steven Holzner
ISBN: 1576102831
Publication Date: 08/01/98

RDO: Opening A Connection

To open an RDO connection to a database, you can use the RDO OpenConnection method. OpenConnection is a method of the rdoEnvironment object, and you’ll find a collection of those objects in the rdoEngine object’s rdoEnvironments collection. To add the RDO objects to a program, select the Project|References menu item in Visual Basic, select the Microsoft Remote Data Object entry in the References dialog box, and click on OK. Now we’re free to use rdoEnvironment methods like OpenConnection:

workspace.OpenConnection(datasource, [prompt, [read-only, [connect, _
   [options]]]])

Here are the arguments to OpenConnection:

•  datasource—The name of the data source.

•  prompt—ODBC prompting characteristic: rdDriverPrompt asks the user for a driver/database, rdDriverNoPrompt uses specified driver/database, rdDriverComplete specifies the connection string itself, and rdDriverCompleteRequired is the same as rdDriverComplete, with the additional requirement that the driver should disable the controls for information not needed for the connection.

•  read-only—True if you want to open the data source as read-only.

•  connect—The connect string.

•  options—Set to rdAsyncEnable if you want to execute commands asynchronously (that is, without waiting for the command to be completed).

Let’s see an example. In our RDO code example, the rdocode project (see “A Full-Scale RDO Example” earlier in this chapter), we create an rdoEnvironment object named re this way when the form loads:

Dim re As Object

Private Sub Form_Load()

    Set re = rdoEngine.rdoEnvironments(0)
...
End Sub

Now we open a connection named db to the ODBC source (we set up this ODBC source in the previous chapter) this way:

Dim re As Object
Dim db As rdoConnection

Private Sub Form_Load()

    Set re = rdoEngine.rdoEnvironments(0)
    Set db = re.OpenConnection("db")

End Sub

That’s it—now we have a connection to our ODBC data source in the rdoConnection object named db. How do we access the records in that source? We’ll look into that next.

RDO: Creating A Result Set

After opening an ODBC data source and creating an rdoConnection object, we can create an RDO result set to start working with the records in that data source. To create a result set, we can use the rdoConnection method OpenResultset:

Set resultset = rdoConnection.OpenResultset (  name, [type, [locktype,_
   [ options]]])

Here are the arguments for OpenResultset:

•  name—Source for the result set; can be an rdoTable object, an rdoQuery object, or an SQL statement.

•  type—Specifies the result set type (see the following list).

•  locktype—Can be one of these values: rdConcurReadOnly (read-only), rdConcurLock (pessimistic concurrency), rdConcurRowVer (optimistic row-based concurrency), rdConcurValues (optimistic value-based concurrency), or rdConcurBatch (optimistic concurrency using batch updates).

•  options—Set to rdAsyncEnable if you want to execute commands asynchronously (that is, without waiting for the command to be completed).

Here are the possible values for the type argument:

•  rdOpenKeyset—Opens a dynaset-type rdoResultset object, which is like an ODBC keyset cursor.

•  rdOpenDynamic—Opens a dynamic-type rdoResultset object, which lets the application see changes made by other users.

•  rdOpenStatic—Opens a static-type rdoResultset object.

•  rdOpenForwardOnly—Opens a forward-only-type rdoResultset object, where you can only use MoveNext to move.

Let’s see an example. Here, we’ll create an SQL-based result set in our RDO code example, the rdocode project (see “A Full-Scale RDO Example” earlier in this chapter), when the form loads, using the rdoConnection object we’ve created—db. In this case, we’ll set up an SQL statement, SQLSel, to place all the fields from the data source’s table named students in the result set:

Dim re As Object
Dim db As rdoConnection
Dim SQLSel As String

Private Sub Form_Load()

    SQLSel = "Select * from students"
    Set re = rdoEngine.rdoEnvironments(0)
    Set db = re.OpenConnection("db")
...

Now we use OpenResultset to create an rdoResultset object, resultset:

Dim re As Object
Dim db As rdoConnection
Dim resultset As rdoResultset
Dim SQLSel As String

Private Sub Form_Load()
    SQLSel = "Select * from students"
    Set re = rdoEngine.rdoEnvironments(0)
    Set db = re.OpenConnection("db")

    Set resultset = db.OpenResultset(SQLSel, rdOpenKeyset)
...

Now that we’ve opened a result set, we can use rdoResultset methods like MoveFirst to move to the first record and display the data in that record’s Name and Grade fields with the rdocode project’s text boxes, Text1 and Text2:

Dim re As Object
Dim db As rdoConnection
Dim resultset As rdoResultset
Dim SQLSel As String

Private Sub Form_Load()

    SQLSel = "Select * from students"
    Set re = rdoEngine.rdoEnvironments(0)
    Set db = re.OpenConnection("db")

    Set resultset = db.OpenResultset(SQLSel, rdOpenKeyset)
    resultset.MoveFirst

    Text1.Text = resultset("Name")
    Text2.Text = resultset("Grade")

End Sub

And that’s it—we’ve opened an RDO result set and displayed some of the data in that result set.