The example uses ADO.NET to examine the database's schema. When you click the Analyze button, the program opens Excel and creates a new Workbook and Worksheet. It then opens the database.

To learn about the database, the program calls the database connection's GetOleDbSchemaTable method. This method builds a series of rows that contain information about a specific part of the database. For example, the program starts by selecting information about tables. It passes GetOleDbSchemaTable an array containing three Nothings and the string "TABLE." This makes the method return only rows that have "TABLE" in their third column. This is the "table type" column and matching the value "TABLE" excludes access and system tables. See the help for the GetOleDbSchemaTable method to learn more about this.

The program then loops over the rows returned describing the tables. It displays each table's name, description, creation date, and last modification date.

Next the program performs a similar schema query for the table's fields. It loops over the fields displaying their names, types, and other information.

The program takes similar steps to describe the table's indexes.

After it has described all of the tables, the program performs the same steps again to display information about the database's foreign key relations.

Note that the GetOleDbSchemaTable method can provide information on all sorts of database entities. See the online help for more information.

Private Sub btnAnalyze_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles btnAnalyze.Click
    Me.Cursor = Cursors.WaitCursor
    System.Windows.Forms.Application.DoEvents()

    ' Open Excel and create a new workbook.
    Dim excel_app As New Excel.Application
    excel_app.Visible = True
    Dim excel_workbook As Excel.Workbook = _
        excel_app.Workbooks.Add()
    Dim excel_worksheet As Excel.Worksheet = _
        DirectCast(excel_workbook.Sheets(1), _
            Excel.Worksheet)
    excel_worksheet.Range("A1", "Z1").Columns.ColumnWidth = _
        20

    ' Examine the tables.
    Dim row_num As Integer = 1

    ' Section heading.
    excel_worksheet.Cells(row_num, 1) = "Tables"
    With excel_worksheet.Range("A" & row_num).Font
        .Size = 25
        .Bold = True
        .Color = RGB(255, 0, 0)
    End With
    row_num += 1

    ' Open the database connection.
    DBConn.ConnectionString = _
        "Provider=""Microsoft.Jet.OLEDB.4.0"";" & _
        "Data Source=""" & txtDatabase.Text & """;" & _
        "Mode=Share Deny None;" & _
        "persist security info=False;"
    DBConn.Open()

    ' Get schema information for TABLE type tables only.
    ' This excludes access and system tables.
    Dim dt_table As System.Data.DataTable = _
        DBConn.GetOleDbSchemaTable( _
            OleDb.OleDbSchemaGuid.Tables, _
            New Object() {Nothing, Nothing, Nothing, _
                "TABLE"})

    ' Loop over the tables.
    For Each dr_table As DataRow In dt_table.Rows
        ' Display the table's name in the form's title bar.
        Dim table_name As String = _
            dr_table("TABLE_NAME").ToString
        Me.Text = "MDBAnalyzer: " & table_name
        System.Windows.Forms.Application.DoEvents()

        ' Display the table's name, description, date
        ' created, and date modified.
        excel_worksheet.Cells(row_num, 1) = table_name
        With excel_worksheet.Range("A" & row_num).Font
            .Size = 20
            .Bold = True
            .Color = RGB(0, 0, 255)
        End With
        row_num += 1

        If dr_table.IsNull("DESCRIPTION") Then
            excel_worksheet.Cells(row_num, 1) = "<no " & _
                "description>"
        Else
            excel_worksheet.Cells(row_num, 1) = _
                dr_table("DESCRIPTION").ToString
        End If
        row_num += 1
        excel_worksheet.Cells(row_num, 1) = "Created:"
        excel_worksheet.Cells(row_num, 2) = _
            dr_table("DATE_CREATED").ToString
        row_num += 1
        excel_worksheet.Cells(row_num, 1) = "Modified:"
        excel_worksheet.Cells(row_num, 2) = _
            dr_table("DATE_MODIFIED").ToString
        row_num += 1

        ' Subsection header.
        excel_worksheet.Cells(row_num, 1) = "Fields"
        With excel_worksheet.Range("A" & row_num).Font
            .Size = 16
            .Bold = True
            .Color = RGB(0, 128, 255)
        End With
        row_num += 1

        ' Get the table's field data.
        Dim dt_field As System.Data.DataTable = _
            DBConn.GetOleDbSchemaTable( _
                OleDb.OleDbSchemaGuid.Columns, _
                New Object() {Nothing, Nothing, table_name})

        ' Headers.
        Dim field_properties() As String = _
            Split("Name,Type,Char Length,Is " & _
            "Nullable,DefaultValue,Description", ",")
        For col_num As Integer = 0 To _
            field_properties.Length - 1
            excel_worksheet.Cells(row_num, col_num + 1) = _
                field_properties(col_num)
        Next col_num
        With excel_worksheet.Range("A" & row_num, "Z" & _
            row_num).Font
            .Bold = True
        End With
        row_num += 1

        ' Display the table's field's information.
        For Each dr_field As DataRow In dt_field.Rows
            ' Name.
            excel_worksheet.Cells(row_num, 1) = _
                dr_field("COLUMN_NAME").ToString

            ' Type.
            Dim ole_db_type As OleDb.OleDbType = _
                CType(dr_field("DATA_TYPE"), _
                    OleDb.OleDbType)
            excel_worksheet.Cells(row_num, 2) = _
                ole_db_type.ToString

            excel_worksheet.Cells(row_num, 3) = _
                dr_field("CHARACTER_MAXIMUM_LENGTH").ToString
            excel_worksheet.Cells(row_num, 4) = _
                dr_field("IS_NULLABLE").ToString

            If CBool(dr_field("COLUMN_HASDEFAULT").ToString) _
                Then _
                excel_worksheet.Cells(row_num, 5) = _
                    dr_field("COLUMN_DEFAULT").ToString

            If Not dr_field.IsNull("DESCRIPTION") Then
                excel_worksheet.Cells(row_num, 6) = _
                    dr_field("DESCRIPTION").ToString
            End If

            row_num += 1
        Next dr_field

        ' Subsection header.
        excel_worksheet.Cells(row_num, 1) = "Indexes"
        With excel_worksheet.Range("A" & row_num).Font
            .Size = 16
            .Bold = True
            .Color = RGB(0, 128, 255)
        End With
        row_num += 1

        ' Get the table's index data.
        Dim dt_index As System.Data.DataTable = _
            DBConn.GetOleDbSchemaTable( _
                OleDb.OleDbSchemaGuid.Indexes, _
                New Object() {Nothing, Nothing, Nothing, _
                    Nothing, table_name})

        ' Headers.
        Dim index_properties() As String = _
            Split("Name,Primary,Unique,Column", ",")
        For col_num As Integer = 0 To _
            index_properties.Length - 1
            excel_worksheet.Cells(row_num, col_num + 1) = _
                index_properties(col_num)
        Next col_num
        With excel_worksheet.Range("A" & row_num, "Z" & _
            row_num).Font
            .Bold = True
        End With
        row_num += 1

        ' Display the table's index's information.
        For Each dr_index As DataRow In dt_index.Rows
            excel_worksheet.Cells(row_num, 1) = _
                dr_index("INDEX_NAME").ToString
            excel_worksheet.Cells(row_num, 2) = _
                dr_index("PRIMARY_KEY").ToString
            excel_worksheet.Cells(row_num, 3) = _
                dr_index("UNIQUE").ToString
            excel_worksheet.Cells(row_num, 4) = _
                dr_index("COLUMN_NAME").ToString
            row_num += 1
        Next dr_index
    Next dr_table

    ' Foreign keys.
    ' Section heading.
    excel_worksheet.Cells(row_num, 1) = "Foreign Keys"
    With excel_worksheet.Range("A" & row_num).Font
        .Size = 25
        .Bold = True
        .Color = RGB(255, 0, 0)
    End With
    row_num += 1

    ' Foreign keys.
    Dim dt_fkeys As System.Data.DataTable = _
        DBConn.GetOleDbSchemaTable( _
            OleDb.OleDbSchemaGuid.Foreign_Keys, Nothing)

    ' Headers.
    Dim fkeys_properties() As String = Split("Primary " & _
        "Column,Foreign Column,Primary Key,Foreign " & _
        "Key,Update Rule,Delete Rule", ",")
    For col_num As Integer = 0 To fkeys_properties.Length - _
        1
        excel_worksheet.Cells(row_num, col_num + 1) = _
            fkeys_properties(col_num)
    Next col_num
    With excel_worksheet.Range("A" & row_num, "Z" & _
        row_num).Font
        .Bold = True
    End With
    row_num += 1

    ' Display foreign key data.
    For Each dr_fkeys As DataRow In dt_fkeys.Rows
        excel_worksheet.Cells(row_num, 1) = _
            dr_fkeys("PK_TABLE_NAME").ToString & "." & _
            dr_fkeys("PK_COLUMN_NAME").ToString
        excel_worksheet.Cells(row_num, 2) = _
            dr_fkeys("FK_TABLE_NAME").ToString & "." & _
            dr_fkeys("FK_COLUMN_NAME").ToString
        excel_worksheet.Cells(row_num, 3) = _
            dr_fkeys("PK_NAME").ToString
        excel_worksheet.Cells(row_num, 4) = _
            dr_fkeys("FK_NAME").ToString
        excel_worksheet.Cells(row_num, 5) = _
            dr_fkeys("UPDATE_RULE").ToString
        excel_worksheet.Cells(row_num, 6) = _
            dr_fkeys("DELETE_RULE").ToString
        row_num += 1
    Next dr_fkeys

    excel_worksheet = Nothing
    excel_workbook = Nothing
    excel_app = Nothing
    DBConn.Close()

    Me.Text = "Done"
    Me.Cursor = Cursors.Default
    MessageBox.Show("Done", "Done", MessageBoxButtons.OK, _
        MessageBoxIcon.Information)
End Sub