ADO - Table Collection

I'm trying to use ADO instead of DAO for accessing an MS Access Database but I can't find anything remotely similar to the DAO.TableDefs collection.

Basically, what I want to do is list all the tables within the aforementioned database in a listbox.

Any ideas? :confused:

Heres a good example:


http://www.freevbcode.com/ShowCode.asp?ID=185

Heres a short example.

VB Code:

'Add a reference to Microsoft ADO Ext. 2.x
 
Dim objConn As New ADODB.Connection
Dim objAdox As New ADOX.Catalog
Dim TB As ADOX.Table
objConn.Open strConnectionString 
objAdox.ActiveConnection = objConn
 
List1.Clear
For Each TB In objAdox.Tables
    List1.AddItem TB.Name
Next
 
objConn.Close
Set objConn = Nothing

:)

Thank you both. That's exactly what I need. :)

Welcome:)

Further to the Tables collection, I'd now like to get the Fields collection.

I've found something through ADOX called the Columns collection which I'm guessing is the equivalent. I can't, however, seem to get a list of a table's fields into a listbox.

Any further solutions?

VB Code:

Dim objConn As New ADODB.Connection
Dim objAdox As New ADOX.Catalog
Dim TB As ADOX.Table
Dim Col As ADOX.Column
objConn.Open ConnectionString
objAdox.ActiveConnection = objConn
 
List1.Clear
Set TB = objAdox.Tables("TableName")
For Each Col In TB.Columns
    List1.AddItem Col.Name 
Next
objConn.Close
Set objConn = Nothing

:)

How about listing the Access Query names and the fields that are used in each Query using ADOX ???

VB Code:

Dim P As ADOX.Procedure
For Each P In objAdox.Procedures
    List1.AddItem P.Name
Next

Just fingure around with this, maybe u might even get the fields involved:)

Here's a little more comprehensive example I made

http://www.1337geek.com/code/vb6/database/ADODB/

This is all good - I can now navigate through all the tables and put all their fields into a listbox but I can't seem to dimension an ADOX.Table and then set it to the name of the table chosen in the listbox

Whenever I try (snippet):

VB Code:

Dim tbl As ADOX.Table
Set tbl = Catalog.Tables(MyListBox)

I get an error regarding not being able to find the item within the collection based on the ordinal.

What are u trying?

trying to get a reference of the selected table?