title: Keeping UID and PWD out of an ADO connection string in an ODBC DSN-less Database and a DAO cached connection?
body: <p>I have used Ben Clothier's suggestion from his Office Blog Power Tip (<a href="http://blogs.office.com/2011/04/08/power-tip-improve-the-security-of-database-connections/" rel="nofollow">http://blogs.office.com/2011/04/08/power-tip-improve-the-security-of-database-connections/</a>) to create a DSN-less connection with cached credentials so that users' UID and PWD aren't saved, or required multiple times, when working in the Access interface.  Have others done this?  If so, what has been your approach when you need to use an ADO connection instead of DOA to reach SQL from Access via VBA?  How do you open a adodb connection without having to provide the User ID and Password again, or having to put it in the code? 
  (I'm using Access 2013 frontend, SQL 2008 R2 backend, SQL Server Security)
  Thanks in advance!</p>
  
  <p>My Cached Connection code works like this:</p>
  
  <pre><code>Public Function InitConnect(strUserName As String, strPassword As String) As Boolean
  ' Description:  Is called in the application’s startup 
  '               to ensure that Access has a cached connection
  '               for all other ODBC objects’ use.
  
      Dim dbs As DAO.Database
      Dim qdf As DAO.QueryDef
      Dim rst As DAO.Recordset
      Dim strConnection As String
  
  
      strConnection = "ODBC;DRIVER=sql server;" &amp; _
               "SERVER=******;" &amp; _
               "APP=Microsoft Office 2010;" &amp; _
               "DATABASE=******;" &amp; _
               "Network=DBMSSOCN;"
  
      Set dbs = DBEngine(0)(0)
      Set qdf = dbs.CreateQueryDef("")
      With qdf
          .Connect = strConnection &amp; _
               "UID=" &amp; strUserName &amp; ";" &amp; _
               "PWD=" &amp; strPassword &amp; ";"
          .SQL = "Select Current_User;"
  
          Set rst = qdf.OpenRecordset(dbOpenSnapshot, dbSQLPassThrough)
  
     End With
      InitConnect = True
  
  ExitProcedure:
      On Error Resume Next
          Set rst = Nothing
          Set qdf = Nothing
          Set dbs = Nothing
      Exit Function
  End Function
  </code></pre>
  
  <p>Then when I need to access data I can do this (Note the UID and PWD are not required):</p>
  
  <pre><code>Dim dbs As DAO.Database 
  Set dbs = OpenDatabase("", False, False, "ODBC;DRIVER=sql server;SERVER=*****;APP=Microsoft Office 2010;DATABASE=*****;Network=DBMSSOCN")
  </code></pre>
  
  <p>I can also set the ODBC connection to pass-through queries as well in Access or VBA.  But these connections work only when the connection string is IDENTICAL to what was originally used in my Cached Connection code.  So, when I need an ADODB connection (as it seems sometimes ADO is needed?), the string obviously isn't going to be identical.<br>
  For Example:</p>
  
  <pre><code> Dim cn As New ADODB.Connection
   cn.Open "Provider = sqloledb;Data Source=*same as "SERVER"*;Initial Catalog=*same as "DATABASE"*;User Id=****;Password=****"
  </code></pre>
  
  <p>This type of connection only works if I supply a User Id  and Password.  How can I write it so that I don't need them?  ~Thanks!</p>