[dba-SQLServer]Changing Connection string server

Thanks Arthur.  Unfortunately, in my case I don't have access to the server 
to be able to do the initial logging in.  However I do know the name of the 
server so I came up with this solution which seems to work.  The Prompt 
property seems to only work when opening a connection, and not setting the 
applications connection string (Not sure why) -

Public Function basSetAucklandConnection()

     On Error GoTo Err_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 sDatabaseName As String 'The name of the database
     Dim sConnectionString As String

     Application.CurrentProject.OpenConnection ""
     sServerName = "AUCKLAND" 'Local MSDE or SQL Server
     sDatabaseName = "SQLbe" 'Name of the database

     Dim cnn As ADODB.Connection, intStartPos As Integer, intStopPos As Integer

StartHere:
     Set cnn = New ADODB.Connection
     With cnn
         .Provider = "SQLOLEDB"
         .Properties("Data Source") = sServerName
         .Properties("Initial Catalog") = sDatabaseName
         .Properties("Prompt") = adPromptAlways
         .Open
     End With
     intStartPos = InStr(cnn.ConnectionString, "User ID=") + 8
     intStopPos = InStr(intStartPos, cnn.ConnectionString, ";")
     sUID = Mid$(cnn.ConnectionString, intStartPos, intStopPos - intStartPos)
     intStartPos = InStr(cnn.ConnectionString, "Password=") + 9
     intStopPos = InStr(intStartPos, cnn.ConnectionString, ";")
     sPWD = Mid$(cnn.ConnectionString, intStartPos, intStopPos - intStartPos)
     cnn.Close
     Set cnn = Nothing

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

Exit_basSetAucklandConnection:
     Exit Function

Err_basSetAucklandConnection:
     Select Case Err
         Case -2147217842
             If MsgBox("You need to log in before you can use the 
programme.  Do you want to try again?", vbYesNo) = vbYes Then
                 Resume StartHere
             Else
                 DoCmd.Quit
             End If
         Case Else
             Call basErrorMsg("basSetAucklandConnection")
     End Select
     Resume Exit_basSetAucklandConnection

End Function



At 16/07/2003, you wrote:
>What you're after is exactly how my app behaves, so I thought I should look
>at its connection string. Here it is:
>Provider=Microsoft.Access.OLEDB.10.0;Persist Security Info=False;Data
>Source=rock;User ID='Enter your name and password';Initial
>Catalog=ETS_Current;Data Provider=SQLOLEDB.1
>
>TO achieve this I created a user called 'Enter your name and password'. Then
>when I create the ADE file from the ADP, I log on as that user first. The
>point is that SQL prompts for the password, since there is none provided in
>the connection string.
>
>Which admittedly looks a lot like what you're trying to do. One difference
>is that I don't do it in code, but right from the Access File menu. Of
>course, I'm not trying to pick up the user ID from the system. I make them
>type it in.
>
>Hth,
>Arthur
>
>-----Original Message-----
>From: dba-sqlserver-bounces at databaseadvisors.com
>[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of David
>Emerson
>Sent: July 16, 2003 1:16 AM
>To: dba-SQLServer at databaseadvisors.com
>Subject: RE: [dba-SQLServer]Changing Connection string server
>
>
>Further to my other message - I have found a property called Prompt which
>is supposed to do what I want - but I can't get it to.  I have tried -
>
>       sConnectionString = "PROVIDER=SQLOLEDB.1;PROMPT=1" & _
>            ";PERSIST SECURITY INFO=FALSE;" & _
>            ";INITIAL CATALOG=" & sDatabaseName & ";DATA SOURCE=" &
>sServerName
>
>and
>
>       sConnectionString =
>"PROVIDER=SQLOLEDB.1;PROMPT=adpromptAlways;PASSWORD=" & sPWD & _
>            ";PERSIST SECURITY INFO=FALSE;USER ID=" & sUID & _
>            ";INITIAL CATALOG=" & sDatabaseName & ";DATA SOURCE=" &
>sServerName
>
>but neither work (nor variations).  Any pointers?

Regards

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