CodeItBetter Programming Another VB Programming Blog

'Database - How to Execute an ad hoc query using ADO and display the results in a grid
'When the user clicks the Connect button, open the database connection.

Private m_DBConnection As ADODB.Connection
' Connect to the database.
Private Sub cmdConnect_Click()
    ' If a database is currently open, close it.
    If Not m_DBConnection Is Nothing Then
        If m_DBConnection.State <> adStateClosed Then m_DBConnection.Close
    End If
 
    Set m_DBConnection = New ADODB.Connection
    m_DBConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & txtDatabase.Text & ";" & _
        "Persist Security Info=False"
    m_DBConnection.Open
 
    cmdExecute.Enabled = True
End Sub
 
'When the user clkicks Execute, use the Connection object's Execute method to
'perform the query and return the results in a Recordset object.

'Use the recordset's Fields collection to get the field names and values. Keep
'track of the width of the text in each column and size the grid's columns
'accordingly.

Private Sub cmdExecute_Click()
    Dim rs As ADODB.Recordset
    Dim r As Integer
    Dim c As Integer
    Dim num_cols As Integer
    Dim col_wid() As Single
    Dim new_wid As Single
 
    ' Open the Recordset.
    Set rs = m_DBConnection.Execute(txtCommand.Text, , adCmdText)
 
    ' Hide the grid.
    flxResults.Visible = False
    DoEvents
 
    ' Display the results.
    If rs.EOF Then
        flxResults.Rows = 1
        flxResults.Cols = 1
        flxResults.TextMatrix(0, 0) = "No Records Selected"
        num_cols = 1
        ReDim col_wid(0 To 0)
        col_wid(0) = TextWidth(flxResults.TextMatrix(0, 0))
    Else
        ' Make room for column widths.
        num_cols = rs.Fields.Count
        ReDim col_wid(0 To num_cols - 1)
 
        ' Set column headers.
        flxResults.Rows = 2
        flxResults.Cols = num_cols
        flxResults.FixedCols = 0
        flxResults.FixedRows = 1
        For c = 0 To num_cols - 1
            flxResults.TextMatrix(0, c) = rs.Fields(c).Name
 
            ' See if we need to enlarge the column.
            new_wid = TextWidth(rs.Fields(c).Name)
            If col_wid(c) < new_wid Then col_wid(c) = new_wid
        Next c
 
        ' Display the data.
        Do Until rs.EOF
            r = r + 1
            flxResults.Rows = r + 1
            For c = 0 To rs.Fields.Count - 1
                flxResults.TextMatrix(r, c) = rs.Fields(c).Value
 
                ' See if we need to enlarge the column.
                new_wid = TextWidth(rs.Fields(c).Value)
                If col_wid(c) < new_wid Then col_wid(c) = new_wid
            Next c
            rs.MoveNext
        Loop
    End If
 
    ' Set the grid's column widths.
    For c = 0 To num_cols - 1
        flxResults.ColWidth(c) = col_wid(c) + 120
    Next
 
    ' Display the grid.
    flxResults.Visible = True
 
    rs.Close
End Sub