Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Checking out a database's structure

Posted on 1998-11-30
4
Medium Priority
?
194 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
[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
  • 2
  • 2
4 Comments
 
LVL 1

Accepted Solution

by:
wizard2072098 earned 280 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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 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…
Suggested Courses

670 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