How to fetch table names

Printable View

Oct 11th, 2001, 06:24 AM
aspguruindia

How to fetch table names

Hello everyone,

I want to fetch names of the tables in a specified .mdb file.
How can I do that.

Thanks in advance for helping.

Using ADO

VB Code:

Option Explicit
Private cat As ADOX.Catalog
Private tbl As ADOX.Table
Private cnn As New ADODB.Connection
 
Private Sub Command1_Click()
    Set cat = New ADOX.Catalog
    Set tbl = New ADOX.Table
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\BIBLIO.MDB"
    Set cat.ActiveConnection = cnn
 
    For Each tbl In cat.Tables
        Debug.Print tbl.Name
    Next tbl
End Sub

Oct 11th, 2001, 06:28 AM
chrisjk

http://www.vbforums.com/showthread.p...ht=table+names

Using DAO

VB Code:

Private Sub Command1_Click()
    Dim db As Database
    Dim tDef As TableDef
    Dim i As Integer
    Set db = OpenDatabase("C:\test.mdb")
    For Each tDef In db.TableDefs
        Debug.Print "Table : " & vbTab & tDef.Name
        For i = 0 To tDef.Fields.Count - 1
            Debug.Print "Field : " & vbTab & vbTab & tDef.Fields(i).Name
        Next i
    Next tDef
End Sub

Oct 11th, 2001, 06:33 AM
Hack
peets answer will get you all of the tables, including the hidden system tables. These are tables that you probably don't want to mess with.

I have one app in which I read in all of the tables in any given .Mdb and display then in a checked list box. But, before the LoadTables sub finished, I remove the hidden system tables from the listbox display, just to be nice and safe. Fortunately, the system tables conveniently start with the same 4 letters, so removing them is very easy.
VB Code:
```
For i = lstDbTables.ListCount - 1 To 0 Step -1
        If lstDbTables.List(i) Like "MSys*" Then
            lstDbTables.RemoveItem i
        End If
    Next
```
lstDbTables is my list control, and i is Dimmed As Long (did you ever wonder why i is not a VB reservered word permently dimension as a Long? IT SHOULD BE!
May 20th, 2002, 12:47 PM

Looks good. But I'm looking for the way to put field names from a table into a list box. I'm using VB 6 with unbound data controls and a dataenvironment. I'm groping in the dark here, being thrown into the VB deep end.