How to List the Fields in a Table & the Tables in a Database

ID: Q109219


The information in this article applies to:
  • Microsoft Visual Basic Professional Edition for Windows, version 3.0


SUMMARY

Below are two examples showing how to list all the fields in a table and all the tables in a database.

  • Example One shows how to list all the fields in a database table by using the Fields collection of a table's TableDef object. It also shows how to list the names of all tables in a database.


  • Example Two shows how to list all the fields in a database table using the ListFields method. The ListFields method creates a Snapshot with one record for each field in a specified recordset.


The technique used in Example One is more efficient than Example Two.


MORE INFORMATION

Example One: How to List the Fields in a Table Using the Fields Collection

  1. Start a new project in Visual Basic. Form1 is created by default.


  2. Add two list boxes to Form1.


  3. Double-click the form to open the code window. Add the following code to the Form Load event:
    
       Sub Form_Load ()
    
          Set MyDb = OpenDatabase("BIBLIO.MDB")
          ' Set AllTableDefs to definitions of all tables in the database:
          Set AllTableDefs = MyDb.TableDefs
          ' Display names of all tables in database:
          For j = 0 To AllTableDefs.Count - 1
             List1.AddItem AllTableDefs(j).Name
          Next
    
       End Sub 


  4. Double-click the List1 list box and enter the following code in its Click event:
    
       Sub List1_Click ()
    
          ' Delete any existing entries in List2 box:
           Do While list2.ListCount > 0
              list2.RemoveItem 0
           Loop
    
          ' Get the definition of the single table currently selected in List1:
          Set SingleTableDef = MyDb(List1.List(List1.ListIndex))
          ' Display the properties of each field in the table:
          For j = 0 To SingleTableDef.Fields.Count - 1
             list2.AddItem "Field item number " & Val(j) & ":"
    
             ' Display the name of the field in the table selected in List1:
             list2.AddItem SingleTableDef.Fields(j).Name
             ' or use the following since Fields are the default collection:
             ' List2.AddItem SingleTableDef(j).Name
    
             list2.AddItem SingleTableDef.Fields(j).Size  ' Size of field.
             list2.AddItem SingleTableDef.Fields(j).Type  ' Type of field.
             ' If field is an index, list the name of the index:
             If j <= SingleTableDef.Indexes.Count - 1 Then
                list2.AddItem "Index name: " & SingleTableDef.Indexes(j).Name
             End If
    
             ' The Value property is only valid if part of a recordset:
             ' list2.AddItem SingleTableDef.Fields(i).Value
    
             ' The other 5 properties are valid for a field of TableDef object:
             list2.AddItem SingleTableDef.Fields(j).OrdinalPosition
             list2.AddItem SingleTableDef.Fields(j).CollatingOrder
             list2.AddItem SingleTableDef.Fields(j).Attributes
             list2.AddItem SingleTableDef.Fields(j).SourceField
             list2.AddItem SingleTableDef.Fields(j).SourceTable
             list2.AddItem " "
          Next
    
       End Sub 


  5. From the File menu, choose New Module. Then enter the following code in the General Declarations section:
    
       Global MyDb As Database
       Global SingleTableDef As TableDef
       Global AllTableDefs As TableDefs 


  6. Start the program. Click any table name in the first list box. In the second list box, the program displays all the fields and field properties for that table. Close the form to end the program. NOTE: Some MSys* tables (such as MSysACEs) have no fields.


Example Two: How to List the Fields in a Table Using the ListFields Method

  1. Start a new project in Visual Basic. Form1 is created by default.


  2. Add a list box to Form1. Size the list box to fill all of Form1.


  3. Double-click the form to open the code window. Add the following code to the Form Load event:
    
       Sub Form_Load ()
       Dim ListSet As Snapshot, MyDB As database, MyTable As table
       Set MyDB = OpenDatabase("BIBLIO.MDB")
       Set MyTable = MyDB.OpenTable("Publishers")  ' Open Table.
       Set ListSet = MyTable.ListFields()  ' Put field info in ListSet.
       MyTable.Close           ' Close Table.
       Do While Not ListSet.EOF
          list1.AddItem "Name: " & ListSet("Name")
          list1.AddItem "type: " & ListSet("Type")
          list1.AddItem "size: " & ListSet("Size")
          list1.AddItem "Attributes: " & ListSet("Attributes")
          list1.AddItem "SourceTable: " & ListSet("SourceTable")
          list1.AddItem "SourceField: " & ListSet("SourceField")
          list1.AddItem " "
          ListSet.MoveNext
       Loop
       End Sub 
    The above program uses the BIBLIO.MDB database that ships with Visual Basic version 3.0


  4. Start the program (or press the F5 key). Close the form to end the program.


The above program lists the following field structure for the Publishers table in the BIBLIO.MDB database:

Name: PubID
type: 4
size: 4
Attributes: 33
SourceTable: Publishers
SourceField: PubID

Name: Name
type: 10
size: 50
Attributes: 32
SourceTable: Publishers
SourceField: Name

Name: Company Name
type: 10
size: 255
Attributes: 32
SourceTable: Publishers
SourceField: Company Name

Name: Address
type: 10
size: 50
Attributes: 32
SourceTable: Publishers
SourceField: Address

Name: City
type: 10
size: 20
Attributes: 32
SourceTable: Publishers
SourceField: City

Name: State
type: 10
size: 10
Attributes: 32
SourceTable: Publishers
SourceField: State

Name: Zip
type: 10
size: 15
Attributes: 32
SourceTable: Publishers
SourceField: Zip

Name: Telephone
type: 10
size: 15
Attributes: 32
SourceTable: Publishers
SourceField: Telephone

Name: Fax
type: 10
size: 15
Attributes: 32
SourceTable: Publishers
SourceField: Fax 

Additional query words: 3.00

Keywords :
Version :
Platform :
Issue type :


Last Reviewed: August 31, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.