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