ADO connecting as MSDASQL provider

[sultan]

Please someone bring me out this misery, I am
trying to use the OLE DB provider for ODBC but i dont get the same results as i would do if i was using DAO.

I want ADO to display the ODBC select DSN box so that a user may choose any DSN s/he wishes from the DSN list on his/her machine.


Dim cnn1 As ADODB.Connection
Dim rstEmployees As ADODB.Recordset
Dim strCnn As String

Set cnn1 = New ADODB.Connection
cnn1.Open "provider=msdasql;driver = {SQL SERVER};ODBC", "sa"

The above string just throws up an error message asking for ie DSN and server name etc, well each user would have unknown to programmer server names so how do i get this right ...some help please.

thanks

regards
sultan

[sultan]

Come on people I feel as though no body wants to help me out here!

Surly you must have some idea, so please drop few words of advice thanks.

[LG]

May be this will give you some ideas. http://msdn.microsoft.com/library/sdkdoc/dasdk/mdae9flk.htm
Good luck

[sultan]

LG that I have already tried but as you can see from the code below that the string contains the names of the sqlserver, DSN and the rest:
Dim cnn1 As ADODB.Connection

' Open a connection without using a Data Source Name (DSN).
Set cnn1 = New ADODB.Connection
cnn1.ConnectionString = "driver={SQL Server};" & _ "server=bigsmile;uid=sa;pwd=pwd;database=pub"
cnn1.ConnectionTimeout = 30
cnn1.Open


I have found some helping hints in the sqlDMO library but what i want to do is let the user choose the names of DSN at the run time from that ODBC box which comes up and displays list of all DSNs etc.

How can i find the name of SQL Server at runtime on the user's machine becasue i believe that might just solve my problem.

Thanks all


[This message has been edited by sultan (edited 10-08-1999).]