Option Explicit

Const DATA_SOURCE As String = "retstest"
Const USER_NAME As String = "Joe"
Const PASSWORD As String = "Schmoe"

Public Sub QueryRETS_ADO()
    Dim conn As ADODB.Connection
    Dim sql As String
    Dim rs As ADODB.Recordset
    
    ' Open the connection
    Set conn = New ADODB.Connection
    conn.Open "Provider=MSDASQL.1;Data Source=" & DATA_SOURCE _
              & ";User ID=" & USER_NAME _
              & ";Password=" & PASSWORD
    
    ' Build the query
    sql = "SELECT ListingID,ListDate,ListPrice" _
          & " FROM data:Property:ResidentialProperty" _
          & " WHERE ListPrice > 0"
    
    ' Execute the query
    Set rs = New ADODB.Recordset
    rs.Open Source:=sql, ActiveConnection:=conn, CursorType:=adOpenForwardOnly, LockType:=adLockReadOnly
        
    Debug.Print "BOF: "; rs.BOF
    Debug.Print "EOF: "; rs.EOF
    
    ' Output query results to the Immediate window
    While Not rs.EOF
        Debug.Print "Listing ID: "; rs!ListingID; "  Listing date:"; rs!ListDate
        rs.MoveNext
    Wend
    
    ' Close connections
    rs.Close
    Set rs = Nothing
    conn.Close
    Set conn = Nothing
End Sub