Home
Search
 
What's New
Index
Books
Links
Q & A
Newsletter
Banners
 
Feedback
Tip Jar
 
C# Helper...
 
XML RSS Feed
Follow VBHelper on Twitter
 
 
MSDN Visual Basic Community
 
 
 
 
 
TitleUse ADO to list the tables and fields in a database
KeywordsADO, database, tables, fields
CategoriesDatabase
 
Use the Connection object's OpenSchema method to get a list of tables.
 
' List the tables in the database.
Private Sub ListTables(ByVal db_name As String)
Dim statement As String
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

    ' Open a connection.
    Set conn = New ADODB.Connection
    conn.ConnectionString = _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & db_name & ";" & _
        "Persist Security Info=False"
    conn.Open

    lstTables.Clear
    lstFields.Clear

    ' Use OpenSchema and get the table names.
    ' The final argument in the parameter array
    ' is "Table" to indicate we want a list of tables.
    Set rs = conn.OpenSchema(adSchemaTables, _
        Array(Empty, Empty, Empty, "Table"))
    Do While Not rs.EOF
        lstTables.AddItem rs!TABLE_NAME
        rs.MoveNext
    Loop

    rs.Close
    conn.Close
End Sub
 
When the user clicks on a table, use OpenSchema again to get a list of table selected table's fields.
 
' List the fields in this table.
Private Sub ListFields(ByVal db_file As String, ByVal _
    db_table_name As String)
Dim statement As String
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

    ' 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

    lstFields.Clear

    ' Use OpenSchema and get the table names.
    ' The final argument in the parameter array
    ' gives the table's name.
    Set rs = conn.OpenSchema(adSchemaColumns, _
        Array(Empty, Empty, db_table_name))

    Do While Not rs.EOF
        lstFields.AddItem rs!COLUMN_NAME
        rs.MoveNext
    Loop

    rs.Close
    conn.Close
End Sub
 
 
Copyright © 1997-2010 Rocky Mountain Computer Consulting, Inc.   All rights reserved.
  Updated