Solved

ADO Problem

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
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…
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…

776 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