vba 枚举计算机上设置的 DSN 列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/164967/
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
Enumerating the list of DSN's set up on a computer
提问by AR.
I have an Excel application in which I want to present the user with a list of the Data Source Names (ie: DSN's), whereby s/he can choose what data source to use.
我有一个 Excel 应用程序，我想在其中向用户显示数据源名称列表（即：DSN），以便他/她可以选择要使用的数据源。
Hopefully once I've got the list, I can easily access the DSN properties to connect to the appropriate database.
希望一旦我获得了列表，我就可以轻松访问 DSN 属性以连接到适当的数据库。
Please note, I do notwant to use a DSN-less connection.
请注意，我不希望使用一个DSN-less连接。
回答by Jorge Ferreira
The DSN entries are stored in the registry in the following keys.
DSN 条目存储在注册表中的以下项中。
HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\ODBC Data Sources HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources
This contains the list of all defined DSN. This acts as an global index and the specific details for each DSN are stored in a key with the DSN name under:
这包含所有定义的 DSN 的列表。这充当全局索引，每个 DSN 的特定详细信息都存储在一个键中，DSN 名称位于：
HKEY_CURRENT_USER\Software\ODBC\ODBC.INI HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI
Create some entries in both User DSN and System DSN tabs from Data Sources (ODBC) control panel applet and check how these values are stored in the registry.
在数据源 (ODBC) 控制面板小程序的用户 DSN 和系统 DSN 选项卡中创建一些条目，并检查这些值如何存储在注册表中。
The following example enumerate the DSN defined for the user trough Control Panel > Administrative Tools > Data Sources (ODBC) [User Dsn Tab].
以下示例通过控制面板 > 管理工具 > 数据源 (ODBC) [用户 Dsn 选项卡] 枚举为用户定义的 DSN。
http://support.microsoft.com/kb/178755
http://support.microsoft.com/kb/178755
Option Explicit
Private Declare Function RegOpenKeyEx Lib "advapi32.dll" _
Alias "RegOpenKeyExA" _
(ByVal hKey As Long, _
ByVal lpSubKey As String, _
ByVal ulOptions As Long, _
ByVal samDesired As Long, phkResult As Long) As Long
Private Declare Function RegEnumValue Lib "advapi32.dll" _
Alias "RegEnumValueA" _
(ByVal hKey As Long, _
ByVal dwIndex As Long, _
ByVal lpValueName As String, _
lpcbValueName As Long, _
ByVal lpReserved As Long, _
lpType As Long, _
lpData As Any, _
lpcbData As Long) As Long
Private Declare Function RegCloseKey Lib "advapi32.dll" _
(ByVal hKey As Long) As Long
Const HKEY_CLASSES_ROOT = &H80000000
Const HKEY_CURRENT_USER = &H80000001
Const HKEY_LOCAL_MACHINE = &H80000002
Const HKEY_USERS = &H80000003
Const ERROR_SUCCESS = 0&
Const SYNCHRONIZE = &H100000
Const STANDARD_RIGHTS_READ = &H20000
Const STANDARD_RIGHTS_WRITE = &H20000
Const STANDARD_RIGHTS_EXECUTE = &H20000
Const STANDARD_RIGHTS_REQUIRED = &HF0000
Const STANDARD_RIGHTS_ALL = &H1F0000
Const KEY_QUERY_VALUE = &H1
Const KEY_SET_VALUE = &H2
Const KEY_CREATE_SUB_KEY = &H4
Const KEY_ENUMERATE_SUB_KEYS = &H8
Const KEY_NOTIFY = &H10
Const KEY_CREATE_LINK = &H20
Const KEY_READ = ((STANDARD_RIGHTS_READ Or _
KEY_QUERY_VALUE Or _
KEY_ENUMERATE_SUB_KEYS Or _
KEY_NOTIFY) And _
(Not SYNCHRONIZE))
Const REG_DWORD = 4
Const REG_BINARY = 3
Const REG_SZ = 1
Private Sub Command1_Click()
Dim lngKeyHandle As Long
Dim lngResult As Long
Dim lngCurIdx As Long
Dim strValue As String
Dim lngValueLen As Long
Dim lngData As Long
Dim lngDataLen As Long
Dim strResult As String
lngResult = RegOpenKeyEx(HKEY_CURRENT_USER, _
"SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources", _
0&, _
KEY_READ, _
lngKeyHandle)
If lngResult <> ERROR_SUCCESS Then
MsgBox "Cannot open key"
Exit Sub
End If
lngCurIdx = 0
Do
lngValueLen = 2000
strValue = String(lngValueLen, 0)
lngDataLen = 2000
lngResult = RegEnumValue(lngKeyHandle, _
lngCurIdx, _
ByVal strValue, _
lngValueLen, _
0&, _
REG_DWORD, _
ByVal lngData, _
lngDataLen)
lngCurIdx = lngCurIdx + 1
If lngResult = ERROR_SUCCESS Then
strResult = strResult & lngCurIdx & ": " & Left(strValue, lngValueLen) & vbCrLf
End If
Loop While lngResult = ERROR_SUCCESS
Call RegCloseKey(lngKeyHandle)
Call MsgBox(strResult, vbInformation)
End Sub
回答by Dimitri C.
You can use the SQLDataSourcesfunction of the ODBC API. See MSDN documentation.
您可以使用ODBC API的SQLDataSources函数。请参阅MSDN 文档。
回答by blah
Extremely cool solution. I ran into an issue where CURRENT_USER wasn't showing all the DSN's, certainly not the one I needed. I changed it to LOCAL_MACHINE and saw all DSN's that showed up in the Connection Manager, including the subset that showed up under CURRENT_USER.
非常酷的解决方案。我遇到了一个问题，即 CURRENT_USER 没有显示所有的 DSN，当然不是我需要的。我将其更改为 LOCAL_MACHINE 并看到了所有出现在连接管理器中的 DSN，包括出现在 CURRENT_USER 下的子集。
http://msdn.microsoft.com/en-us/library/windows/desktop/ms712603(v=vs.85).aspx
http://msdn.microsoft.com/en-us/library/windows/desktop/ms712603(v=vs.85).aspx
