Untitled Page

Sometimes we need to see what Tables and their Column definitions are in an Access (.mdb) Database. This demonstration program will allow you to select any Access .mdb Database file on your computer and will show a list of Table and Column definitions.

A Visual Studio 2010 demonstration project for Schema Info is available for your use.

Two Structures were created to define the Tables and Columns. There is one sSchemaTable structure element for each table in the database, and one sSchemaColumn structure for each Column in the table.

Structure sSchemaTable

Element Type Description

TableName String Name of Table

scs() sSchemaColumn Structure Array - One element for each Column in Table
Structure sSchemaColumn

Element Type Description

ColumnName String Name of Column

ColumnSize Integer Size of Column

ColumnType String Data Type of Column, ie; Integer, string, etc.

Element	Type	Description
TableName	String	Name of Table
scs()	sSchemaColumn	Structure Array - One element for each Column in Table

Element	Type	Description
ColumnName	String	Name of Column
ColumnSize	Integer	Size of Column
ColumnType	String	Data Type of Column, ie; Integer, string, etc.

Program Properties and Routines:

Property/Routine	Parameters	Returns	Description
TableInfo	_TableName	sSchemaTable	Information for a Table
ColumnInfo	_Column _TableName	sSchemaColumn	Information for a Table Column
ColumnLength	_Column _TableName	Integer	Maximum Length of a Column
GetSchema	_TableNames _connDb		Get the Column Definitions for all Tables listed in the _TableNames string array using the _connDB database connection.

The follow is a demonstration program to list an Access Database's Tables and Columns.

Create a new Windows Form Application Project with Visual Studio.
Create a ListView on the default Form1.
Use the follow code for Form1.
Execute the program and select the Access Database file you what to list.

Option Explicit On
Option Strict On

'  William Dowling       BDowling@TreelessSoftware.com
'  Treeless Software     www.TreelessSoftware.com

Imports System.IO
Imports System.Data.OleDb

Public Class Form1

' Information Columns available with GetSchemaTable using CommandBehavior.KeyInfo
    '   ColumnName      ColumnOrdinal       ColumnSize      NumericPrecision
    '   NumericScale    DataType            ProviderType    IsLong
    '   AllowDBNull     IsReadOnly          IsRowVersion    IsUnique
    '   IsKey           IsAutoIncrement     BaseSchemaName  BaseCatalogName
    '   BaseTableName   BaseColumnName

Private m_sts() As sSchemaTable = {}

''' <summary>
    ''' Schema Table Definition Structure
    ''' </summary>
    ''' <remarks></remarks>
    Public Structure sSchemaTable
        Public TableName As String
        Public scs() As sSchemaColumn
    End Structure

''' <summary>
    ''' Schema Column Definition for Table Structure
    ''' </summary>
    ''' <remarks></remarks>
    Public Structure sSchemaColumn
        Public ColumnName As String
        Public ColumnSize As Integer
        Public ColumnType As String
    End Structure

''' <summary>
    ''' Get the Information for a Table
    ''' </summary>
    ''' <param name="_TableName">Table Name to Find</param>
    ''' <value>Information for a Table</value>
    ''' <returns>Information for a Table</returns>
    ''' <remarks></remarks>
    Public ReadOnly Property TableInfo(ByVal _TableName As String) As sSchemaTable
        Get
            For Each st As sSchemaTable In m_sts
                If st.TableName = _TableName.Trim Then Return st
            Next
            Throw New Exception("Invalid TableName for TableInfo: " + _TableName)
        End Get
    End Property

''' <summary>
    ''' Get the Information for a Table Column
    ''' </summary>
    ''' <param name="_Column">Column Name to Find</param>
    ''' <param name="_TableName">Table Name to Find</param>
    ''' <value>Information for a Table Column</value>
    ''' <returns>Information for a Table Column</returns>
    ''' <remarks></remarks>
    Public ReadOnly Property ColumnInfo(ByVal _Column As String, _
                                        ByVal _TableName As String) As sSchemaColumn
        Get
            Dim st As sSchemaTable = Me.TableInfo(_TableName)
            For Each sc As sSchemaColumn In st.scs
                If sc.ColumnName = _Column.Trim Then Return sc
            Next
            Throw New Exception("Invalid Column for ColumnInfo: " + _Column)
        End Get
    End Property

''' <summary>
    ''' Get the Maximum Length of a Table Column
    ''' </summary>
    ''' <param name="_Column">Column Name to Find</param>
    ''' <param name="_TableName">Table Name to Find</param>
    ''' <value>Maximum Length of a Column</value>
    ''' <returns>Maximum Length of a Column</returns>
    ''' <remarks></remarks>
    Public ReadOnly Property ColumnLength(ByVal _Column As String, _
                                          ByVal _TableName As String) As Integer
        Get
            Return Me.ColumnInfo(_Column, _TableName).ColumnSize
        End Get
    End Property

''' <summary>
    ''' Get the Field Definitions for all listed Tables
    ''' </summary>
    ''' <param name="_TableNames">Array of Table Names to get Field Definitions</param>
    ''' <param name="_connDb">Database Connection</param>
    ''' <remarks></remarks>
    Private Sub GetSchema(_TableNames() As String, _connDb As OleDbConnection)
        ReDim m_sts(_TableNames.GetUpperBound(0))
        Dim cmd As New OleDbCommand
        cmd.Connection = _connDb
        '*******************
        ' Process each table
        '*******************
        Dim nTableIndex As Integer = 0
        For Each sTableName As String In _TableNames
            m_sts(nTableIndex).TableName = sTableName
            cmd.CommandText = "SELECT * FROM [" + sTableName + "];"
            '*********************************************************
            ' Get the column and primary key information for our table
            '*********************************************************
            Dim rdr As OleDbDataReader = cmd.ExecuteReader(CommandBehavior.KeyInfo)
            Dim dt As DataTable = rdr.GetSchemaTable
            rdr.Close()                                 ' Close up the Data Reader
            '**************************************************************
            ' Each DataRow is a column in our table, save what info we need
            '**************************************************************
            Dim nRowIndex As Integer = 0
            ReDim m_sts(nTableIndex).scs(dt.Rows.Count - 1)
            For Each dr As DataRow In dt.Rows
                m_sts(nTableIndex).scs(nRowIndex).ColumnName = dr.Item("ColumnName").ToString
                m_sts(nTableIndex).scs(nRowIndex).ColumnSize = CType(dr.Item("ColumnSize"), Integer)
                m_sts(nTableIndex).scs(nRowIndex).ColumnType = dr.Item("DataType").ToString
                nRowIndex += 1
            Next
            nTableIndex += 1
        Next
    End Sub

''' <summary>
    ''' Main routine for when Form loads
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Try
            '*************************
            ' Get the database to show
            '*************************
            Dim ofd As New OpenFileDialog
            ofd.Filter = "DB Files (*.mdb)|*.mdb|All Files (*.*)|*.*"
            ofd.FilterIndex = 1
            If ofd.ShowDialog(Me) <> Windows.Forms.DialogResult.OK Then Exit Sub
            '******************
            ' Open the database
            '******************
            Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" + _
                "Data Source=" + ofd.FileName + ";" + _
                "Persist Security Info=False;"
            Dim connDB As New OleDbConnection(sConnectionString)
            connDB.Open()                       ' Open the Access Database
            '***********************
            ' Get the list of tables
            '***********************
            Dim sRestrictions() As String = New String() {Nothing, Nothing, Nothing, "Table"}
            Dim dtTables As DataTable = connDB.GetSchema("Tables", sRestrictions)
            Dim sTables(dtTables.Rows.Count - 1) As String
            For nTable As Integer = 0 To dtTables.Rows.Count - 1
                sTables(nTable) = dtTables.Rows(nTable).Item("TABLE_NAME").ToString
            Next
            '************************
            ' Get the list of Columns
            '************************
            Me.GetSchema(sTables, connDB)
            connDB.Close()                      ' All done with database
            '************************************
            ' Show the list of Tables and Columns
            '************************************
            Me.ListView1.View = View.Details
            Me.ListView1.GridLines = True
            Me.ListView1.Columns.Add("Table", 100)
            Me.ListView1.Columns.Add("Column", 100)
            Me.ListView1.Columns.Add("Size", 100, HorizontalAlignment.Right)
            Me.ListView1.Columns.Add("Type", 100)
            For Each st As sSchemaTable In m_sts
                For Each sc As sSchemaColumn In st.scs
                    Dim lvi As ListViewItem = Me.ListView1.Items.Add(st.TableName)
                    lvi.SubItems.Add(sc.ColumnName)
                    lvi.SubItems.Add(sc.ColumnSize.ToString)
                    lvi.SubItems.Add(sc.ColumnType)
                Next
            Next
        Catch ex As Exception
            Throw New Exception(ex.Message)
        End Try
    End Sub

End Class

Treeless Software

PC and Web Software Development.

Latest News

New VB.Net and SQL Examples

New Website Launched

July 7, 2011

Useful Links

Search