MoreResults Method Example

The following example illustrates use of the MoreResults method. In this example, an SQL query containing three separate SELECT queries is executed. The first query simply returns the number of publishers in the Publishers table. The next two queries each return two columns resulting from more complex join operations. All of this information is displayed in a ListBox control.

Option Explicit
Dim Cn As New rdoConnection
Dim Rs As rdoResultset
Dim SQL As String

Private Sub Test_Click()
SQL = "Select Count(*) From Publishers" _
   & " Select Pub_Name, Title  " _
   & " From Publishers P, Titles T" _
   & " Where P.Pub_ID = T.Pub_ID" _
   & " Select Au_Lname, Title " _
   & " From Titles T, TitleAuthor Ta, Authors A" _
   & " Where T.title_ID = ta.Title_ID " _
   & " and Ta.Au_ID = A.Au_ID"
Set Rs = Cn.OpenResultset(SQL, rdOpenForwardOnly, _
   rdConcurReadOnly)
' From the first set of results
List1.AddItem "Publishers: " & Rs(0)  
'
'   Loop through all of the remaining result sets
'
Do While Rs.MoreResults
   List1.AddItem Rs(0).Name & " - " & Rs(1).Name
   Do Until Rs.EOF
      List1.AddItem Rs(0) & "     -    " & Rs(1)
      Rs.MoveNext
   Loop
Loop
End Sub

Private Sub Form_Load()
On Error GoTo CnEh
With Cn
   .Connect = "UID=;PWD=;Database=Pubs;" _
   & "Server=SEQUEL;Driver={SQL Server}" _
   & "DSN='';"
   .LoginTimeout = 5
   .CursorDriver = rdUseOdbc
   .EstablishConnection rdDriverNoPrompt, True
End With
Exit Sub

CnEh:
Dim er As rdoError
Debug.Print Err, Error
For Each er In rdoErrors
   Debug.Print er.Description, er.Number
Next er
Resume Next
End Sub