Programatically getting DSN's, DB's and Tables 1

[MelissaEvans]

I have an app that is very configurable. It talks to a table, but the user decides what table it uses. Not only that, but the user decides what ODBC DSN to use, and which database to use. Currently, I have a configuration tool where the user types in this informtaion, but that's very prone to error. I would rather give the user the options that are available. I would like to populate a combo box with the DSN's the user can choose from. Once one of those is picked, then I want to provide the user with a list of Database names that the DSN can get into. Once a Database is picked, I would like to give a list of the tables within the database. This way the user just picks from a list rather than type in the information.

How do I get a list of DSN's, DB's, and Tables?

 

[HermanvLimbeek]

Hi Melissa,

You can find how to list the DSN's in faq222-82. Once you have that done, it's easy to enumerate through db's and tables.

Success, Herman :-Q

 

[MelissaEvans]

Thanks for the help getting the sysem DSN's.... but I guess I'm being silly. How do you enumerate through the DB's and tables? I'm only looking for SQL Server connections (no Access) if that clarifies anything. =)

I like your smiley! =)
~Melissa

 

[HermanvLimbeek]

You can use SQL-DMO which is a COM-server, installed as part of the SQL Server client installation (version 7.0).
Add a reference to the Microsoft SQLDMO Object Library. And some code to do the job:
dim sqlServer as sqldmo.sqlServer
dim objDatabase as sqldmo.Database
dim objTable as sqldmo.Table

Set sqlServer = new sqldmo.sqlServer
'set SQL Server name, user name and password
sqlServer.Connect "Vale2000s", "sa", ""

Set objDatabase = sqlServer.Databases("Pubs&quot
For Each objTable in objDatabase.Tables
lstTables.AddItem objTables.Name
Next

I haven't tried it myself, but it's an excerpt of the book
Professional ADO 2.5 Programming from Wrox Press which I can recommend if you want to do extensive ADO programming.

Herman :-Q

 

[MelissaEvans]

Thank you!

Wrox must do really well - I've fallen in love with their ADO 2.6 Programmers' Reference book. =)

~Melissa