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