我已经使用本CLOTHIER的建议，从他的办公室博客电源设计小贴士（的http://blogs.office.com/2011/04/08/power-tip-improve-the-security-of-database-connections/)创建具有缓存的凭据，以便用户的UID和PWD不会被保存，或者需要多次的访问接口，工作时一个DSN的连接。有其他人做到了这一点吗？如果有，是什么一直是你的方式，当你需要使用ADO连接，而不是DOA通过VBA从Access到达SQL？你怎么开，而无需再次提供用户名和密码，也不必把它放在code一个ADODB连接？ （我使用的是访问2013前端，SQL 2008 R2后端，SQL Server安全） 在此先感谢！

I have used Ben Clothier's suggestion from his Office Blog Power Tip (http://blogs.office.com/2011/04/08/power-tip-improve-the-security-of-database-connections/) 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!

我的缓存的连接code是这样的：

My Cached Connection code works like this:

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;" & _
             "SERVER=******;" & _
             "APP=Microsoft Office 2010;" & _
             "DATABASE=******;" & _
             "Network=DBMSSOCN;"

    Set dbs = DBEngine(0)(0)
    Set qdf = dbs.CreateQueryDef("")
    With qdf
        .Connect = strConnection & _
             "UID=" & strUserName & ";" & _
             "PWD=" & strPassword & ";"
        .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

后来，当我需要访问的数据，我可以做到这一点（请注意，UID和PWD不要求）：

Then when I need to access data I can do this (Note the UID and PWD are not required):

Dim dbs As DAO.Database 
Set dbs = OpenDatabase("", False, False, "ODBC;DRIVER=sql server;SERVER=*****;APP=Microsoft Office 2010;DATABASE=*****;Network=DBMSSOCN")

我还可以设置ODBC连接传递查询，以及在Access或VBA。但是这些连接只能工作在连接字符串是相同的东西最初是用我的缓存的连接code。所以，当我需要一个ADODB连接（因为它似乎有时ADO是必要的？），字符串显然不会是相同的。照片 例如：

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. For Example:

 Dim cn As New ADODB.Connection
 cn.Open "Provider = sqloledb;Data Source=*same as "SERVER"*;Initial Catalog=*same as "DATABASE"*;User Id=****;Password=****"

这类型的连接只能如果我提供用户ID和密码。我怎么能写这样我不需要它们？ 〜谢谢！

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!

推荐答案

虽然获得了关于安全的一些薄弱环节，你可以做一些事情，以尽量减少风险。其中之一是编译DB到ACCDE。这种方式的VBA被编译和不可见的。

Although ACCESS has some weak points regarding security, you can do few things to minimize the risks. One of them would be compile the DB to ACCDE. This way VBA is compiled and not visible.

您可以创建一个公共函数返回一个字符串

You can create a public function that returns a string

Public Function GET_CONNECTION_STRING() as STRING
' construct your connection string here with server name and password
    GET_CONNECTION_STRING = "DRIVER={" & Driver & "};PORT=" & mPort & ";DATABASE=" & mDatabase & ";SERVER={" & mServer & "};UID=" & mUser & ";PWD={" & mPassword & "};"
End Function

然后创建一个应用程序打开时运行的AutoExe宏。 在AutoExe执行清爽链接到你的链接表。类似的东西你所拥有的。

then create an AutoExe macro that runs when the application is opened. in your AutoExe perform refreshing links to your linked tables. something similar to what you have.

For Each tdf In db.TableDefs
   If tdf.connect <> vbNullString Then
       tdf.connect = GET_CONNECTION_STRING & ";TABLE=" & tdf.name
       tdf.RefreshLink
    End If
Next tdf

您可以通过查询现有的传球做同样的：

you can do the same for existing pass through queries:

For Each myQuerydef In MyDB.QueryDefs
    If Left(myQuerydef.connect, 4) = "ODBC" Then
        myQuerydef.connect = "ODBC;" & GET_CONNECTION_STRING
        myQuerydef.Close
    End If
   Next

此外，你可以有一些其他的公共职能，以获得当前登录的用户名。 像

in addition you can have some other public functions to get current logged in username. something like

public function getCrruserID() as int
   'check your public variable crr_user_id if its empty redirect to login
   if nz(crr_user_id,0) = 0 then
     'go to login and save the user id after successful login
   else
      getCrruserID = crr_user_id
   end if
end function

使用简单的DAO执行sql code像

use simple DAO to execute sql code like

dim db as DAO.Database
set db = currentdb
dim rs as Dao.Recordset
set rs = db.openrecordset("select something from your linked table")

或

db.execute "update command", dbfailonerror

你的最后一个问题。如果保存的东西在内存中，一旦您的应用程序被关闭，将被销毁。

to your last question. if you save something in memory it will be destroyed once your application is closed.

编辑： 如果你有超过50个链接表可能是不刷新它们在每次启动一个好主意。相反，你可以创建一个本地表包含您的[local_Appversion，isFreshInstall]和其他一些变量，根据您的需要。每当用户收到更新的freshInstall将是真实，code你的应用程序来连接和刷新所有表。 （只是为了确保客户将获得不间断的连接）

if you have more than 50 linked tables it might be not a good idea to refresh them at every startup. Instead you can create a Local table containing your [local_Appversion, isFreshInstall] and some other variables as per your need. Every time your user receives an update the freshInstall will be true and code your App to connect and refresh all tables. (just to make sure client will get uninterrupted connection)

所以在你autoExe code：如果freshInstall然后 连接并refreshlinks如果不只是设置的connectionString。 （通常登录后闪屏执行此操作） 连接成功后只是更新本地isFreshInstall值设置为false一个更快的启动下一次。

so in your autoExe code: if its freshInstall then connect and refreshlinks if not just set the connectionString. (usually a splash screen after login to perform this action) After successful connection just update the local isFreshInstall value to false for a quicker start next time.

您也可以有一个专门的菜单，用户可以点击并手动刷新链接。（万一要是连接掉下来） 就像是

you can also have a dedicated menu where user can click and refresh links manually.(in case if the connection get dropped) something like

如果您的组织拥有一个域名，你可以允许受信任的连接使用Windows登录名 祝你好运。

if your organisation has a domain you can allow trusted connection using windows login name good luck.