Tutorial 9: Cheap Dynamic Queries Example

Okay, I just grabbed the database for the houses dynamic queries example for this example. It's really fast and cheap and illustrates how you could do a couple of dynamic queries using the ADO programming route. Here's the general idea for the program:

set some default query to start with (I picked build date > 1964, and that's it!)
if you want to add a query with a new element, I just "and" it to the end of the query string.
every time a query value is changed, the query string is rebuilt and the database is requeried.
- to rebuild the query each time, I have several string values representing query fragments, and they're just stapled together with a simple function and returned as one unified string.

Just like that! You can download my project files and the same db below,

Download dq_database.zip

And also have a look at the source code below. I won't go through the design process, the only thing I did that was out of the ordinary was changing the visibility property of the ADO Data Control to false. Anyways, the code:

Option Explicit

Dim strPrice As String
Dim strYear As String

' this button just spits out a count of how many records the latest dyno query returned
Private Sub Command1_Click()
    MsgBox Adodc1.Recordset.RecordCount, vbOKOnly, "# of Records Returned"
End Sub

' this button will step through the records one by one and save their ids in a string,
' which is then displayed in a message box.
Private Sub Command2_Click()

    Dim duh As String
    duh = ""

    With Adodc1.Recordset
        ' move to the first record, top o' the list
        .MoveFirst
        While Not .EOF
            ' this saves the value in the ID field of the current record.  Hint: you can
            ' do the same thing for any other column in the db / record / whatever
            ' (so .Recordset!YearBuilt would return that number for the current record)
            duh = duh & " " & !ID
            .MoveNext
        Wend
    End With
    
    MsgBox duh, vbOKOnly, "IDs of Records Returned"
End Sub

Private Sub Form_Load()
    ' init some values for the query strings (note: it _always_ needs a query for YearBuilt,
    ' I just set it to be one less than the lowest number in the db.  This is a cheap hack.
    ' It does get requeried on the fly, this just sort of seeds the preliminary results. The
    ' reason there needs to be a yearbuilt query is that everything else gets anded on the
    ' end, and without it the logic won't make sense to the sql query dude.)
    strPrice = ""
    strYear = " YearBuilt > 1964"
    
    With Adodc1
        ' initialize our connection to the database
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
            App.Path & "\housedata.mdb;Persist Security Info=False"
        ' set up the first query
        .RecordSource = BuildQuery()
        ' refresh the data source to make sure the query goes through. I'm still unclear
        ' as to how a bunch of this works...
        .Refresh
    End With

    Label2.Caption = "Year Built > " & Slider2.Value
End Sub

' here's some hip query action happening and stuff.  (slider1 does price, slider2
' does construction date)
Private Sub Slider1_Change()
    strPrice = " and AskingPrice < " & Slider1.Value
    Label1.Caption = "Asking Price < $" & Slider1.Value & ".00"
    Adodc1.RecordSource = BuildQuery()
    Adodc1.Refresh
End Sub

Private Sub Slider2_Change()
    strYear = " YearBuilt > " & Slider2.Value
    Label2.Caption = "Year Built > " & Slider2.Value
    Adodc1.RecordSource = BuildQuery()
    Adodc1.Refresh
End Sub

' this function just builds an sql query based on a bunch of substrings that are managed
' by individual controls in this project and are stored globally.
Private Function BuildQuery() As String
    BuildQuery = "select * from Houses where " & strYear & strPrice
End Function