Solved

Checking out a database's structure

Posted on 1998-11-30
4
187 Views
Last Modified: 2010-05-03
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
Comment
Question by:cavacasp
  • 2
  • 2
4 Comments
 
LVL 1

Accepted Solution

by:
wizard2072098 earned 70 total points
ID: 1447504
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
 

Author Comment

by:cavacasp
ID: 1447505
Excatly what I was looking for thanks

0
 

Author Comment

by:cavacasp
ID: 1447506
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
 
LVL 1

Expert Comment

by:wizard2072098
ID: 1447507
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

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

Suggested Solutions

Title # Comments Views Activity
Hide vba in gp 7 113
Problem to line 23 72
Remove Exif Orientation before adding image to db 9 38
Visual Basic 6: Code needed for TripleDES Encryption/Decryption 13 30
There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
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…

680 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