[RESOLVED] View Tables in a Database

[d3gerald]

After opening my connection, I want to view the tables found in my database and display them on screen. Can anybody please help me do that?

Thanx

[Bruce Fox]

If your using ADO, you can use 'Schema'. A search will yeild examples.

[d3gerald]

Yes I'm using ADO. is it the openschema function in the connection string? How do i do it?

[RhinoBull]

Not every db supports Schema so using ADOX could be a better choice.

[d3gerald]

well what i actually want to do is to view the tables found in my Access, SQL Server Database and also the worksheets in Microsoft excel

[d3gerald]

I'm going to view these tables in a treeview then if a user clicks on a table, the fields will be displayed in a listbox

[noielen]

LINK

[d3gerald]

i've seen the link, ive tried it but unfortunately, its not working

[RhinoBull]

Set references to ADO and also ADO Ext. ... and try this sample:

Private Sub Command1_Click()
'============================
Dim tbl As ADOX.Table
Dim cat As ADOX.Catalog
Dim cnn As ADODB.Connection
Dim i%
 
    Set cnn = New ADODB.Connection
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=" & App.Path & "\biblio.mdb;"
    
    Set cat = New ADOX.Catalog
    Set cat.ActiveConnection = cnn
    
    For Each tbl In cat.Tables
        Combo1.AddItem que.Name
    Next tbl
    
    cnn.Close
    
    Set cat = Nothing
    Set cnn = Nothing
 
End Sub

[d3gerald]

thankx Rhino. First, it didn't work due to the que.Name

what i did is i just changed que to tbl which is defined as a table in adox. only one thing, I have one excel worksheet in my excel file called was2. when i run that code, it returnce two values, was2$(which is the expected output) and was2$_. is this a normal scenario in adox or theres is something missing in the code.

my first approach for this is to filter the returned values if the last character is an (_). if it is, i wont display it but if not, i will.

is there any other better option for this?

[d3gerald]

well i just filter the tablenames by looking for the $ sign at the end of the value returned. if the last character of the value is $ then it is one of the worksheets in excel and it will display it. ir not it wont, thanks Rhino for the help

[RhinoBull]

thankx Rhino. First, it didn't work due to the que.Name...

That's because what I posted is shrinked version of much more complex sample... Glad you figured it out on your own.

[d3gerald]

am i on the right track on my solution above, or theres another way of doing it?