Solved

Using VB6 to view MS Access Database Tables

Posted on 2006-07-13
10
352 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:mbejohn
  • 3
  • 3
  • 2
10 Comments
 
LVL 16

Accepted Solution

by:
CWS (haripriya) earned 168 total points
ID: 17106288
       
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
 
LVL 8

Assisted Solution

by:hiteshgupta1
hiteshgupta1 earned 166 total points
ID: 17106538
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
 
LVL 8

Expert Comment

by:hiteshgupta1
ID: 17106543
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
 

Assisted Solution

by:dbrayley
dbrayley earned 166 total points
ID: 17115610
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Expert Comment

by:dbrayley
ID: 17115620
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
 
LVL 8

Expert Comment

by:hiteshgupta1
ID: 17268753
Hi Venabili

No objection from my side

Thanks
Hitesh
0
 

Expert Comment

by:dbrayley
ID: 17270384
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
 
LVL 16

Expert Comment

by:CWS (haripriya)
ID: 17270488
I too agree with Points Split.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Adobe Customization Wizard XI issues 26 169
matchUp  challenge 9 72
array11 challenge 16 52
advertisement module in core php 4 94
Purpose To explain how to place a textual stamp on a PDF document.  This is commonly referred to as an annotation, or possibly a watermark, but a watermark is generally different in that it is somewhat translucent.  Watermark’s may be text or graph…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now