?
Solved

ADO Problem

Posted on 2002-06-03
5
Medium Priority
?
161 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
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…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…
Suggested Courses
Course of the Month13 days, 12 hours left to enroll

800 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