[dba-SQLServer]Changing Connection string server

I am getting closer to the solution (I think).

Thanks to Doris for her code but I don't think it is quite what I need.

I now have the following which is called from an autoexec maco -

Public Function basSetAucklandConnection()

     Dim sUID As String 'The user ID
     Dim sPWD As String 'The user password
     Dim sServerName As String 'The name of the MSDE or SQL Server
     Dim sDatabaseFileName As String 'The name of the mdf
     Dim sDatabaseName As String 'The name of the database
     Dim sConnectionString As String

Application.CurrentProject.OpenConnection ""

     sUID = "fred" 'User ID
     sPWD = "mydog" 'Password
     sServerName = "AUCKLAND" 'Local MSDE or SQL Server
     sDatabaseName = "MySQLbe" 'Name of the database

     'Connect this adp to new database.
      sConnectionString = "PROVIDER=SQLOLEDB.1;PASSWORD=" & sPWD & _
           ";PERSIST SECURITY INFO=FALSE;USER ID=" & sUID & _
           ";INITIAL CATALOG=" & sDatabaseName & ";DATA SOURCE=" & sServerName
     Application.CurrentProject.OpenConnection sConnectionString

..etc

This works except for one problem - the user doesn't need to put in a 
password (hence no security)!!

What I want is for the normal SQL logon box to appear asking for user and 
password.

If there is not a simple way to do this then I may have to resort to an 
Access pop up box asking for the details and then feed these into the sUID 
and sPWD variables before putting them into the sConnectionString.


Regards

David Emerson
DALYN Software Ltd
25b Cunliffe St, Johnsonville
Wellington, New Zealand
Ph/Fax (877) 456-1205