• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 198
  • Last Modified:

Checking out a database's structure

Hello
I need to be able to check out the table structure of a database.  What I need to do is be able to provide people with a list of all tables that belong to a database.  I know how I can get each field that belongs to the table, but I don't know how to get each table that belongs to the database.

Also I need to do this with ADO 2.0
0
cavacasp
Asked:
cavacasp
  • 2
  • 2
1 Solution
 
wizard2072098Commented:
You can retrieve the structure of a database by using the OpenSchema method of an open ADO connection object. By setting the "QueryType" parameter, you can get information about tables, procs, columns, etc.

Example:

Set rs = conn.OpenSchema(adSchemaTables)      
Do Until rs.EOF
      Debug.Print rs!TABLE_NAME & rs!TABLE_TYPE  (etc, etc...)
      rs.MoveNext
Loop  
rs.Close


0
 
cavacaspAuthor Commented:
Excatly what I was looking for thanks

0
 
cavacaspAuthor Commented:
Actually I found a problem with the way I was going to get the field names.  It works but it could take a very long time to finish.

Could you please provide me with an example of how to get the field names of a specific table.  This is what I currently have

  Set rs = cn.OpenSchema(adSchemaColumns, "TABLE_NAME='" & cboValidateTable_3.Text & "'")
   
  cboValidateColumn_3.AddItem " "
 
  Do Until rs.EOF
    cboValidateTable_3.AddItem rs!column_Name
    rs.MoveNext
  Loop

0
 
wizard2072098Commented:
I'd say that if you're already down to the table level, meaning you know which table you're looking at, your best bet is to just create an empty recordset against the table and cycle through the fields collection, as such:

set rs = cn.Open("select * from <table> where 1=2") -- or some such SQL...

for i = 0 to rs.Fields.Count - 1
   Debug.Print rs.Fields(i).Name
next i

Once you have the table name, you can get to the fields, indexes or whatever. The example you gave will work, but like you said, it could conceivably take quite a while.

Hope this helps.

0
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

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now