Solved

ADO Problem

Posted on 2002-06-03
5
155 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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 100 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now