Getting Table Info

[Rick B]

Can you actually query a *.mdb database and retrieve the table names when the database and/or recordsource(Table) has not been set.?

[SteveFlitIII]

Use the tabledefs collection to loop through the tables in a database

[Rick B]

Thanks, but I'm aware of that. But it can only be done as far as I know when the db or is current. I want to be able to look in a mdb file (Query it if you like) and abstract information from that *.mdb file even though I might not want to make that particulat db the current one....if you understand what i mean.

Anyone?

[Clunietp]

Are you trying to do this from MS Access VBA or thru VB? The CurrentDB property is only available thru VBA code, not VB.

In either case, if you want to get the table structure of a database, you will have to declare the objects, open the DB and enumerate thru the TableDefs collection as Steve said

[Rick B]

Yeah, I've finally come to the conclusion that ADO sux. Everytime I come across a potential problem with the pesky control, I find I can resolve it by using DAO!!!

Thanks for your help ppl, Clunietp I have done exactly what you mentioned and hey, it works, but not with ADO and the Data Control....

[Clunietp]

ADO is the way of the future, so you can't resist forever...

    'references:
    'ActiveX Data Objects 2.1
    'ADO 2.1 for DDL and security
    
    Dim tbl As ADOX.Table
    Dim cn As ADODB.Connection
    
    Dim ax As ADOX.Catalog
    
    Set cn = New ADODB.Connection
    
    'connect
    cn.Open "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=Nwind.mdb"
    
    Set ax = New ADOX.Catalog
    
    ax.ActiveConnection = cn
    
    'loop thru tables
    For Each tbl In ax.Tables
        Debug.Print tbl.Name & " " & tbl.Type
    Next tbl
    
    'close connection
    cn.Close
    Set cn = Nothing