Day 18

Using the Remote Data Control and the Remote Data Objects

Today, you'll learn about two alternative methods for accessing database information using Visual Basic 5: the Remote Data Control (RDC) and the Remote Data Objects (RDOs). Both these alternative methods are designed for reading and updating data stored in relational database management systems (RDBMSs) that are external to Visual Basic and to the Microsoft Jet data engine. Although it is possible to use the standard data control and Microsoft Jet data object collections to access data stored in RDBMS, the RDC and RDOs have properties and methods that make them better suited to manipulating data in remote systems.

WARNING: The RDC and RDOs are shipped as part of the Visual Basic 5
Enterprise Edition. If you do not have the Enterprise Edition of
Visual Basic 5, you cannot complete the examples in this chapter or run the code that ships on the CD-ROM with this book. You still can get a lot out of this chapter by reading through the text and inspecting the code examples, though.

Along with the details of the RDC and the RDOs, you learn some of the basics of remote data access in general. These basics are hidden from you when you use the data control, or they do not apply unless you are accessing remote data. You'll learn the meaning and use of these elements:

In today's lesson, you'll learn the properties, methods, and events of the RDC and how you can use these to develop data-entry forms using the same data-bound controls you learned about in the first week's lessons. After you learn the details of the RDC programming tool, you'll build a simple data-entry form based on the RDC. In this chapter, you even use an ODBC definition that links your RDC to a Microsoft Jet Access database.

You also learn the details of the RDOs. The RDOs are programming objects similar to the Microsoft Jet data-access objects (DAOs) you learned about in Week 2. Like the RDC, the RDO collection has special properties and methods that make it better suited to accessing data from remote storage systems. In this chapter, you'll learn how to use the RDO programming objects listed in Table 18.1.

Table 18.1. RDO programming objects.
Object Description
rdoColumn The RDO version of the Microsoft Jet Field object
rdoConnection The RDO equivalent of the Microsoft Jet Database object
rdoEngine The top-level data engine used to access remote data
rdoEnvironment The RDO equivalent of the Microsoft Jet Workspace object
rdoParameters A special collection of query parameters for the rdoQuery object
rdoQuery The RDO version of the Microsoft Jet QueryDef object
rdoResultset The RDO equivalent of the Microsoft Jet Recordset object
rdoTable The RDO version of the Microsoft Jet Table object


As Table 18.1 shows, most RDOs have a parallel in the Microsoft Jet DAO collections. If you have not read through the chapter covering the Microsoft Jet Database objects (Day 9, "Visual Basic and the Microsoft Jet Engine"), you might want to review that material before you continue with this chapter.

When you complete this chapter, you'll be able to create data-bound entry forms using the RDCs and to build Visual Basic programs that manipulate RDBMS data using the RDOs.

The Basics of Remote Data Access

Before getting into the details of the RDC and RDO programming tools, it is important to review a few basic principles of remote data access. When you use the Microsoft Jet DAOs or the standard data control, you usually do not need to deal with some of the issues covered here. The three concepts covered here are cursor drivers, dataset types, and lock types. Manipulating the parameters of these three properties affects the type of dataset you are working with and the types of operations you can perform on that data. By default, all remote connections to data are non-updatable (read-only) datasets. You can change this behavior by manipulating the three properties covered here.

Cursor Drivers

You use the cursor drivers to define the way in which you can move within a set of data. When you connect to data using the standard data control or the Microsoft Jet DAOs, you can, by default, move forward and backward in the dataset and move to any record in the collection. You can use any of the Move and Find methods (MoveFirst, MoveLast, MoveNext, MovePrevious, FindFirst, FindLast, FindNext, and FindPrevious), for example.

In order to have this movement capability, the database engine must be able to keep track of all the records in the collection and their place in the dataset. This process of keeping track of the location of the data pointer in the dataset is called cursor management. You can use two primary locations to keep track of the cursor location: the client workstation or the database server. Under the RDC and RDOs, the local workstation version of cursor management is handled by the ODBC driver. The server-side cursor management is handled by the database server that holds the data.

Under RDC and RDOs, there are two other possible settings for cursor management: Client Batch and None. You can use the Client Batch option with advanced data servers that allow multiple data requests to be sent simultaneously over the same connection. In this case, you can batch up multiple SQL statements in a single string and send them to the server at one time. The server manages the requests and reports results back to you as each SQL statement is completed.

You also can choose to use no cursor driver when accessing remote data. This results in the server sending you a single record from the dataset each time you request it. You cannot request previous records from the server and, if you want to start at the top of the collection, you must restart the query. This is the most limited cursor management available for a remote connection.

TIP: Although using no cursor management severely limits your capability to navigate a large set of data, it is the fastest connection possible. Even more valuable, you can use non-cursor driver sets to execute action queries such as SET UPDATE, INSERT INTO, and SELECT INTO queries. This means that you can perform multirecord updates without having to declare a cursor.

Cursor drivers can have five possible settings when you are working with RDC and RDOs. Table 18.2 shows these values and their meanings.

Table 18.2. Cursor driver options with RDC and RDOs.
Driver Option Integer Value Description
rdUseIfNeeded 0 Instructs RDOs/RDC to determine the best cursor driver to use for the requested operation. This is the default setting.
rdUseOdbc 1 Instructs RDC/RDOs to use the client-side ODBC cursor driver to keep track of the data pointer in the dataset.
rdUseServer 2 Instructs RDC/RDO to use the remote RDBMS cursor driver to keep track of the data pointer in the dataset.
rdUseClientBatch 3 Instructs RDC/RDO to use the remote RDBMS to manage multiple cursors in response to batch requests sent from the client workstation. This is available only on advanced RDBMS systems (SQL Server 6.0 and higher).
rdUseNone 4 Instructs RDC/RDO to use no cursor driver. The re-sult is a return on only one row, no matter how may rows are in the result set. This option still can be used to perform action queries, such as SET UPDATE, INSERT INTO, SELECT INTO, and so on.


As you can see in Table 18.2, the default option for RDC/RDOs is to allow rdoEngine to select the most appropriate cursor available. When rdUseIfNeeded is in place, RDC/RDOs attempt to use server-side cursors if they are available (rdUseServer or rdUseClientBatch if Batch mode is in force). If the RDBMS does not support server-side cursor management, RDC/RDOs use the client workstation cursor manager (rdUseOdbc). The rdUseNone option is used only if the value is explicitly selected.

Again, the important thing to remember about cursor drivers is that they govern the way you can navigate the dataset. The other important aspect of cursor drivers is that, with RDC/RDOs, you can select the driver you prefer: client-side or server-side. The only caveat to all this is that the data source (RDBMS) must support your cursor request in order for you to be able to select certain server-side options.

Dataset Type

The selection of cursor drivers is just one of the options you must determine when accessing remote data. Another important parameter is the dataset type property of the connection. Several types of datasets can be returned by the remote data source. Table 18.3 outlines these types.

Table 18.3. Dataset type options with RDC/RDOs.
Dataset Option Integer Value Description
rdOpenForwardOnly 0 Creates a read-only, scroll-forward-only dataset. All members are copied to the client workstation for use. This is the default option.
rdOpenStatic 1 Creates an updatable dataset that has non-changing membership. New records added to the set may (or may not) appear as part of the set, depending on the cursor driver. All members are copied to the client workstation for use.
rdOpenDynamic 2 Creates an updatable dataset that has changing membership. New records added to the set will appear as part of the set. Actual data records are buffered to the client workstation as needed. Record keys are not used.
rdOpenKeyset 3 Creates an updatable dataset that has changing membership. New records added to the set will appear as part of the set. Actual data records are buffered to the client workstation as needed. Record keys (the key set) are created to point to all members of the set. This enables you to use bookmarks with the dataset.


The information in Table 18.3 deserves some additional comment. Remember that the primary work of dataset management from the workstation point of view is gaining access to the actual rows of data stored in the remote system. The most efficient method for gaining access is to receive a set of row pointers from the RDBMS, not to actually receive the complete rows of data. In this way, the RDBMS can allow multiple users to have access to the same set of records without having to deal with major synchronization work if more than one user attempts to update the same row of data.

For this reason, RDC/RDOs support the use of rdOpenKeyset datasets. These are sets of data that contain not just data rows, but also key-set pointers to other rows in the requested dataset. rdOpenKeysets are the remote data-access versions of Microsoft Jet Dynaset-type datasets.

The rdOpenDynamic datasets do not support keys, but they do act as dynamically changing sets of data. These dynamic datasets reflect newly added or deleted records just as rdOpenKeyset datasets, but they do not support the use of bookmarks. This is because the rdOpenDynamic datasets are kept dynamic through the use of recurring refreshes of the data membership from the remote data source. Although this method is accurate, it is hardly efficient. If you have large sets of data, using the rdOpenDynamic option can result in decreased throughput, because all data members are shipped to the client each time the set is refreshed.

It also is important to note that rdOpenStatic datasets are updatable. Even though their membership is kept static (there is no constant refresh from the remote data source), it still is possible to update the records in the set. These datasets act much like an updatable version of the Microsoft Jet Snapshot dataset.

Lock Types

The final concept that deserves special attention when dealing with remote data access is the lock type used to manage dataset updates. With Microsoft Jet data access, you have two options: pessimistic (lock at start of edit) and optimistic (lock at start of update). RDC/RDOs offer a few additional variations to these two basic options. Table 18.4 shows the lock-type options with RDC/RDOs.

Table 18.4. Dataset lock-type options with RDC/RDOs.
Lock-Type Option Integer Value Description
rdConcurReadOnly 0 Provides no row-level locking. Forces the dataset to act as a read-only set. You can use this option to perform action queries, though. This is the default option.
rdConcurLock 1 Provides pessimistic locking for the entire row set. The lock occurs as soon as the data is accessed--not when an Edit operation begins.
rdConcurRowver 2 Provides optimistic locking based on internal row ID values (usually, the TimeStamp column).
rdConcurValues 3 Provides optimistic locking based on a column-by-column check of the data in each row.
rdConcurBatch 4 Provides optimistic locking based on the value in the UpdateCriteria property when using Batch Update mode. Not supported by all RDBMSs.


The type of locking mechanism used can have a great effect on the performance of your application and the capability of others to access the shared data. The rdConcurReadOnly option allows anyone else to access the same data your application is using. Your application cannot update the dataset unless you are using action queries, however. The rdConcurLock option provides the greatest degree of locking. As soon as your dataset is created, all buffered rows in the dataset are locked. Because locks occur on pages, this can result in hundreds of record locks while your application browses the dataset.

The rdConcurRowver and rdConcurValues options allow optimistic locking schemes. In the rdConcurRowver option, each record's ID value (usually, the TimeStamp column) is checked at the time the row is updated. If the ID has changed since your application retrieved the data, an error is reported. In the rdConcurValues option, each value in the row's columns is checked against the original value. If the value has changed since your application received the dataset, an error is reported.

The least intrusive form of locking for read/write datasets is the rdConcurRowver or rdConcurValues option. The most secure form of locking for read/write datasets is the rdConcurLock option. An advantage that RDC/RDOs have over Microsoft Jet is that you easily can tune the number of rows in the row set by using the RowsetSize property. If you set this property sufficiently low, you can use the rdConcurLock option without adversely affecting other users who are attempting to access the remote data.

Now that you understand the basics of accessing remote data, you are ready to begin using the RDC and RDO tools to build Visual Basic programs that read and update remote data sources.

Building an ODBC Definition

Before you can complete any of the projects in this chapter, you need to build an ODBC connection to the BOOKS5.MDB database that ships on the CD-ROM with this book. After you build this ODBC data source definition, you'll be able to use both RDC and RDO programming tools to access the BOOKS5.MDB database.

WARNING: You might have both the 32-bit and the 16-bit ODBC Administrator applets in your Control Panel group. Be sure to use the 32-bit ODBC Administrator to define your new data source. The RDC and RDO programming objects recognize only data-source definitions built with the 32-bit ODBC Administrator.

To build an ODBC definition, you need to call up the ODBC Administrator. Follow these steps:

1. Choose Start | Settings | Control Panel from the main Windows 95 (or WinNT4) menu. The Control Panel appears, as shown in Figure 18.1. Double-click the 32-bit ODBC icon to open the ODBC Administrator.


Figure 18.1. Accessing the 32-bit ODBC Administrator applet from the Control Panel group.

NOTE: If you are using WinNT 3.51, double-click the ODBC Administrator applet from the Control Panel group.
2. After the ODBC Data Source Administrator dialog box appears, select the User DSN tab and click Add. The Create New Data Source dialog box appears, as shown in Figure 18.2.


Figure 18.2. Adding a new definition to the User DSN collection.


3. Double-click the Microsoft Access 7.0 Database driver. The OBBC Microsoft Access 97 Setup dialog box appears. In the Data Source Name field, enter RDC Books5. In the Description field, enter Remote Data Connection to books5.mdb.
In the Database section, specify the path C:\TYSDBVB5\SOURCE\DATA\BOOKS5.MDB by clicking Select and navigating to it in the Select Database dialog box. Figure 18.3 shows what the dialog box should look like at this point.

4. Click OK to save the definition and then exit the ODBC Administrator applet. You now have an ODBC data source definition that you can use with the RDC and the RDOs.


Figure 18.3. Filling out the Microsoft Access 97 Setup dialog box.

NOTE: Using RDC/RDOs to connect to a Microsoft Jet Access database is not advisable in a production setting. The process of going through RDC/RDOs to ODBC to Microsoft Access is very wasteful and limits the programming options available to the application. Using Microsoft Access tables in this way, however, is a common method of prototyping tables that will later be moved to SQL Server or some other RDBMS. If you have SQL Server or some other RDBMS available to your workstation, you can substitute the DSN built in this section for another one that uses your own remote data.


Programming with the
Remote Data Control

Programming with the RDC is very similar to programming with the standard data control that ships with all versions of Visual Basic 5.0. The RDC has unique property names, but these properties are quite similar to the standard data-control properties and they provide almost the same functionality. Also notice that the RDC has methods and events that are close, if not identical, to the methods and events of the standard data control.

In the following sections, you learn the properties, methods, and events of the RDC. As you review these items, it might help to refer to the material covered in Day 3, "Visual Basic Database Objects."

The RDC Properties

The RDC has a number of unique properties you can use to establish and manage your connection to the remote data source. Table 18.5 shows these unique properties, their types, default settings, and short descriptions of their meaning and use.

Table 18.5. Unique properties of the RDC.
Property Type Default Setting Description
Connection rdoConnection <none> Contains an object reference to the RDO Connection object created by the RDC. You can use this as you use the Database property of the standard data control.
CursorDriver Integer rdUseIfNeeded (0) Controls the source and behavior of the cursor manager.
DataSourceName String <none> Specifies the name of the ODBC data source you want to access. This is the RDO equivalent of the database name.
EditMode Integer rdEditNone (0) Indicates whether an edit or AddNew oper-ation is in effect. You can use this to deter-mine whether an Update method must be used to complete a pending action.
Environment rdoEnvironment <none> Contains an object reference to the RDO Environment object created by the RDC. You can use this as you use the Workspace object in Microsoft Jet data access.
KeysetSize Integer 100 Specifies the number of rows in the keyset buffer. Using this value and the MaxRows and RowsetSize properties can affect the way records are buffered and locked.
LockType Integer rdConcurReadOnly (0) Controls how records are locked for update.
LoginTimeOut Integer 0 Using a value greater than zero indicates the length of time (in seconds) that RDC/RDOs wait before reporting a time-out error when trying to log onto the remote data source. A typical LAN connection time-out value is 15 seconds. RAS/Internet connections may need a longer time-out setting.
LogMessages String <none> Setting this value to a valid drive/path/filename enables the creation of a trace file for ODBC con-versations. This file can get quite long and should be used only for temporary debugging of ques-tionable connections.
MaxRows Integer -1 Controls how many rows are affected by an action. When set to -1 (default) all rows matching the criteria are affected. You can set this value to 1 to ensure that only one record is updated when us-ing an UPDATE query.
Prompt Integer rdDriverPrompt (0) Controls the behav-ior of the ODBC logon process. Set-ting this value to rdDriverNoPrompt suppresses the ODBC logon screen. You can use this set-ting to log onto the data source without asking the user for additional parameters. If the values set in the programmatic logon are invalid, an error is reported.
Resultset rdoResultset <none> Contains an object reference to the rdoResultset object created by the RDC. you can use this as you use the Recordset object of the standard data control.
ResultsetType Integer rdOpenStatic (0) Controls the type of dataset returned by the RDC. The rdOpenStatic option returns an updatable dataset with unchanging membership; the rdOpenKeyset opens an updatable dataset with changing mem-bership.
RowsetSize Integer 100 Controls the number of rows buffered to your application. This is also the num-ber of rows locked when using pessimistic locking (rdConcurLock).
SQL String <none> Specifies the SQL statement used to populate the dataset for the RDC. This is the RDC equivalent of the RecordSource property of the stan-dard data control.
StillExecuting Boolean False Indicates whether the dataset is still in the process of being created. You can check this periodically on long data connections.
Transactions Boolean False Indicates whether the remote data source supports the use of BeginTrans, CommitTrans, and RollbackTrans methods. You can check this before you attempt to use these methods.


The RDC Methods

Several methods are associated with the RDC. Most of these methods have counterparts with the standard data control. Table 18.6 lists the RDC methods.

Table 18.6. The RDC methods.
Method Function
BeginTrans, Enables programmers to provide transaction management
CommitTrans, for RDC actions. This improves data integrity on action queries RollbackTrans and can speed up processing on single-row updates.
Cancel Cancels any pending Query, Edit, AddNew, or Delete operation.
Refresh Repopulates the dataset.
UpdateControls Refreshes the data-bound controls with the contents of the dataset.
UpdateRows Refreshes the dataset with the values in the data-bound controls.


The most important items to note in Table 18.6 are the transaction-management methods and the UpdateRows method. These methods are unique to the RDC and are not available with the standard Microsoft Jet data control.

The RDC Events

The RDC offers a set of unique events that are similar to the events supported by the Microsoft Jet data control: Validate, Reposition, and Error. The RDC has an additional event not supported by the Microsoft Jet data control: the QueryCompleted event. This event is fired after the dataset has returned successfully from the remote data source. This event can be used to alert users of the completion of delayed queries because of slow connections or a large dataset size.

The events, methods, and properties covered in the previous sections are illustrated in the RDC data-entry project in the next section.

Laying Out the RDC Data-Entry Forms

In this section, you create a simple data-entry form using the RDC. This form illustrates the use of most of the properties, methods, and events covered in previous sections of this chapter. If you haven't already done so, start Visual Basic 5.0 and select a new, Standard EXE project.

Before you begin laying out the data-entry form, be sure to load the RDC by choosing
Project | Components. The Components dialog box appears, as shown in Figure 18.4.

Figure 18.4. Loading the RDC.


After adding the RDC to the project, create your data-entry form based on Figure 18.5 and Table 18.7.

Table 18.7. Laying out the RDC data-entry form.
Control Property Setting
VB.Form Name frmDataEntry
Caption "Form1"
ClientHeight 2250
ClientLeft 60
ClientTop 345
ClientWidth 6510
StartUpPosition 3 `Windows Default
VB.CommandButton Name cmdBtn
Caption "&Close"
Height 300
Index 4
Left 5160
Top 1500
Width 1200
VB.CommandButton Name cmdBtn
Caption "&Refresh"
Height 300
Index 3
Left 3900
Top 1500
Width 1200
VB.CommandButton Name cmdBtn
Caption "&Update"
Height 300
Index 2
Left 2640
Top 1500
Width 1200
VB.CommandButton Name cmdBtn
Caption "&Delete"
Height 300
Index 1
Left 1380
Top 1500
Width 1200
VB.CommandButton Name cmdBtn
Caption "&Add"
Height 300
Index 0
Left 120
Top 1500
Width 1200
VB.TextBox Name txtDOB
DataSource "MSRDC1"
Height 285
Left 1440
Text "Text2"
Top 960
Width 1200
VB.TextBox Name txtName
DataSource "MSRDC1"
Height 285
Left 1440
Text "Text1"
Top 540
Width 2400
MSRDC.MSRDC Name MSRDC1
Align Bottom
Height 330
Left 0
Top 1920
Width 6510
VB.Label Name lblDOB
Caption "DOB"
Height 255
Left 120
Top 1020
Width 1200
VB.Label Name lblName
Caption "Name"
Height 255
Left 120
Top 600
Width 1200
VB.Label Name lblAuthorID
Caption "Author ID"
Height 255
Left 120
Top 180
Width 1200
VB.Label Name lblAUID
BorderStyle 1 `Fixed Single
Caption "Label1"
DataSource "MSRDC1"
Height 255
Left 1440
Top 180
Width 1200


In addition to the main data-entry form, you need to build a second form to display status messages while the data-entry form is running. Add a new standard form to your project. Set its Name property to frmMsgs, and place a single textbox on the form. Set the textbox Name property to txtMsgs, and set its Multiline property to True and its Scrollbars property to Both.

Figure 18.5. Laying out the RDC data-entry form.


Now save the project as PRJRDC.VBP, the main form as FRMDATAENTRY.FRM, and the support form as FRMMSGS.FRM. Now you're ready to add Visual Basic code to the forms.

Coding the RDC Data-Entry Forms

First, you need to add code to two events in the frmMsgs form. Listing 18.1 shows the code for the Form_Load and Form_Resize events. Add this code to your support form.

Listing 18.1. Coding the events for the frmMsgs support form.

Private Sub Form_Load()
    `
    Me.Caption = "MSRDC Event Messages"
    txtMsg.Text = ""
    `
End Sub

Private Sub Form_Resize()
    `
    ` make text box fill the form
    `
    If Me.WindowState <> vbMinimized Then
        txtMsg.Left = 1
        txtMsg.Top = 1
        txtMsg.Width = Me.ScaleWidth
        txtMsg.Height = Me.ScaleHeight
    End If
    `
End Sub


The code in Listing 18.1 sets up the initial caption and textbox on the form and then forces the textbox to fill the entire frmMsgs form space. This form displays progress messages reported by the main data-entry form. You can save and close this form. This is all the code you need to add to the frmMsgs form.

Now open the main data-entry form (frmDataEntry) and add the code from Listing 18.2 to the Form_Load event of the form.

Listing 18.2. Coding the Form_Load event of the frmDataEntry form.

Private Sub Form_Load()
    `
    ` project setup actions
    `
    frmMsgs.Show ` launch message window
    `
    ` set up RDC
    MSRDC1.DataSourceName = "RDC Books5"
    MSRDC1.SQL = "SELECT * FROM Authors"
    MSRDC1.CursorDriver = rdUseClientBatch
    MSRDC1.LockType = rdConcurBatch
    MSRDC1.ResultsetType = rdOpenStatic
    MSRDC1.Refresh
    `
    ` bind inputs to rdc
    lblAUID.DataField = "AUID"
    txtName.DataField = "Name"
    txtDOB.DataField = "DOB"
    `
    ` set form title
    Me.Caption = "Remote Data Control Demo"
    `
End Sub


The code in Listing 18.2 sets up the basic parameters to the RDC, uses the Refresh method to fetch the data, and then updates the DataField properties of the bound input controls in order to link them to the dataset returned in the RDC. Note that you are using the rdOpenStatic option to create an updatable, fixed-membership dataset.

Now add the code from Listing 18.3 to the cmdBtn_Click event of the form. This single set of code handles all the command buttons, because the buttons were added as a control array.

Listing 18.3. Coding the cmdBtn_Click event.

Private Sub cmdBtn_Click(Index As Integer)
    `
    ` handle button selections
    `
    Select Case Index
        Case 0 ` add
            MSRDC1.Resultset.AddNew
        Case 1 ` delete
            MSRDC1.Resultset.Delete
        Case 2 ` update
            MSRDC1.UpdateRow
        Case 3 ` refresh
            MSRDC1.UpdateControls
        Case 4 ` close
            Unload Me
    End Select
    `
End Sub


Listing 18.4 shows the code you need to add to the four RDC-related events. Note that all four of these routines use a PostMsg subroutine and that the Validate event calls a ShowAction function. You build these routines in the next step of the project.

Listing 18.4. Coding the events of the RDC.

Private Sub MSRDC1_Error(ByVal Number As Long, Description As String, ByVal ÂScode As Long, ByVal Source As String, ByVal HelpFile As String, ByVal ÂHelpContext As Long, CancelDisplay As Boolean)
    `
    PostMsg "MSRDC1_Error - Number=" & CStr(Number) & ", Description = " & ÂDescription & ", Scode = " & CStr(Scode) & ", Source = " & Source & ", ÂHelpFile = " 
& HelpFile & ", HelpContext = " & CStr(HelpContext) & ", ÂCancelDisplay = " & CStr(CancelDisplay)
    `
End Sub

Private Sub MSRDC1_QueryCompleted()
    `
    PostMsg "MSRDC1_QueryCompleted"
    `
End Sub

Private Sub MSRDC1_Reposition()
    `
    PostMsg "MSRDC1_Reposition"
    `
End Sub

Private Sub MSRDC1_Validate(Action As Integer, Reserved As Integer)
    `
    PostMsg "MSRDC1_Validate - Action=" & ShowAction(Action) & ", Reserved=" & ÂCStr(Reserved)
    `
End Sub


After adding the code for all the events, add the code in Listing 18.5 to the form to create the ShowAction function. This function converts the integer value passed from the Validate event into a friendly string name. This is used to display progress messages in the frmMsg form.

Listing 18.5. Coding the ShowAction function.

Public Function ShowAction(intAction As Integer) As String
    `
    ` convert numeric action value
    ` into friendly string value
    `
    Dim strMsg As String
    `
    Select Case intAction
        Case rdActionCancel  `0
            strMsg = "Cancel"
        Case rdActionMoveFirst      `1
            strMsg = "MoveFirst"
        Case rdActionMovePrevious    `2
            strMsg = "MovePrevious"
        Case rdActionMoveNext    `3
            strMsg = "MoveNext"
        Case rdActionMoveLast    `4
            strMsg = "MoveLast"
        Case rdActionAddNew  `5
            strMsg = "AddNew"
        Case rdActionUpdate  `6
            strMsg = "Update"
        Case rdActionDelete  `7
            strMsg = "Delete"
        Case rdActionFind `8
            strMsg = "Find"
        Case rdActionBookmark `9
            strMsg = "Bookmark"
        Case rdActionClose '10
            strMsg = "Close"
        Case rdActionUnload '11
            strMsg = "Unload"
        Case rdActionUpdateAddNew '12
            strMsg = "UpdateAddNew"
        Case rdActionUpdateModified '13
            strMsg = "UpdateModified"
        Case rdActionRefresh '14
            strMsg = "Refresh"
        Case rdActionCancelUpdate '15
            strMsg = "CancelUpdate"
        Case rdActionBeginTransact '16
            strMsg = "BeginTrans"
        Case rdActionCommitTransact '17
            strMsg = "CommitTrans"
        Case rdActionRollbackTransact '18
            strMsg = "RollbackTrans"
        Case rdActionNewParameters '19
            strMsg = "NewParameters"
        Case rdActionNewSQL '20
            strMsg = "NewSQL"
    End Select
    `
    ShowAction = strMsg
    `
End Function


Notice that the Validate event of the RDC has several action values that are not available with the standard Microsoft Jet data control. These additional values come in handy when managing remote data connections.

Now add the PostMsg method to your data-entry form. Listing 18.6 shows the code for this support routine.

Listing 18.6. Coding the PostMsg subroutine.

Public Sub PostMsg(strMsg As String)
    `
    ` post a message to a text box
    `
    Static lngCounter As Long
    `
    lngCounter = lngCounter + 1
    frmMsgs.txtMsg = Format(lngCounter, "000") & ":" & strMsg & vbCrLf & ÂfrmMsgs.txtMsg
    `
End Sub


As mentioned earlier, the sole purpose of this routine is to post messages to the supporting form so that you can see the progress of data requests using the RDC. Now save the form (FRMDATAENTRY.FRM) and the project (PRJRDC.VBP) before running it.

When you run the project, the message form and the data form appear. Note the messages that have been posted to the support form. Modify the first record (change the value of the Name field) and move the record pointer. You see a number of messages appear in the support form indicating the validation of the new data, the updating of the modified data, and the repositioning of the record pointer on the new record (see Figure 18.6).

Figure 18.6. Running the RDC data-entry project.


That's all there is to creating data-entry forms using data-bound controls and the RDC. In the next section, you'll learn how to create database applications that use the RDOs instead of a data-bound control set.

Programming with RDOs

Creating database applications that use the RDOs is quite similar to programming with the Microsoft Jet DAOs. Most of the material in this section of the chapter refers to Chapter 9. If you have not already completed that chapter, you might want to review it before you continue with this chapter.

The RDO programming objects are arranged in a hierarchy of collections. The top-level object is the rdoEngine object. This is the programmatic access to the Microsoft remote data engine used by Visual Basic 5 to gain access to all remote data. All requests using the RDO objects are handled by the rdoEngine.

The rdoEngine creates one or more rdoEnvironment objects. These objects are used to manage the details of the various connections to datasets. rdoEnvironment objects can create rdoConnection objects. These are the actual connections to existing data sources at the remote data system. Each rdoConnection can create one or more rdoResultset objects. The rdoResultset object contains the actual rows of data. You also can create and access rdoQuery objects or rdoTable objects from the rdoConnection object. The rdoQuery object also has an rdoParameter object to manage the passing of parameters during the processing of queries.

In the sections that follow, you code examples of each of the RDO objects, inspect their properties, and exercise their methods. To do this, start a new Visual Basic 5 Standard EXE project. Set the project name to prjRDO and the form name to frmRDO. Save the empty form as FRMRDO.FRM and the project as PRJRDO.VBP. In each of the following sections, you add code that illustrates each of the RDO objects.

The rdoEngine Object

The rdoEngine object is the top-level object in the RDO collection. This object has only a handful of parameters and has no collection of its own. You can have only one rdoEngine instance in your Visual Basic programs. Add a new command button to your form. Set its Name property to cmdRDOEngine and its Caption to RDO Engine. Now add the code in Listing 18.7 to the cmdRDOEngine_Click event.

Listing 18.7. Coding the cmdRDOEngine_Click event.

Private Sub cmdRDOEngine_Click()
    `
    ` show rdo engine properties
    `
    Dim strMsg As String
    Dim rdoEng As rdoEngine
    Dim aryCursorDriver As Variant
    `
    aryCursorDriver = Array("rdUseIfNeeded", "rdUseOdbc", "rdUseServer", Â"rdUseClientBatch", "rduseNone")
    `
    Set rdoEng = rdoEngine
    `
    strMsg = strMsg & "rdoDefaultCursorDriver=" & ÂaryCursorDriver(rdoEng.rdoDefaultCursorDriver) & vbCrLf
    strMsg = strMsg & "rdoDefaultErrorThreshold=" & ÂCStr(rdoEng.rdoDefaultErrorThreshold) & vbCrLf
    strMsg = strMsg & "rdoDefaultLoginTimeOut=" & ÂCStr(rdoEng.rdoDefaultLoginTimeout) & vbCrLf
    strMsg = strMsg & "rdoDefaultPassword=" & rdoEng.rdoDefaultPassword & vbCrLf
    strMsg = strMsg & "rdoDefaultUser=" & rdoEng.rdoDefaultUser & vbCrLf
    strMsg = strMsg & "rdoLocaleID=" & CStr(rdoEng.rdoLocaleID) & vbCrLf
    strMsg = strMsg & "rdoVersion=" & CStr(rdoEng.rdoVersion) & vbCrLf
    `
    MsgBox strMsg, vbInformation, "RDOEngine"
    `
    Set rdoEng = Nothing
    `
End Sub


Save and run the project. After you click the RDO Engine command button, you should see something like the message in Figure 18.7.

Figure 18.7. Displaying the RDO Engine properties.


The rdoEnvironment Object

The rdoEnvironment object contains information about the current environment for data connections. The rdoEnvironment object is the RDO equivalent of the Microsoft Jet Workspaces object. rdoEnvironment objects can be used to group rdoConnection objects together for transaction-management purposes as well.

You can create multiple rdoEnvironment objects under the rdoEngine object. All rdoEnvironment objects are contained in the rdoEnvironments collection object.

Add a new button to the form. Set its Name property to cmdRDOEnvironment and
its Caption to Environment. Now enter the code from Listing 18.8 to the cmdRDOEnvironment_Click event.

Listing 18.8. Coding the cmdRDOEnvironment_Click event.

Private Sub cmdRDOEnvironment_Click()
    `
    ` show environment collection as properties
    `
    Dim strMsg As String
    Dim rdoEnv As rdoEnvironment
    Dim rdoNewEnv As rdoEnvironment
    Dim aryCursorDriver As Variant
    `
    aryCursorDriver = Array("rdUseIfNeeded", "rdUseOdbc", "rdUseServer", Â"rdUseClientBatch", "rduseNone")
    `
    Set rdoNewEnv = rdoEngine.rdoCreateEnvironment("rdoTEMP", "admin", "")
    `
    For Each rdoEnv In rdoEngine.rdoEnvironments
        strMsg = strMsg & "rdoCursorDriver=" & ÂaryCursorDriver(rdoEnv.CursorDriver) & vbCrLf
        strMsg = strMsg & "hEnv=" & CStr(rdoEnv.hEnv) & vbCrLf
        strMsg = strMsg & "LoginTimeOut=" & CStr(rdoEnv.LoginTimeout) & vbCrLf
        strMsg = strMsg & "Name=" & rdoEnv.Name & vbCrLf
        `strMsg = strMsg & "Password=" & rdoEnv.Password & vbCrLf
        strMsg = strMsg & "UserName=" & rdoEnv.UserName & vbCrLf
        strMsg = strMsg & vbCrLf
    Next
    `
    MsgBox strMsg, vbInformation, "rdoEnvironment"
    `
    rdoNewEnv.Close
    Set rdoEnv = Nothing
    Set rdoNewEnv = Nothing
    `
End Sub


After you save and run the project, click the Environment button to display a dialog box similar to the one in Figure 18.8.

Figure 18.8. Displaying the rdoEnvironment properties.


Notice that a default environment always is available to your application. It is advisable, however, to create your own environment before you attempt to establish a connection to remote data sources.

The rdoConnection Object

The rdoConnection object contains the details needed to establish a connection between your application and the remote data source. The rdoConnection object is similar to the Microsoft Jet Database object.

You can create more than one rdoConnection object under the same rdoEnvironment object. All rdoConnection objects are stored in the rdoConnections collection.

Add a new button to the form. Set its Name to cmdRDOConnection and its Caption to Connection. Then add the code in Listing 18.9 to the cmdRDOConnection_Click event.

Listing 18.9. Coding the cmdRDOConnection_Click event.

Private Sub cmdRDOConnection_Click()
    `
    ` show resultsets
    `
    Dim strMsg As String
    Dim rdoEnv As rdoEnvironment
    Dim rdoCon As rdoConnection
    Dim rdoNewCon As rdoConnection
    Dim aryCursorDriver As Variant
    `
    aryCursorDriver = Array("rdUseIfNeeded", "rdUseOdbc", "rdUseServer", Â"rdUseClientBatch", "rduseNone")
    `
    Set rdoEnv = rdoEngine.rdoCreateEnvironment("rdoTEMP", "admin", "")
    Set rdoNewCon = rdoEnv.OpenConnection("RDC Books5")
    `
    For Each rdoCon In rdoEnv.rdoConnections
        strMsg = strMsg & "AsyncCheckInterval=" & ÂCStr(rdoCon.AsyncCheckInterval) & vbCrLf
        strMsg = strMsg & "Connect=" & rdoCon.Connect & vbCrLf
        strMsg = strMsg & "CursorDriver=" & aryCursorDriver(rdoCon.CursorDriver) Â& vbCrLf
        strMsg = strMsg & "hDbc=" & CStr(rdoCon.hDbc) & vbCrLf
        strMsg = strMsg & "LoginTimeOut=" & CStr(rdoCon.LoginTimeout) & vbCrLf
        strMsg = strMsg & "LogMessages=" & rdoCon.LogMessages & vbCrLf
        strMsg = strMsg & "Name=" & rdoCon.Name & vbCrLf
        strMsg = strMsg & "QueryTimeOut=" & CStr(rdoCon.QueryTimeout) & vbCrLf
        strMsg = strMsg & "RowsAffected=" & CStr(rdoCon.RowsAffected) & vbCrLf
        strMsg = strMsg & "StillConnecting=" & CStr(rdoCon.StillConnecting) & ÂvbCrLf
        strMsg = strMsg & "StillExecuting=" & CStr(rdoCon.StillExecuting) & ÂvbCrLf
        strMsg = strMsg & "Transactions=" & CStr(rdoCon.Transactions) & vbCrLf
        strMsg = strMsg & "Updatable=" & CStr(rdoCon.Updatable) & vbCrLf
        strMsg = strMsg & "Version=" & rdoCon.Version & vbCrLf
    Next
    `
    MsgBox strMsg, vbInformation, "rdoConnection"
    `
    rdoNewCon.Close
    rdoEnv.Close
    Set rdoEnv = Nothing
    Set rdoCon = Nothing
    Set rdoNewCon = Nothing
    `
End Sub


Save and run the project. After you click the Connection button, you'll see a display of all the default properties of an rdoConnection object, as shown in Figure 18.9.

Figure 18.9. Displaying the rdoConnection properties.


Note that the Version property of the rdoConnection object reports the version number of the ODBC driver used to establish the connection to the remote data source.

The rdoResultset Object

After a connection is established between your program and the remote data, you can use the rdoResultset object to create a collection of records. The rdoResultset object is the RDO equivalent of the Microsoft Jet Recordset object. rdoResultset contains a direct reference to all the rows and columns in the dataset.

You can have multiple rdoResultset objects for each rdoConnection object. All Resultset objects are stored in the Resultsets collection of the rdoConnection object.

Add a new button to the project. Set its Name property to cmdRDOResultset and its Caption to Resultset. Now add the code in Listing 18.10 to the cmdRDOResultset_Click event.

Listing 18.10. Coding the cmdRDOResultset_Click event.

Private Sub cmdRDOResultset_Click()
    `
    ` show result set properties
    `
    Dim rdoEnv As rdoEnvironment
    Dim rdoCon As rdoConnection
    Dim rdoRS As rdoResultset
    Dim strMsg As String
    Dim aryEditMode As Variant
    Dim aryLockType As Variant
    Dim aryType As Variant
    `
    aryEditMode = Array("rdEditNone", "rdEditInProgress", "rdEditAdd")
    aryLockType = Array("rdConcurReadOnly", "rdConcurLock", "rdConcurRowVer", Â"rdConcurValues", "rdConCurBatchEdit")
    aryType = Array("rdOpenForwardOnly", "rdOpenKeyset", "rdOpenDynamic", Â"rdOpenStatic")
    `
    ` set up env/con/rs
    Set rdoEnv = rdoEngine.rdoCreateEnvironment("rdoTEMP", "admin", "")
    Set rdoCon = rdoEnv.OpenConnection("RDC Books5")
    Set rdoRS = rdoCon.OpenResultset("SELECT * FROM Authors")
    `
    ` show properties of the rdoRS
    strMsg = strMsg & "AbsolutePosition=" & CStr(rdoRS.AbsolutePosition) & ÂvbCrLf
    strMsg = strMsg & "BOF=" & CStr(rdoRS.BOF) & vbCrLf
    strMsg = strMsg & "Bookmark=" & rdoRS.Bookmark & vbCrLf
    strMsg = strMsg & "Bookmarkable=" & CStr(rdoRS.Bookmarkable) & vbCrLf
    strMsg = strMsg & "EditMode=" & aryEditMode(rdoRS.EditMode) & vbCrLf
    strMsg = strMsg & "EOF=" & CStr(rdoRS.EOF) & vbCrLf
    strMsg = strMsg & "hStmt=" & CStr(rdoRS.hStmt) & vbCrLf
    strMsg = strMsg & "LastModified=" & rdoRS.LastModified & vbCrLf
    strMsg = strMsg & "LockEdits=" & CStr(rdoRS.LockEdits) & vbCrLf
    strMsg = strMsg & "LockType=" & aryLockType(rdoRS.LockType) & vbCrLf
    strMsg = strMsg & "Name=" & rdoRS.Name & vbCrLf
    strMsg = strMsg & "PercentPosition=" & CStr(rdoRS.PercentPosition) & vbCrLf
    strMsg = strMsg & "Restartable=" & CStr(rdoRS.Restartable) & vbCrLf
    strMsg = strMsg & "RowCount=" & CStr(rdoRS.RowCount) & vbCrLf
    strMsg = strMsg & "Status=" & CStr(rdoRS.Status) & vbCrLf
    strMsg = strMsg & "StillExecuting=" & CStr(rdoRS.StillExecuting) & vbCrLf
    strMsg = strMsg & "Transactions=" & CStr(rdoRS.Transactions) & vbCrLf
    strMsg = strMsg & "Type=" & aryType(rdoRS.Type) & vbCrLf
    strMsg = strMsg & "Updatable=" & CStr(rdoRS.Updatable) & vbCrLf
    `
    MsgBox strMsg, vbInformation, "rdoResultset"
    `
    rdoRS.Close
    rdoCon.Close
    rdoEnv.Close
    Set rdoRS = Nothing
    Set rdoCon = Nothing
    Set rdoEnv = Nothing
    `
End Sub


Note the use of the OpenResultset method of the rdoConnection object to create the rdoResultset. Save and run the project. After you click the Resultset button, you'll see a display similar to the one in Figure 18.10.

Figure 18.10. Displaying the rdoResultset properties.


The rdoTable Object

You also can open an rdoTable object from the rdoConnection object. This object contains information about each of the columns in the base table that exist on the remote data source. You can use the rdoTables collection to get a listing of all the base objects available through the rdoConnection. The rdoTables collection returns more than just the defined base tables. You also receive all the stored queries (views) available at the remote data source.

WARNING: The rdoTable object is included in Visual Basic 5.0 for backward compatibility with previous versions of the RDO Engine. Although the rdoTable object works as expected in this version of Visual Basic, it might not be supported in future versions of Visual Basic.

Add a new button to the project. Set its Name property to cmdRDOTables and its Caption to RDO Tables. Now add the code in Listing 18.11 to the cmdRDOTables_Click event.

Listing 18.11. Coding the cmdRDOTables_Click event.

Private Sub cmdRDOTables_Click()
    `
    ` get rdo table collection
    `
    Dim rdoEnv As rdoEnvironment
    Dim rdoCon As rdoConnection
    Dim rdoTbl As rdoTable
    Dim strMsg As String
    `
    ` set env/con
    Set rdoEnv = rdoEngine.rdoCreateEnvironment("rdoTEMP", "admin", "")
    Set rdoCon = rdoEnv.OpenConnection("RDC Books5")
    `
    ` update the tables collection
    rdoCon.rdoTables.Refresh
    `
    ` show table properties
    For Each rdoTbl In rdoCon.rdoTables
        strMsg = strMsg & "Name=" & rdoTbl.Name & vbCrLf
        strMsg = strMsg & "RowCount=" & CStr(rdoTbl.RowCount) & vbCrLf
        strMsg = strMsg & "Type=" & CStr(rdoTbl.Type) & vbCrLf
        strMsg = strMsg & "Updatable=" & CStr(rdoTbl.Updatable)
        strMsg = strMsg & vbCrLf
        `
        MsgBox strMsg, vbInformation, "rdoTable"
        strMsg = ""
    Next
    `
    rdoCon.Close
    rdoEnv.Close
    Set rdoTbl = Nothing
    Set rdoCon = Nothing
    Set rdoEnv = Nothing
    `
End Sub


Notice the use of the Refresh method on the rdoTables collection. This is required if you want to get a list of all the table and view objects available from the data source. The rdoTables collection is not automatically refreshed when you create the rdoConnection object.

Save and run the project. After you click the RDO Tables button, you'll see a list of the tables and views available from the data source. Figure 18.11 shows one of those displays.

Figure 18.11. Inspecting the rdoTable properties.


The rdoColumns Object

The rdoColumns object contains detailed information about the contents and pro-
perties of each data column in the rdoTable or rdoResultset object. The rdoColumn object corresponds to the Microsoft Jet Field object. Usually, more than one rdoColumn object exists for each rdoTable or rdoResultset object. All rdoColumn objects are stored in the rdoColumns collection.

Add a new button to the project. Set its Name to cmdRDOColumns and its Caption to Columns. Now add the code in Listing 18.12 to the cmdRDOColumns_Click event.

Listing 18.12. Coding the cmdRDOColumns_Click event

Private Sub cmdRDOColumns_Click()
    `
    ` show rdo columns collection
    `
    Dim rdoEnv As rdoEnvironment
    Dim rdoCon As rdoConnection
    Dim rdoTbl As rdoTable
    Dim rdoCol As rdoColumn
    Dim strMsg As String
    `
    ` set up connection
    Set rdoEnv = rdoEngine.rdoCreateEnvironment("rdoTEMP", "admin", "")
    Set rdoCon = rdoEnv.OpenConnection("RDC Books5")
    `
    ` get table info
    rdoCon.rdoTables.Refresh
    Set rdoTbl = rdoCon.rdoTables("Authors")
    `
    ` get column info
    For Each rdoCol In rdoTbl.rdoColumns
        strMsg = strMsg & "AllowZeroLength=" & CStr(rdoCol.AllowZeroLength) & ÂvbCrLf
        strMsg = strMsg & "Attributes=" & Hex(rdoCol.Attributes) & vbCrLf
        strMsg = strMsg & "ChunkRequired=" & CStr(rdoCol.ChunkRequired) & vbCrLf
        strMsg = strMsg & "Name=" & rdoCol.Name & vbCrLf
        strMsg = strMsg & "OrdinalPosition=" & CStr(rdoCol.OrdinalPosition) & ÂvbCrLf
        strMsg = strMsg & "Required=" & CStr(rdoCol.Required) & vbCrLf
        strMsg = strMsg & "Size=" & CStr(rdoCol.Size) & vbCrLf
        strMsg = strMsg & "SourceColumn=" & rdoCol.SourceColumn & vbCrLf
        strMsg = strMsg & "SourceTable=" & rdoCol.SourceTable & vbCrLf
        strMsg = strMsg & "Type=" & CStr(rdoCol.Type) & vbCrLf
        strMsg = strMsg & "Updatable=" & CStr(rdoCol.Updatable) & vbCrLf
        `
        MsgBox strMsg, vbInformation, "rdoColumn"
        strMsg = ""
        `
    Next
    `
    rdoCon.Close
    rdoEnv.Close
    Set rdoCol = Nothing
    Set rdoTbl = Nothing
    Set rdoCon = Nothing
    Set rdoEnv = Nothing
    `
End Sub


The code in Listing 18.12 displays detailed properties for each column in the Authors table at the data source. Note that the .Value and the .OriginalValue properties have been left out of this example. You also can access these properties in your programs. Also, the value of the .Type property maps to a set of predefined Visual Basic constants. Table 18.8 lists those values.

Table 18.8. Various type values of the rdoColumns.Type property.
Visual Basic Constant Integer Value Description
rdTypeCHAR 1 Fixed-length character string. Length set by Size property.
rdTypeNUMERIC 2 Signed, exact numeric value with precision p and scale s (1 p 15; 0 s p).
rdTypeDECIMAL 3 Signed, exact numeric value with precision p and scale s (1 p 15; 0 s p).
rdTypeINTEGER 4 Signed, exact numeric value with precision 10, scale 0 (signed: -231 n 231-1; unsigned: 0 n 232-1).
rdTypeSMALLINT 5 Signed, exact numeric value with precision 5, scale 0 (signed: -32,768 n 32,767; unsigned: 0 n 65,535).
rdTypeFLOAT 6 Signed, approximate numeric value with mantissa precision 15 (zero or absolute value 10-308 to 10308).
rdTypeREAL 7 Signed, approximate numeric value with mantissa precision 7 (zero or absolute value 10-38 to 1038).
rdTypeDOUBLE 8 Signed, approximate numeric value with mantissa precision 15 (zero or absolute value 10-308 to 10308).
rdTypeDATE 9 Date: Data-source dependent.
rdTypeTIME 10 Time: Data-source dependent.
rdTypeTIMESTAMP 11 TimeStamp: Data-source dependent.
rdTypeVARCHAR 12 Variable-length character string. Maximum length: 255.
rdTypeLONGVARCHAR -1 Variable-length character string. Maximum length determined by data source.
rdTypeBINARY -2 Fixed-length binary data. Maximum length: 255.
rdTypeVARBINARY -3 Variable-length binary data. Maximum length: 255.
rdTypeLONGVARBINARY -4 Variable-length binary data. Maximum data-source dependent.
rdTypeBIGINT -5 Signed, exact numeric value with precision 19 (signed) or 20 (unsigned); scale 0 (signed: -263 n 263-1; unsigned: 0 n 264-1).
rdTypeTINYINT -6 Signed, exact numeric value with precision 3, scale 0; (signed: -128 n 127; unsigned: 0 n 255).
rdTypeBIT -7 Single binary digit.


The exact data type returned in each column is data-source dependent. Not all data sources support all data types listed here.

Save and run the project. After you click the RDO Columns button, you'll see a series of dialog boxes that show the details of each column in the Authors table. Figure 18.12 shows one of those dialog boxes.

Figure 18.12. Viewing the rdoColumn properties.


The rdoQuery Object

The rdoQuery object provides a method for creating and executing defined queries or views on the remote data source. The rdoQuery object is the RDO version of the Microsoft Jet QueryDef object. You can create more than one rdoQuery object on each rdoConnection object. All rdoQuery objects are accessed through the rdoQueries collection object.

Add a new button to the form. Set its Name to cmdRDOQueries and its Caption to RDO Queries. Add the code in Listing 18.13 to the cmdRDOQueries_Click event.

Listing 18.13. Coding for the cmdRDOQueries_Click event.

Private Sub cmdRDOQueries_Click()
    `
    ` example rdo query
    `
    Dim rdoEnv As rdoEnvironment
    Dim rdoCon As rdoConnection
    Dim rdoQry As rdoQuery
    Dim rdoNewQry As rdoQuery
    Dim rdoRS As rdoResultset
    Dim strMsg As String
    Dim strSQL As String
    Dim aryLockType As Variant
    Dim aryType As Variant
    Dim aryCursorDriver As Variant
    `
    aryCursorDriver = Array("rdUseIfNeeded", "rdUseOdbc", "rdUseServer", Â"rdUseClientBatch", "rduseNone")
    aryLockType = Array("rdConcurReadOnly", "rdConcurLock", "rdConcurRowVer", Â"rdConcurValues", "rdConCurBatchEdit")
    aryType = Array("rdOpenForwardOnly", "rdOpenKeyset", "rdOpenDynamic", Â"rdOpenStatic")
    `
    strSQL = "SELECT * FROM Publishers,Titles WHERE ÂPublishers.PubID=Titles.PubId"
    `
    ` set env/con
    Set rdoEnv = rdoEngine.rdoCreateEnvironment("rdoTEMP", "admin", "")
    Set rdoCon = rdoEnv.OpenConnection("RDC Books5")
    `
    ` build a new query & collect data set
    Set rdoNewQry = rdoCon.CreateQuery("rdoQryTest", strSQL)
    Set rdoRS = rdoNewQry.OpenResultset()
    `
    ` show details
    For Each rdoQry In rdoCon.rdoQueries
        strMsg = strMsg & "BindThreshold=" & CStr(rdoQry.BindThreshold) & vbCrLf
        strMsg = strMsg & "CursorType=" & aryCursorDriver(rdoQry.CursorType) & ÂvbCrLf
        strMsg = strMsg & "hStmt=" & CStr(rdoQry.hStmt) & vbCrLf
        strMsg = strMsg & "KeysetSize=" & CStr(rdoQry.KeysetSize) & vbCrLf
        strMsg = strMsg & "LockType=" & aryLockType(rdoQry.LockType) & vbCrLf
        strMsg = strMsg & "MaxRows=" & CStr(rdoQry.MaxRows) & vbCrLf
        strMsg = strMsg & "Name=" & rdoQry.Name & vbCrLf
        strMsg = strMsg & "Prepared=" & rdoQry.Prepared & vbCrLf
        strMsg = strMsg & "QueryTimeOut=" & CStr(rdoQry.QueryTimeout) & vbCrLf
        strMsg = strMsg & "RowsAffeced=" & CStr(rdoQry.RowsAffected) & vbCrLf
        strMsg = strMsg & "RowsetSize=" & CStr(rdoQry.RowsetSize) & vbCrLf
        strMsg = strMsg & "SQL=" & rdoQry.SQL & vbCrLf
        strMsg = strMsg & "StillExecuting=" & CStr(rdoQry.StillExecuting) & ÂvbCrLf
        strMsg = strMsg & "Type=" & aryType(rdoQry.Type) & vbCrLf
        `
        MsgBox strMsg, vbInformation, "rdoQuery"
        strMsg = ""
        `
    Next
    `
    rdoNewQry.Close
    rdoCon.Close
    rdoEnv.Close
    Set rdoQry = Nothing
    Set rdoNewQry = Nothing
    Set rdoCon = Nothing
    Set rdoEnv = Nothing
    `
End Sub


Notice the use of the OpenResultset method on the rdoQuery object. This is the way to fetch rows from the data source using the rdoQuery object as the base. Save and run the project. After you click the RDO Queries button, you'll see a detailed listing of the properties of the query, as shown in Figure 18.13.

Figure 18.13. Displaying the rdoQuery properties.

The rdoParameter Object

The rdoParameter object enables you to populate the various predefined runtime parameters of a sorted query so that you can create flexible queries that can be adjusted at runtime by programming code or user input. You can define more than one rdoParameter object for each rdoQuery object. All the parameter objects are accessed via the rdoParameters collection.

Add one last button to the project. Set its Name to cmdRDOParameter and its Caption to Parameters. Then add the code in Listing 18.14 to the cmdRDOParameter_Click event.

Listing 18.14. Coding for the cmdRDOParameter_Click event.

Private Sub cmdRDOParameters_Click()
    `
    ` example of rdo parameters
    `
    Dim rdoEnv As rdoEnvironment
    Dim rdoCon As rdoConnection
    Dim rdoQry As rdoQuery
    Dim rdoRS As rdoResultset
    Dim rdoPrm As rdoParameter
    Dim strMsg As String
    Dim strSQL As String
    `
    strSQL = "SELECT * FROM Authors WHERE Name Like ?"
    `
    ` open env/con
    Set rdoEnv = rdoEngine.rdoCreateEnvironment("rdoTEMP", "admin", "")
    Set rdoCon = rdoEnv.OpenConnection("RDC Books5")
    `
    ` create a parameter query
    Set rdoQry = rdoCon.CreateQuery("rdoQryPrm", strSQL)
    `
    ` load parameter
    rdoQry.rdoParameters(0).Value = "%s%"
    rdoQry.rdoParameters(0).Type = rdTypeCHAR
    `
    ` get result from parameterized query
    Set rdoRS = rdoQry.OpenResultset(rdOpenKeyset)
    rdoRS.MoveLast
    `
    ` show some details
    strMsg = strMsg & "Name=" & rdoRS.Name & vbCrLf
    strMsg = strMsg & "Parameter=" & rdoQry.rdoParameters(0) & vbCrLf
    strMsg = strMsg & "RowCount=" & CStr(rdoRS.RowCount) & vbCrLf
    `
    MsgBox strMsg, vbInformation, "rdoParameters"
    `
    rdoQry.Close
    rdoCon.Close
    rdoEnv.Close
    Set rdoPrm = Nothing
    Set rdoQry = Nothing
    Set rdoRS = Nothing
    Set rdoCon = Nothing
    Set rdoEnv = Nothing
    `
End Sub


The code in Listing 18.14 first opens the data connection and then creates a parameterized query (note the ? that represents the parameter portion of the statement). Then the rdoParameter object is populated by using the Value and Type properties. Notice that it is not necessary to surround string parameters in single or double quotation marks. This is handled by the remote data source. Then the OpenResultset method is used to populate the dataset, and the MoveLast method is used to force the cursor to traverse the entire record collection. This ensures an accurate value for the Rowcount property of the rdoResultset. Finally, the results appear in a dialog box, as shown in Figure 18.14.

Figure 18.14. Viewing the rdoParameters properties.


TIP: The code in Listing 18.14 uses the Type property of the rdoParameter object. This is not a required property when creating rdoParameter objects, but it is highly recommended. If no Type property is set, the remote data source makes a guess at the data type of the parameter. This can lead to unexpected errors. If you have a CHAR column in your table that contains a shoe size (8.5, 9, and so on), for example, and use this column in a parameterized query, it is possible that passing a value of 8 will be misinterpreted by the RDBMS as an integer or long value instead of a CHAR value.

That completes your tour of the RDO programming object collection.

Summary

In today's lesson, you learned about two alternative methods for accessing remote data. You learned that you can use the RDC to create simple data-entry forms with data-bound controls. You also learned to use the RDOs to create Visual Basic 5 programs that can access data from a remote RDBMS.

Along with the details of the RDC and the RDOs, you also learned some of the basics of remote data access in general:

You also learned the details of the following Microsoft RDOs:

Quiz

1. What is the difference between the standard data control and the Remote Data Control?

2. What is a cursor driver?

3. What are the four dataset types?

4. What are the five lock types?

5. What is the Microsoft Jet equivalent of the rdoResultset object?

6. What is the RDO equivalent of the Microsoft Jet Workspace object?

Exercise

You have been asked to build a quick utility that scans any RDBMS database and provides a list of all the tables and views in that database. This will be used to catalog old RDBMS databases and assist in maintenance chores.

Create a simple data entry form that allows users to select any available ODBC data source and then view all tables and views in the data source in a list box. Hint: Use the rdo.connection method with an empty DSN string to get the ODBC dialog to appear.