Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Checking out a database's structure

Posted on 1998-11-30
4
Medium Priority
?
195 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 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

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…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

927 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