Solved

Checking out a database's structure

Posted on 1998-11-30
4
182 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
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…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

743 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