The top of this program holds three columns of controls. The left column contains combo boxes holding the names of the fields in a database table. The middle column holds operators such as =, <, and >=. The right column holds text boxes. The user can use these controls to determine how the program queries the database. For example, if the user selects the Title field from the first combo box, the >= operator from the second, and enters R in the first text box, the program searches for records where the Title field has value >= R.

The following code executes when the form loads.

Private ConnectString As String
Private m_DBConnection As ADODB.Connection
Private m_FieldNames() As String
Private m_DataTypes() As Long

' Get the field names and data types.
Private Sub Form_Load()
Dim db_name As String
Dim rs As ADODB.Recordset
Dim num_cols As Integer
Dim c As Integer
Dim i As Integer

    db_name = App.Path & "\books.mdb"

    Set m_DBConnection = New ADODB.Connection
    ConnectString = _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & db_name & ";" & _
        "Persist Security Info=False"
    m_DBConnection.ConnectionString = ConnectString

    m_DBConnection.Open
    Set rs = m_DBConnection.Execute( _
        "SELECT TOP 1 * FROM Books", , adCmdText)

    num_cols = rs.Fields.Count
    ReDim m_FieldNames(0 To num_cols - 1)
    ReDim m_DataTypes(0 To num_cols - 1)
    For c = 0 To num_cols - 1
        m_FieldNames(c) = rs.Fields(c).Name
        m_DataTypes(c) = rs.Fields(c).Type
    Next c
    
    ' Add the field names to the field ComboBoxes.
    For i = cboField.LBound To cboField.UBound
        cboField(i).AddItem ""
        For c = 0 To num_cols - 1
            cboField(i).AddItem m_FieldNames(c)
        Next c
    Next i

    m_DBConnection.Close
End Sub

When the user makes selections and clicks Query, the following code executes.

' Build and execute the appropriate query.
Private Sub cmdQuery_Click()
Dim query As String
Dim where_clause As String
Dim i As Integer
Dim delimiter As String
Dim value As String
Dim column_num As Integer

    where_clause = ""
    For i = cboField.LBound To cboField.UBound
        ' See if the field and operator are non-blank.
        If ((cboField(i).ListIndex <= 0) Or _
            (cboOperator(i).ListIndex <= 0)) _
        Then
            ' Don't use this row. Clear it to prevent
            ' confusion.
            cboField(i).ListIndex = 0
            cboOperator(i).ListIndex = 0
            txtValue(i).Text = ""
        Else
            ' See what delimiter we need for this type of
            ' field.
            delimiter = ""
            value = txtValue(i).Text
            column_num = cboField(i).ListIndex - 1
            If (m_DataTypes(column_num) = adVarChar) Or _
               (m_DataTypes(column_num) = adChar) Or _
               (m_DataTypes(column_num) = adVarWChar) Or _
               (m_DataTypes(column_num) = adWChar) _
            Then
                delimiter = "'"
                value = Replace$(value, "'", "''")
            ElseIf (m_DataTypes(column_num) = adDate) Or _
                   (m_DataTypes(column_num) = adDBDate) Or _
                   (m_DataTypes(column_num) = adDBTime) _
            Then
                ' Use # for Access, ' for SQL Server.
                delimiter = "#"
            End If

            ' Add the constraint to the WHERE clause.
            where_clause = where_clause & " AND " & _
                cboField(i).List(cboField(i).ListIndex) & "" & _
                    "" & _
                cboOperator(i).List(cboOperator(i).ListIndex) _
                    & " " & _
                delimiter & value & delimiter
        End If  ' if field and operator are selected.
    Next i  ' cboField.LBound To cboField.UBound

    ' If where_clause is non-blank, remove the initial " AND
    ' ".
    If Len(where_clause) > 0 Then where_clause = _
        Mid$(where_clause, 6)

    ' Compose the query.
    query = "SELECT * FROM Books"
    If Len(where_clause) > 0 Then query = query & " WHERE " _
        & where_clause
    ' Debug.Print "Query: " & query

    ' Connect the ADODC to the database.
    adodcBooks.ConnectionString = ConnectString
    adodcBooks.RecordSource = query
    adodcBooks.Refresh

    ' Bind the ADODC to the DataGrid.
    Set dgBooks.DataSource = adodcBooks
End Sub

When it builds each piece of the WHERE clause, the code uses delimiters for string and date values. It uses a single quote ' for strings, and it uses a # for dates. (Access databases require # delimiters for dates. SQL Server databases require ' delimiters for dates.)

After is has built the WHERE clause, the program composes a final query. It sets the ADO Data Control's RecordSource property to the query and refreshes the control. Finally it makes the DataGrid control use the ADO Data Control as its DataSource.