Connect and ReturnsRecords Properties Example (Client/Server)

This example uses the Connect and ReturnsRecords properties to select the top five book titles from a Microsoft SQL Server database based on year-to-date sales amounts. In the event of an exact match in sales amounts, the example increases the size of the list displaying the results of the query and prints a message explaining why this occurred.

Sub ClientServerX1()

   Dim dbsCurrent As Database
   Dim qdfPassThrough As QueryDef
   Dim qdfLocal As QueryDef
   Dim rstTopFive As Recordset
   Dim strMessage As String

   ' Open a database from which QueryDef objects can be 
   ' created.
   Set dbsCurrent = OpenDatabase("DB1.mdb")

   ' Create a pass-through query to retrieve data from
   ' a Microsoft SQL Server database.
   Set qdfPassThrough = _
      dbsCurrent.CreateQueryDef("AllTitles")
   qdfPassThrough.Connect = _
      "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers"
   qdfPassThrough.SQL = "SELECT * FROM titles " & _
      "ORDER BY ytd_sales DESC"
   qdfPassThrough.ReturnsRecords = True

   ' Create a temporary QueryDef object to retrieve
   ' data from the pass-through query.
   Set qdfLocal = dbsCurrent.CreateQueryDef("")
   qdfLocal.SQL = "SELECT TOP 5 title FROM AllTitles"

   Set rstTopFive = qdfLocal.OpenRecordset()

   ' Display results of queries.
   With rstTopFive
      strMessage = _
         "Our top 5 best-selling books are:" & vbCr

      Do While Not .EOF
         strMessage = strMessage & "  " & !Title & _
            vbCr
         .MoveNext
      Loop

      If .RecordCount > 5 Then
         strMessage = strMessage & _
            "(There was a tie, resulting in " & _
            vbCr & .RecordCount & _
            " books in the list.)"
      End If

      MsgBox strMessage
      .Close
   End With

   ' Delete new pass-through query because this is a
   ' demonstration.
   dbsCurrent.QueryDefs.Delete "AllTitles"
   dbsCurrent.Close

End Sub