Solved

CHALLENGE - Data Fields Retrieval

Posted on 2002-03-03
16
156 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 69

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 69

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
 

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 69

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 69

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 69

Expert Comment

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

Dim oConn As New ADODB.Connection
0
 
LVL 69

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 69

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 69

Expert Comment

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

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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 Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

747 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

11 Experts available now in Live!

Get 1:1 Help Now