CodeItBetter Programming Another VB Programming Blog

'Database - How to use ADO to load data into a FlexGrid control
'Open the database and execute the query. Loop through the recordset's Fields
'array to get the field names and use them as column headers.

'For each record in the Recordset, loop through the fields saving their values
'in the FlexGrid.

'Keep track of the largest text width in each column and make each column big
'enough to display the text.

Private Sub Form_Load()
    Dim db_file As String
    Dim statement As String
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim c As Integer
    Dim r As Integer
    Dim col_wid() As Single
    Dim field_wid As Single
 
    ' Get the data.
    db_file = App.Path
    If Right$(db_file, 1) <> "\" Then db_file = db_file & "\"
    db_file = db_file & "books.mdb"
 
    ' Open a connection.
    Set conn = New ADODB.Connection
    conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & db_file & ";" & _
        "Persist Security Info=False"
    conn.Open
 
    ' Select the data.
    statement = "SELECT * FROM Books ORDER BY Title"
 
    ' Get the records.
    Set rs = conn.Execute(statement, , adCmdText)
 
    ' Use one fixed row and no fixed columns.
    MSFlexGrid1.Rows = 2
    MSFlexGrid1.FixedRows = 1
    MSFlexGrid1.FixedCols = 0
 
    ' Display column headers.
    MSFlexGrid1.Rows = 1
    MSFlexGrid1.Cols = rs.Fields.Count
    ReDim col_wid(0 To rs.Fields.Count - 1)
    For c = 0 To rs.Fields.Count - 1
        MSFlexGrid1.TextMatrix(0, c) = rs.Fields(c).Name
        col_wid(c) = TextWidth(rs.Fields(c).Name)
    Next c
 
    ' Display the values for each row.
    r = 1
    Do While Not rs.EOF
        MSFlexGrid1.Rows = MSFlexGrid1.Rows + 1
        For c = 0 To rs.Fields.Count - 1
            MSFlexGrid1.TextMatrix(r, c) = rs.Fields(c).Value
 
            ' See how big the value is.
            field_wid = TextWidth(rs.Fields(c).Value)
            If col_wid(c) < field_wid Then col_wid(c) = field_wid
        Next c
 
        rs.MoveNext
        r = r + 1
    Loop
 
    ' Close the recordset and connection.
    rs.Close
    conn.Close
 
    ' Set the column widths.
    For c = 0 To MSFlexGrid1.Cols - 1
        MSFlexGrid1.ColWidth(c) = col_wid(c) + 240
    Next c
End Sub