Passing the Data: How to open a RDO Resultset in a Class Module, and then utilize it in your project.


The following sample project is based on obtaining data from Visual Basic 6.0 via RDO 2.0. The backend data is from the ‘pubs’ sample SQL Server Database, version 6.5 with Service Pack 4 (SP4), using the SQL Server driver version 3.60.0319 (which comes with MDAC 2.0). If you do not have Visual Basic 6.0, in order to obtain the most recent ODBC drivers, the easiest thing to do would be to install ADO 2.0 on your development computer, (as long as ADO 1.5 is NOT already installed). Click here to go to the MDAC download page.

In this project, I open a rdoConnection and rdoResultset in the Class Module. I then use these procedures to return the number of records from two different tables in the database.

Why bother? Well first of all, the Class Module is the basic building block of any ActiveX application. If you can create a Class Module with a few methods, and properties, you can easily port this to an ActiveX DLL or ActiveX EXE project. The goal of OOP is reusable code. Again, why re-invent the wheel every time you code?

Second, you can use this example for troubleshooting your own applications. Imagine that you can connect to SQL Server without errors using this example, but your application fails against your database. With this information you know that the problem is either with your code, or the ODBC driver. Then, you modify the example to obtain data from two different tables in your database (for this to work, make sure you are querying tables with different RowCounts). If this works, then you’ve determined that the problem is not the database, or your code, but originates with the driver. Then you can go to the appropriate website, and download an update, or contact the right company for tech support.

Here’s the step by step example:

1. Start a new Standard EXE project in Visual Basic. Form1 is created by default.
Please note that the recommended practice would be to set the Name property of Form1 and Project1 to something UNIQUE. For the purposes of this simple example, I am not naming any of the controls, the Form, or the Project. Don’t make it a habit! Just imagine 50 Project1’s, and you’ll get the picture.

2. In the Project Explorer, right-click on Project1, select Add, select Class Module, then select Class Module again. Class1 will be created by default.

3. From the Project menu, select References, check off the "Microsoft Remote Data Object 2.0" Library, and click OK.

4. Add two command buttons to Form1, and leave the default names, Command1 and Command2.

5. Paste the following code in the General Declaration's section of Class1:

Option Explicit
' Declare your variables
     Public cn As rdoConnection
     Public rs As rdoResultset

Public Sub Connect()
     Set cn = New rdoConnection

' This opens an RDO Connection.
' You would need to change the MyServer parameter below to the name of your server.
     With cn
         .Connect = "UID=sa;PWD=;database=pubs;" _
         & "Server=MyServer;" _
         & "driver={SQL Server};"
         .QueryTimeout = 5
         .CursorDriver = rdUseOdbc
         .EstablishConnection rdDriverNoPrompt
     End With
End Sub

Public Sub GetRecords(sSelect As String)
' This opens an RDO Resultset.
     Set rs = cn.OpenResultset(sSelect, rdOpenKeyset, rdConcurValues)
End Sub

Private Sub Class_Terminate()
' Clean up after the RDO Objects; first by closing, then by using the Nothing keyword.
     rs.Close
     Set rs = Nothing
     cn.Close
     Set cn = Nothing
End Sub

6. Paste the following code in the General Declaration's section of Form1:

Option Explicit
' Declare your variables
     Public MyClass As New Class1

Private Sub Command1_Click()
' This returns the number of records in the Authors table.
     MyClass.Connect
     MyClass.GetRecords ("Select * from dbo.Authors")
     MsgBox MyClass.rs.RowCount
End Sub

Private Sub Command2_Click()
' This returns the number of records in the Titles table.
     MyClass.GetRecords ("Select * from dbo.Titles")
     MyClass.rs.Requery
     MsgBox MyClass.rs.RowCount
End Sub

Private Sub Form_Load()
' This labels the captions of the Command Buttons.
     Command1.Caption = "Get Records in Authors"
     Command2.Caption = "Get Records in Titles"
End Sub

Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
' This destroys/cleans up after the class object.
     Set MyClass = Nothing
End Sub

7. Press the F8 key to step through the code in the program. Watch how the cursor moves from the Form1 code window to the Class1 code window, and back again.


Back to top
Home