• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 167
  • Last Modified:

ADO Problem

DAO allows you to get to the tables in a database using the TableDef object.
How do you do this in ADO?
For instance I just want to get a list of table names in a database.
0
ianch
Asked:
ianch
1 Solution
 
CJ_SCommented:
Which database are you using?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
To get the list of table names:
dim c as ADODB.Connection
dim r as ADODB.Recordset
set c = new ADODB.Connection
c.open ...
set r = c.OpenSchema(adSchemaTables)

in r you will see all the tables ...

CHeers
0
 
chops123Commented:
If you are using SQL Server,you can get the tables in the database using:

User Tables:
select * from databaseName..sysobjects where xtype = 'U'

System Tables:
select * from databasename..sysobjects where xtype = 'S'

Rgds
KC
0
 
iboutchkineCommented:
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 Debug.Print tbl.Name
'not to see system tables        If Left$(tbl.Name, 4) <> "MSys" Then Debug.Print tbl.Name

 Next

End Sub
0
 
ianchAuthor Commented:
Thanks All
Angel's answer & iboutchkine' answer worked out about the same.
However Angel's has less overhead and he was first in.

Thanks again
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now