Solved

CHALLENGE - Data Fields Retrieval

Posted on 2002-03-03
16
159 Views
Last Modified: 2010-05-02
There are different database environments - Access, SQL, etc. The issue is to have some code that would first do a search to find out which databases exist; then pick all the field names within the tables of those databases and present those names on a GUI.

e.g. An Access database has a table 'A' with feilds 'a', 'b', 'c'. Another database in SQL has a table 'B' with fields 'x', 'y', 'z'.

The user clicks a button and as a result, a GUI appears showing all the fields names 'a', 'b', 'c', 'x', 'y', 'z'.

Thanks.
0
Comment
Question by:muhammasif
  • 8
  • 4
  • 3
  • +1
16 Comments
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 100 total points
ID: 6837631
Use the ADO OpenSchema method. See examples at http://support.microsoft.com/default.aspx?scid=kb;en-us;Q186246
0
 

Author Comment

by:muhammasif
ID: 6837731
Can it work with databases on the intranet (databases not on your local pc)?

Is there a way to access other databases like Oracle etc?
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 6837852
OpenSchema will work as soon as you have a connection no matter if it is local or not. It will also work with almost all data providers.

For connection strings to Oracle (and others), see http://www.able-consulting.com/ADO_Conn.htm
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

Author Comment

by:muhammasif
ID: 6837968
so far, so good. Actually, I was looking for a way that would search for different databases 'without having to specify the database names'.

e.g.
Dim oODBCConnection As Odbc.OdbcConnection
Dim sConnString As String = "Dsn=myDsn;"

requires the Dsn name to be specified. Is it possible to just specify the various extensions only such as .mdb, .xls etc. and the code looks for all databases with those extensions?

Thanks.
0
 
LVL 17

Expert Comment

by:inthedark
ID: 6838017
emoreau's right. The only problem with openscheema is that Microsoft's vision of a solution fell well short of what should have been provided.  If you need to know the data types and also which fields have been indexed or are primary keys openscheema won't be enough.  And so there is no common tool that you can use to gain a full picture of a database.



0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 6838272
>>Is it possible to just specify the various extensions only such as .mdb, .xls etc. and the code looks for all databases with those extensions?

The problem is that each type of files requires a different provider. More than that, you cannot directly open Oracle or SQL server files. You need to use the database engine. You won't be able to be opened to every kind of databases dynamically.
0
 
LVL 1

Expert Comment

by:riaz9
ID: 6838468
Please see if ADO Extensions for DDL and Security(ADOX) can help you.  I have used ADOX to create a Query Builder wizard which currently connects to Foxpro, SQL Server 7.0 and Access 97/2000.  It automatically identifies the tables, fields, fieldtypes etc., and shows them in list boxes.  But, for each database, I have used different Provider strings. Even the Foxpro connection can be made programatically without explicitly creating an ODBC connection using the control panel applet.
0
 

Author Comment

by:muhammasif
ID: 6838517
riaz9, your solution looks close to what I was looking for. Could you please give an example, or indicate where it can be found on the Internet?
Thanks.
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 6838754
If you only want to list tables and fields, you don't need more than OpenSchema!
0
 

Author Comment

by:muhammasif
ID: 6840028
emoreau, I tried

oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=c:\part.mdb;"

It gives
Run Time Error - 424
Object Required

The path and name of the database are correct.

Thanks.
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 6843342
How oConn is declared?

Dim oConn As New ADODB.Connection
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 6843359
0
 
LVL 1

Expert Comment

by:riaz9
ID: 6843839
In References, set Microsoft ADO Ext. 2.5 for DDL and Security

Public sub TableList(cnConnection as Object)
  Dim tblTable As Table, catCatalog As New ADOX.Catalog, colColumn as Column

  Set catCatalog.ActiveConnection = cnConnection
  For Each tblTable In catCatalog.Tables
    If tblTable.Type = "TABLE" then
     cboTableList.AddItem tblTable.Name
     for each colColumn in tblTable.Columns
       cboColumnList.AddItem tblTable.Name & "." & colColumn.Name
     next
    endif
  next

  set colColumn = nothing
  set tblTable = nothing
  set catCatalog = nothing
end sub

- Please let me know if this solves your problem
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 6843884
Connection is not declared in ADO Ext.

Reference Microsoft ActiveX Data Objects 2.x Library
0
 
LVL 1

Expert Comment

by:riaz9
ID: 6849731
You are right emoreau, you need to set a reference to Microsoft ActiveX Data Objects 2.x Library also.
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 6869328
any progress?
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

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…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…
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…

809 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