ianch
asked on
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.
How do you do this in ADO?
For instance I just want to get a list of table names in a database.
CJ_S
Which database are you using?
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
User Tables:
select * from databaseName..sysobjects where xtype = 'U'
System Tables:
select * from databasename..sysobjects where xtype = 'S'
Rgds
KC
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.OL
"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
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.OL
"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
ASKER
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
Angel's answer & iboutchkine' answer worked out about the same.
However Angel's has less overhead and he was first in.
Thanks again