HOWTO: Programmatically create DSN to SQL in VB6.0

[Jay Devlin]

Well, the title speaks for itself. I want to programmatically create a DSN
to connect to SQL Server that my program will then use as a persistant
connection. I don't want to store Uid and Pwd anywhere as plain text so I
need the set up a DSN.

Any help will be most appreciated. Jay D!

[Garreth]

I found a typo in my first attempt. Let me try again:

You can use a DSN-less connection with RDO. I don't think you can do so in
DAO, but even so the DSN's UID and PWD can be specified in your code rather
than in the DSN itself.

Here's a bit of sample code for the RDO DSN-less connection approach
(cribbed and trimmed from a report that works, but this snippet is not
tested on its own):

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim en As rdoEnvironment
Dim cn As rdoConnection
Dim qyTopTen As rdoQuery
Dim rsTopTen As rdoResultset
Dim Conn$, Query$

' connection parms for dsn-less connection
Conn$ = "UID=MyUserName;PWD=MyPwd;Database=MyDB;" _
& "Server=MyServer;Driver={SQL Server};"

Set en = rdoEnvironments(0)
en.CursorDriver = rdUseOdbc

Set cn = en.OpenConnection(dsname:="", _
prompt:=rdDriverNoPrompt, Connect:=Conn$)

cn.QueryTimeout = 300

Query$ = "exec ersp_topten ?, ?"

' create rdo query object
Set qyTopTen = cn.CreateQuery("top10", Query$)

' set parameters for query
qyTopTen(0) = MyForm.mskStartDate
qyTopTen(1) = MyForm.mskEndDate

' create and open rdo resultset
Set rsTopTen = qyTopTenCaseType.OpenResultset(rdOpenStatic)

[Jay Devlin]

Thanks for that Garreth,
but I really need it to be a dsn so that a password is not needed with
Integrated Security, and asked for with Standard Security. I won't know what
username and password to put in the code, in order to use dsn-less
connection.
Any other ideas would be helpful

cheers Jay D!

[Jay Devlin]

It's okay folks, I've found a HOWTO article on the subject. Q184608