Add Database Names, table names & related fields from table in combo box

[Snowy]

Hello All.

I have three combo box on my vb form namely.
1. cmbDatabases.
2. cmbTables.
3. cmbFields.

I have connected to mysql through ADODB.
Their are above 100 databases in mysql.
What i want is to show these databases names in 1st combo box i.e.
cmbDatabases then
show table names from selected database in 2nd combo box i.e. cmbTables
and then show related fields from tables in 3rd combo box i.e
cmbfields.

can anyone tell me how to fetch databases name, related tables & fields
in combo box.

Thanks
Snowy

 

[Guest]

To get the list of tables, you need to be connected to a database, to get the
list of fields, you need a reference to a table. I am not sure how you will
get a list of databases without a connection; to get a connection you need a
specific database.

If you were using SQL Server, you could use the SQLDMO object; but that does
not work with other databases.

Assuming you have a connection to a database, you can get a list of tables
(and their types) and for each table the fields and their types using the
ADOX.Catalog object.

Set xx=CreateObject("ADOX.Catalog")
xx.ActiveConnection = {whatever that is for mysql}
for each table in xx.Tables
debug.print table.Name
for each fld in table.Fields
debug.print fld.name ' fld.Type
next
next

 

[Snowy]

In ODBC Data Sources Administrator I have configer the DSN name &
through that connect to database. Then I have following code to create
database connection.

Public fileconn As New ADODB.Connection

Public Sub CreateConnections()
fileconn.Open "DSN=<DSN Name>"
End Sub

With this code I'm able to connect to databases.
But what I want; I should able to display database name in combo box.
Their are 100 databases. I want to add all these 100 databases name in
combo box.
After selecting 1 database name among 100 databases from combo box,
Second combo box display the tables from selected databases and 3rd
combo box display fields from that table.
For e.g when you create(Configure) System DSN through Administrative
Tool, Data Source (ODBC). Their are Database selection list. I just
want such list in my VB program.

Please help me

Thanks
Snowy

 

[Guest]

fileconn.Open "DSN=<DSN Name>"

If your statement above is successsful, the DSN has the name of the database
embedded within it, does it not? You are connecting to the database.

In order to enumerate the databases in the Server, you will need to connect
to the serveritself ... there must be a low level ODBC API for this (of which
I have no experience). If you have ODBC help on your PC, look therein. Or
Google.

 

[Guest]

Yes, after all, you can enumerate the database names!! HEre's how:

Sub ss()
cn='your connection string
set xx = CreateObject("ADODB.Connection")
xx.Open cn
set xy = xx.OpenSchema(1) ' Catalogs
debug.print xy.GetString ' prints the names of the databases, delimited
by vbCRLF

End Sub

The xy object has a number of methods; use the GetRows method (with the
appropriate argument) to get 1 row at a time and add it to your COMBO.
You might want to use Early Binding: then Intellisense will give you all
the clues you need.