Why not just use a DSN-less connection? It's very easy to build all of the required elements for a connection string from either user input or system data, or both. The user can be prompted for both a user name and password if desired, or a password only. You'll see this on web sites (Yahoo! Mail, for example) where they give you the option of signing in as a different user, or maintaining the same user account (you still need to enter a password)
IMHO DSNs are not worth the hassle anymore, especially if you're using ADO to connect to a database. They really don't provide any benefit.
Yes, the DSN-less connections are great I was using it all the time before.
But the problem is that my management decided to host my web application somewhere else (another city) and they don't want the uid and password hardcoded into my app. The DBA wants to be able to create a database for this app and place it at any location he likes. If we use DSN than he can simply configure DSN without contacting me (developer). If we use DSN-less connection I have to send him updated version of my VB component.
Basically this is your classic trade-off between security and usability...
If you feel comfortable keeping your password in a file, there is no reason why you can't create an ini file that your application would read on start-up. This file can contain all the configuration parameters needed by your application, including your database password (possibly encrypted using Crypto API or something).
--------------- Strong SQL Developer wanted in the Boston area. Please e-mail if interested.