Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 167
  • Last Modified:

CHALLENGE - Data Fields Retrieval

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
muhammasif
Asked:
muhammasif
  • 8
  • 4
  • 3
  • +1
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
Use the ADO OpenSchema method. See examples at http://support.microsoft.com/default.aspx?scid=kb;en-us;Q186246
0
 
muhammasifAuthor Commented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
muhammasifAuthor Commented:
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
 
inthedarkCommented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
>>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
 
riaz9Commented:
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
 
muhammasifAuthor Commented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
If you only want to list tables and fields, you don't need more than OpenSchema!
0
 
muhammasifAuthor Commented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
How oConn is declared?

Dim oConn As New ADODB.Connection
0
 
Éric MoreauSenior .Net ConsultantCommented:
0
 
riaz9Commented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
Connection is not declared in ADO Ext.

Reference Microsoft ActiveX Data Objects 2.x Library
0
 
riaz9Commented:
You are right emoreau, you need to set a reference to Microsoft ActiveX Data Objects 2.x Library also.
0
 
Éric MoreauSenior .Net ConsultantCommented:
any progress?
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 8
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now