XTreme Logo

If you appreciate these tips, please consider a small donation:

General Tip - Intermediate

Generating Output of Database Structure
Applies to: VB4 16 and 32-bit and VB5
Courtesy of Renee LeBlanc

The code shown below will loop through all the tables and queries in a database, displaying them with the fields contained in each.

novdbfrm.jpg (31526 bytes)
Remember to add a project reference to the appropriate DAO library before you start!  Also be sure to set the multiline property of Text1 to true so everything will display correctly.

Private Sub Command1_Click()
    Dim objQueryDef As QueryDef
    Dim objTableDef As TableDef
    Dim objField As Field
    Dim db As Database
    'If the following line fails, test hardcoding the path
db = OpenDatabase("biblio.mdb")
    'Northwind is another good test database for this code
    Text1.Text = "Structure for " & db.Name & " Database." & vbCrLf
    Text1.Text = Text1.Text & "Table Definitions" & vbCrLf
    'Loop through the tables
Each objTableDef In db.TableDefs
        If objTableDef.Attributes And dbSystemObject Then
        'Let's not list the system tables, so do nothing here

            Text1.Text = Text1.Text & vbCrLf & objTableDef.Name
'Loop through the fields in each TableDef and list them
             For Each objField In objTableDef.Fields
                 Text1.Text = Text1.Text & vbCrLf & vbTab & objField.Name
        End If
    Text1.Text = Text1.Text & vbCrLf & vbCrLf & "Query Definitions." & vbCrLf
    'Now loop through the queries and list those too
Each objQueryDef In db.QueryDefs
        Text1.Text = Text1.Text & vbCrLf & objQueryDef.Name
'Loop through the fields in each QueryDef and list them
        For Each objField In objQueryDef.Fields
            Text1.Text = Text1.Text & vbCrLf & vbTab & objField.Name
End Sub

[ Back To The Top ]

Contact: web@xtremecomp.com
All contents copyright XTreme Computing unless noted otherwise.