Improve company productivity with a Business Account.Sign Up

x
?
Solved

ADO Problem

Posted on 2002-06-03
5
Medium Priority
?
169 Views
Last Modified: 2013-11-23
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
Comment
Question by:ianch
5 Comments
 
LVL 22

Expert Comment

by:CJ_S
ID: 7050726
Which database are you using?
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 400 total points
ID: 7050739
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
 
LVL 2

Expert Comment

by:chops123
ID: 7050777
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
 
LVL 28

Expert Comment

by:iboutchkine
ID: 7051165
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
 

Author Comment

by:ianch
ID: 7052776
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

584 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question