Using VB6 to view MS Access Database Tables

I Would like some ideas on how to view Database tables (MS Access) from VB6, not the data inside the tables because this is pretty simple. I want to open a mdb file and look at the tables it contains.

Please help
LVL 1
mbejohnAsked:
Who is Participating?
 
CWS (haripriya)Connect With a Mentor Commented:
       
Dim rsh As New ADODB.Recordset
Dim conn as New ADODB.Connection
CONN.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\test.mdb;Persist Security Info=False"
   
    Set rsh = CONN.OpenSchema(adSchemaTables)
    Do Until rsh.EOF
    MsgBox "Table Name : " & rsh("table_name") & Vbcrlf & "Table Type : " & rsh("Table_type") & vbcrlf

    rsh.MoveNext
    Loop
0
 
hiteshgupta1Connect With a Mentor Commented:
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

Reference :
http://www.vb-helper.com
0
 
hiteshgupta1Commented:
This will further give the field level details
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
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
dbrayleyConnect With a Mentor Commented:
Here's an alternative. You can use the ADOX api, which provides an easy to use object model. It's root object is a Catalog, which corresponds to a database. A Catalog has a collections called Tables, Views, Procedures, Users, and Groups. The Tables collection contains a set of Table objects, the Views collection contains a set of View objects, and so on. A Table object contains collections of Columns, Keys, and Indexes. Each of these collections contains objects of the corresponding singular name (Column, Key, Index). The full object model description can be found in the MSDN library documentation that comes with VB6. See "Platform SDK Documentation/Data Services/ADO Programmer's Reference/ADOX API Information".

Here is an example of how to use the ADOX API:


' Sample Code to Use the ADOX API
'
' This fragment can be pasted into the main form of an empty application.
' The form should contain a text control called "txtMessages"; it should
' be multiline, and have vertical and horizontal scrollbars.
'
' The project requires references to
' "Microsoft Active X Data Objects 2.8 Library"
' "Microsoft ADO Ext. 2.5 for DDL and Security"
'
Option Explicit

Private Sub Form_Load()
    Const l_DatabaseFileName As String = "D:\Brayley\Current\Client\Alphora Research\Alphora1\Alphora1.mdb"
    Dim l_ConnectionString As String
    Dim l_Connection As ADODB.connection
    Dim l_Catalog As ADOX.Catalog
    Dim l_Table As ADOX.Table
    Dim l_Column As ADOX.Column
    Dim l_Key As ADOX.Key
    '
    ' Construct the connection string, and open a connection to the Access Database
    '
    l_ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
    l_ConnectionString = l_ConnectionString + l_DatabaseFileName
    l_ConnectionString = l_ConnectionString + ";Persist Security Info=False"
    Set l_Connection = New ADODB.connection
    l_Connection.Open l_ConnectionString
    '
    ' Open the Catalog
    '
    Set l_Catalog = New ADOX.Catalog
    Set l_Catalog.ActiveConnection = l_Connection
    '
    ' Write a greeting message to the text control
    '
    txtMessages.Text = "List of Tables in Database " + l_DatabaseFileName
    OutputMessage vbCrLf + vbCrLf
   
    For Each l_Table In l_Catalog.Tables
        OutputMessage "Table : " + DelimitedName(l_Table.Name) + vbCrLf
        '
        ' Output Columns of Table
        '
        OutputMessage "Columns" + vbCrLf
        For Each l_Column In l_Table.Columns
            OutputMessage vbTab + DelimitedName(l_Column.Name)
            OutputMessage " : " + DataTypeName(l_Column.Type)
            OutputMessage vbCrLf
        Next l_Column
        '
        ' Output Keys of Table
        '
        OutputMessage "Keys" + vbCrLf
        For Each l_Key In l_Table.Keys
            OutputMessage vbTab + DelimitedName(l_Key.Name)
            OutputMessage " : " + KeyTypeName(l_Key.Type)
            Select Case l_Key.Type
                Case adKeyPrimary
                    '
                    ' List the columns of the Primary Key
                    '
                    Dim l_keyColumn As ADOX.Column
                   
                    OutputMessage " on column(s) "
                    For Each l_keyColumn In l_Key.Columns
                        OutputMessage DelimitedName(l_keyColumn.Name) + " "
                    Next l_keyColumn
                   
                Case adKeyForeign
                    '
                    ' show the name of the foreign table
                    '
                    OutputMessage " to table "
                    OutputMessage DelimitedName(l_Key.RelatedTable)
                   
                Case adKeyUnique
                   
                Case Else
                   
            End Select
            OutputMessage vbCrLf
        Next l_Key
       
       
        OutputMessage vbCrLf
    Next l_Table
   
    Set l_Catalog = Nothing
   
    l_Connection.Close
    Set l_Connection = Nothing
   
End Sub

' DelimitedName encloses a table or column name in square brackets,
' as is done by Access
'
Private Function DelimitedName(UndelimitedName As String) As String
    DelimitedName = "[" + UndelimitedName + "]"
End Function

' DataTypeName returns a string for a DataTypeID value
'
Private Function DataTypeName(TypeID As Integer) As String
    Select Case TypeID
        Case adEmpty
            DataTypeName = "Empty"
        Case adSmallInt
            DataTypeName = "SmallInt"
        Case adInteger
            DataTypeName = "Integer"
        Case adSingle
            DataTypeName = "Single"
        Case adDouble
            DataTypeName = "Double"
        Case adCurrency
            DataTypeName = "Currency"
        Case adDate
            DataTypeName = "Date"
        Case adBSTR
            DataTypeName = "BSTR"
        Case adIDispatch
            DataTypeName = "IDispatch"
        Case adError
            DataTypeName = "Error"
        Case adBoolean
            DataTypeName = "Boolean"
        Case adVariant
            DataTypeName = "Variant"
        Case adIUnknown
            DataTypeName = "IUnknown"
        Case adDecimal
            DataTypeName = "Decimal"
        Case adTinyInt
            DataTypeName = "TinyInt"
        Case adUnsignedTinyInt
            DataTypeName = "UnsignedTinyInt"
        Case adUnsignedSmallInt
            DataTypeName = "UnsignedSmallInt"
        Case adUnsignedInt
            DataTypeName = "UnsignedInt"
        Case adBigInt
            DataTypeName = "BigInt"
        Case adUnsignedBigInt
            DataTypeName = "UnsignedBigInt"
        Case adFileTime
            DataTypeName = "FileTime"
        Case adGUID
            DataTypeName = "GUID"
        Case adBinary
            DataTypeName = "Binary"
        Case adChar
            DataTypeName = "Char"
        Case adWChar
            DataTypeName = "WChar"
        Case adNumeric
            DataTypeName = "Numeric"
        Case adUserDefined
            DataTypeName = "UserDefined"
        Case adDBDate
            DataTypeName = "DBDate"
        Case adDBTime
            DataTypeName = "DBTime"
        Case adDBTimeStamp
            DataTypeName = "DBTimeStamp"
        Case adChapter
            DataTypeName = "Chapter"
        Case adPropVariant
            DataTypeName = "PropVariant"
        Case adVarNumeric
            DataTypeName = "VarNumeric"
        Case adVarChar
            DataTypeName = "VarChar"
        Case adLongVarChar
            DataTypeName = "LongVarChar"
        Case adVarWChar
            DataTypeName = "VarWChar"
        Case adLongVarWChar
            DataTypeName = "LongVarWChar"
        Case adVarBinary
            DataTypeName = "VarBinary"
        Case adLongVarBinary
            DataTypeName = "LongVarBinary"
        Case Else
            DataTypeName = "Unknown Data Type " + CStr(TypeID)
    End Select
End Function

' KeyTypeName returns a string for a KeyTypeID value
'
Private Function KeyTypeName(TypeID As Integer) As String
    Select Case TypeID
        Case adKeyPrimary
            KeyTypeName = "Primary Key"
        Case adKeyForeign
            KeyTypeName = "Foreign Key"
        Case adKeyUnique
            KeyTypeName = "Unique Constraint"
        Case Else
            KeyTypeName = "Unknown Key Type " + CStr(TypeID)
    End Select
End Function

' OutputMessage appends a message to the text box (txtMessages)
'
Private Sub OutputMessage(Message As String)
    txtMessages.Text = txtMessages.Text + Message
End Sub
0
 
dbrayleyCommented:
A little ammendment to my last comment:

The line:

  Const l_DatabaseFileName As String = "D:\Brayley\Current\Client\Alphora Research\Alphora1\Alphora1.mdb"

refers of course to a database file on my workstation. You'll want to change that name to the database you want to access...
0
 
hiteshgupta1Commented:
Hi Venabili

No objection from my side

Thanks
Hitesh
0
 
dbrayleyCommented:
Hi Venabili,

I'm OK with this too. It's disappointing when you put a lot of effort into answering a question, then get no response at all from the inquirer - I'm sure that Hitesh - who did the same - feels the same way.

dbrayley
0
 
CWS (haripriya)Commented:
I too agree with Points Split.
0
All Courses

From novice to tech pro — start learning today.