vba 如何以编程方式创建 DSN？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23413727/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I programmatically create a DSN?
提问by CaptainKid
I have an Access Form that uses a linked sql server table as a datasource. I will need to distribute this file to other users soon and I need a way to programmaticly install the DSN to their machines. This is the process of manually setting up the link:
我有一个使用链接的 sql server 表作为数据源的访问表单。我需要尽快将这个文件分发给其他用户，我需要一种方法来以编程方式将 DSN 安装到他们的机器上。这是手动设置链接的过程：
External Data > More > ODBC Database > Link to data source > Machine data source tab > press new > user data source > sql server > name=up to you; server= serverName > How should SQL server verify the autheticity of the login ID? With windows NT authentication using the network login ID> Attach database File Name (database name) > choose the table and press ok
外部数据 > 更多 > ODBC 数据库 > 链接到数据源 > 机器数据源选项卡 > 按新建 > 用户数据源 > sql server > name=up to you；server= serverName > SQL server 如何验证登录ID的真实性？使用网络登录ID进行Windows NT身份验证>附加数据库文件名（数据库名称）>选择表并按确定
That is what I did to access my table but I would like it so that the user can press a button and get access to the table and at the same time be authenticated by using windows NT authentication.
这就是我访问我的表所做的，但我希望用户可以按下按钮并访问该表，同时使用 Windows NT 身份验证进行身份验证。
I am having trouble finding a way to write this in access vba code can someone direct me in the right direction?
我很难找到一种在访问 vba 代码中编写此代码的方法，有人可以指导我朝正确的方向发展吗？
回答by Albert D. Kallal
As a general rule you find MUCH better success by using a DSN less connection. This will eliminate many issues and problems. How to use a DSN less connection is outlined here:
作为一般规则，您会发现使用无 DSN 连接会取得更好的成功。这将消除许多问题和问题。此处概述了如何使用无 DSN 连接：
http://www.accessmvp.com/DJSteele/DSNLessLinks.html
http://www.accessmvp.com/DJsteele/DSNLessLinks.html
And also you do NOT want to store the user name + password in the connection string, but only “log on” one time. Again this saves huge hassles and also means your connection strings and/or DSN does not have to save and expose the user name and password in the actual links.
而且您也不想在连接字符串中存储用户名 + 密码，而只想“登录”一次。这再次节省了大量麻烦，也意味着您的连接字符串和/或 DSN 不必在实际链接中保存和公开用户名和密码。
And this approach means you can have different logons and NOT have to re-link or change existing table links.
这种方法意味着您可以有不同的登录，而不必重新链接或更改现有的表链接。
The follow shows how to use a cached logon and this thus allows one to have different logons without having to re-link your tables.
下面显示了如何使用缓存登录，从而允许用户进行不同的登录，而无需重新链接您的表。
https://blogs.office.com/en-us/2011/04/08/power-tip-improve-the-security-of-database-connections/
https://blogs.office.com/en-us/2011/04/08/power-tip-improve-the-security-of-database-connections/
I highly recommend you adopt both of the above approaches when using linked tables to SQL server.
我强烈建议您在使用链接表到 SQL 服务器时采用上述两种方法。
回答by Pow-Ian
This question is the first google result for "VBA create DSN", however I do not like the answers since they seem to revolve around touching the registry or otherwise avoiding the use of a DSN. In my case I have a project manager that wants to use a DSN because that is what they are comfortable with and so I could not avoid it. For anyone else struggling with this, I found a very straight forward way to do it elsewhere. Notably starting here.
这个问题是“VBA 创建 DSN”的第一个谷歌结果，但是我不喜欢这些答案，因为它们似乎围绕着接触注册表或以其他方式避免使用 DSN。就我而言，我有一个项目经理想要使用 DSN，因为这是他们的习惯，所以我无法避免。对于其他为此而苦苦挣扎的人，我找到了一种非常直接的方法可以在其他地方做到这一点。尤其是从这里开始。
I used code found there, hereand hereto cobble this together and put it in the open event of a splash screen form:
我使用了在此处和此处找到的代码将其拼凑在一起并将其放入启动屏幕表单的打开事件中：
Private Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" _
(ByVal hwndParent As Long, ByVal fRequest As Long, _
ByVal lpszDriver As String, ByVal lpszAttributes As String) As Long
Private Sub Form_Open(Cancel As Integer)
On Error Resume Next
If fDsnExist("DSN=YOUR_DSN_NAME") = True Then
'Do all of your loading or just close this form.
Else
Dim doContinue As Integer
doContinue = MsgBox("There is an issue with the database connection. This can be corrected now or you can reach out to support." _
& vbCrLf & vbCrLf & "Do you want to attempt to correct the issue now?", vbYesNo, "Connection Error")
If doContinue = vbYes Then
Dim vAttributes As String
vAttributes = "DSN=YOUR_DSN_NAME" & Chr(0)
vAttributes = vAttributes & "Description=Self Explnatory" & Chr(0)
vAttributes = vAttributes & "Trusted_Connection=Yes" & Chr(0)
vAttributes = vAttributes & "Server=YOUR_SQL_SERVER_ADDRESS" & Chr(0)
vAttributes = vAttributes & "Database=YOUR_DATABASE_NAME" & Chr(0)
SQLConfigDataSource 0&, 1, "SQL Server", vAttributes
If Err.Number <> 0 Then
MsgBox "The connection could not be restored. Please report this error to support: " & vbCrLf & vbCrLf & Err.Description
Err.Clear
DoCmd.Close acForm, "frmSplash"
DoCmd.Quit acQuitSaveNone
Else
MsgBox "The Connection has been restored.", , "Success"
End If
Else
MsgBox "Please contact support to resolve this issue.", vbCritical + vbOKOnly, "Error"
DoCmd.Close acForm, "frmSplash"
DoCmd.Quit acQuitSaveNone
End If
End If
End Sub
Function fDsnExist(strDsn)
On Error Resume Next
' ------------------------------------------------------
' Declare Variables
' ------------------------------------------------------
Dim objConnection
Dim strReturn
' ------------------------------------------------------
' Create database object
' ------------------------------------------------------
Set objConnection = CreateObject("ADODB.Connection")
objConnection.ConnectionString = strDsn
objConnection.Open
' ------------------------------------------------------
' Check if database is open Correctly
' ------------------------------------------------------
If Err.Number <> 0 Then
strReturn = False
Err.Clear
Else
strReturn = True
' ------------------------------------------------------
' Close database connection
' ------------------------------------------------------
objConnection.Close
End If
Set objConnection = Nothing
' ------------------------------------------------------
' Return database status
' ------------------------------------------------------
fDsnExist = strReturn
End Function
Now when the user opens the access database, the splash form checks for the existence of the DSN and if it is not found, gives the user an option to create it.
现在，当用户打开访问数据库时，启动表单会检查 DSN 是否存在，如果未找到，则为用户提供创建它的选项。
You mentioned NT authentication; I use a trusted connection under the assumption that the user is already logged into a domain and has been provided access using those credentials to the SQL server. You may need to modify the DSN connection string in order to prompt for a password and username.
您提到了 NT 身份验证；我使用受信任的连接，假设用户已经登录到域并且已使用这些凭据提供对 SQL 服务器的访问权限。您可能需要修改 DSN 连接字符串以提示输入密码和用户名。
回答by RubberDuck
First and foremost, Albert D. Kallal is absolutely correct with his answer. If you can use a DSN-less connection, you should. However, for the sake of answering the question you asked...
首先，Albert D. Kallal的回答绝对正确。如果您可以使用无 DSN 连接，则应该使用。但是，为了回答你提出的问题......
ODBC DSN entries are stored in the windows registry. You can add by directly modifying the windows registry. I DO NOT recommend this if you are not familiar with the registry. You can brick a machine if you remove/alter the wrong keys. The particular keys we're looking for are located under Software/ODBC of HKEY_LOCAL_MACHINE and HKEY_CURRENT_USER, depending on whether we're looking for a System or User odbc connection respectively.
ODBC DSN 条目存储在 Windows 注册表中。可以通过直接修改windows注册表来添加。如果您不熟悉注册表，我不建议您这样做。如果您删除/更改错误的密钥，您可以将机器变砖。我们要查找的特定键位于 HKEY_LOCAL_MACHINE 和 HKEY_CURRENT_USER 的 Software/ODBC 下，具体取决于我们分别查找的是 System 还是 User odbc 连接。
My solution is too long and involved to post in it's entirety on Stack Overflow. You can find it on my blog under VBA ODBC DSN Installercomplete with class module downloads and examples of how to use them. (Full disclosure, one of them was originally written by Steve McMahon, but I have modified it for use with MS Access.)
我的解决方案太长，无法在 Stack Overflow 上完整发布。您可以在我的博客中的 VBA ODBC DSN 安装程序下找到它，包括类模块下载和如何使用它们的示例。（完全公开，其中之一最初是由 Steve McMahon 编写的，但我已对其进行了修改以用于 MS Access。）
The short version is I built a DSN class on top of Mr. McMahon's registry class in order to install DSNs when my MS Access application is started.
简短的版本是我在 McMahon 先生的注册表类之上构建了一个 DSN 类，以便在我的 MS Access 应用程序启动时安装 DSN。
