Tables Count

[danprinz]

hi,
Is there any Tables Collection in ADO?
i need to count the tables in my database and listing them, so my users can edit any table without me to write special procedure to do that.
10x.

Daniel Prinz

[awylie]

Yep,

There are a couple of ways to do this.

1. Include a reference to MSADOX.dll (Microsoft ADO Ext) and use the Catalog class
2. In ADO Connection class, there is a function OpenSchema where you can specify the schema type to open and this is returned in an ADO recordset. Check out the help on this for the column names that store the table details.

Thanx
Andrew

[Iouri]

Sub ListTables()

Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table

'References : Microsoft ADO Ext 2.1 or DDL and Security

' Open the catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\nwind.mdb;"

' Loop through the tables in the database and print their name
For Each tbl In cat.Tables
If tbl.Type <> "VIEW" Then List1.AddItem tbl.Name
'not to see system tables If Left$(tbl.Name, 4) <> "MSys" Then List1.AddItem tbl.Name

Next

End Sub

Iouri Boutchkine
iouri@hotsheet.com