Populate a FlexGrid With Data From an ADO Recordset

Category:

Database

Type:

Snippets

Difficulty:

Beginning

Author:

Alastair Vance

Version Compatibility:

More information:

See comments within code for usage and example

Instructions: Copy the declarations and code below and paste directly into your VB project.

Declarations:
' (None)

Code:

Public Function PopulateFlexGrid(FlexGrid As Object, _
   rs As Object) As Boolean
'*******************************************************
'PURPOSE: Populate MSFlexGrid with data from an
'         ADO Recordset
'PARAMETERS:  FlexGrid: MsFlexGrid to Populate
'             RS: Open ADO Recordset
'RETURNS:     True if successful, false otherwise
'REQUIRES:    -- Reference to Microsoft Active Data Objects
'             -- Recordset should be open with cursor set at 
'                first row when passed and must 
'                support recordcount property
'             -- FlexGrid should be empty when passed
'EXAMPLE:
'Dim conn As New ADODB.Connection
'Dim rs As New ADODB.Recordset
'Dim sConnString As String
'
'sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyDatabase.mdb"
'conn.Open sConnString
'rs.Open " SELECT * FROM MyTable", oConn, adOpenKeyset, adLockOptimistic
'PopulateFlexGrid MSFlexGrid1, rs
'
'rs.Close
'conn.Close
'***********************************************************
            
On Error GoTo ErrorHandler

If Not TypeOf FlexGrid Is MSFlexGrid Then Exit Function
If Not TypeOf rs Is ADODB.Recordset Then Exit Function

Dim i As Integer
Dim J As Integer

FlexGrid.FixedRows = 1
    FlexGrid.FixedCols = 0
    
    If Not rs.EOF Then
    
        FlexGrid.Rows = rs.RecordCount + 1
        FlexGrid.Cols = rs.Fields.Count

For i = 0 To rs.Fields.Count - 1
            FlexGrid.TextMatrix(0, i) = rs.Fields(i).Name
           Next
    
        i = 1
        Do While Not rs.EOF
    
            For J = 0 To rs.Fields.Count - 1
                If Not IsNull(rs.Fields(J).Value) Then
                    FlexGrid.TextMatrix(i, J) = _
                       rs.Fields(J).Value
                End If
            Next
    
        i = i + 1
        rs.MoveNext
        Loop
    
    
    End If
PopulateFlexGrid = True
ErrorHandler:
Exit Function
End Function