odbc data sources?

[Jamppa]

Hi,

Is there any way in VB to get a list on system DSNs which you have configured in ODBC data source administrator window?

thanks

[Serge]

Sure! Here's the function I wrote a while back to get DSNs (User DSN or System DSN)


Private Declare Function RegEnumKeyEx Lib "advapi32.dll" Alias "RegEnumKeyExA" (ByVal hKey As Long, ByVal dwIndex As Long, ByVal lpName As String, lpcbName As Long, ByVal lpReserved As Long, ByVal lpClass As String, lpcbClass As Long, lpftLastWriteTime As FILETIME) As Long
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 RegCloseKey Lib "advapi32.dll" (ByVal hKey As Long) As Long
Private Const HKEY_CURRENT_USER = &H80000001
Private Const HKEY_LOCAL_MACHINE = &H80000002
Private Const ERROR_SUCCESS = 0&
Private Const KEY_QUERY_VALUE = &H1
Private Const KEY_NOTIFY = &H10
Private Const READ_CONTROL = &H20000
Private Const KEY_ENUMERATE_SUB_KEYS = &H8
Private Const STANDARD_RIGHTS_READ = (READ_CONTROL)
Private Const SYNCHRONIZE = &H100000
Private Const KEY_READ = ((STANDARD_RIGHTS_READ Or KEY_QUERY_VALUE Or KEY_ENUMERATE_SUB_KEYS Or KEY_NOTIFY) And (Not SYNCHRONIZE))
Private Type FILETIME
dwLowDateTime As Long
dwHighDateTime As Long
End Type
Public Enum EnumDSNType
eUser = 1
eSystem = 2
End Enum


Public Function GetDataSources(pDSNType As EnumDSNType) As Variant
Dim strKey As String
Dim i As Integer
Dim strDSN As String
Dim lngRet As Long
Dim lKeyHandle As Long
Dim lngSize As Long
Dim strClass As String
Dim lngClassSize As Long
Dim timeBuffer As FILETIME
Dim arrDSN() As Variant

strKey = "Software\ODBC\ODBC.INI"
If pDSNType = eUser Then
lngRet = RegOpenKeyEx(HKEY_CURRENT_USER, strKey, 0, KEY_READ, lKeyHandle)
Else
lngRet = RegOpenKeyEx(HKEY_LOCAL_MACHINE, strKey, 0, KEY_READ, lKeyHandle)
End If

strDSN = Space(255)
lngSize = Len(strDSN)
strClass = Space(255)
lngClassSize = Len(strClass)
lngRet = RegEnumKeyEx(lKeyHandle, i, strDSN, lngSize, 0, strClass, lngClassSize, timeBuffer)

Do Until lngRet <> ERROR_SUCCESS
strDSN = Left(strDSN, InStr(strDSN, vbNullChar) - 1)
ReDim Preserve arrDSN(i)
arrDSN(i) = strDSN
i = i + 1
strDSN = Space(255)
lngSize = Len(strDSN)
strClass = Space(255)
lngClassSize = Len(strClass)
lngRet = RegEnumKeyEx(lKeyHandle, i, strDSN, lngSize, 0, strClass, lngClassSize, timeBuffer)
Loop
RegCloseKey lKeyHandle
GetDataSources = arrDSN
End Function


Usage: Array = GetDataSources(DSNType

Example: Drop a Listbox (List1) and a Command Button (Command1) on the form. Copy this code:


Private Sub Command1_Click()
Dim arrDSN() As Variant
Dim i As Integer

arrDSN = GetDataSources(eSystem)
For i = LBound(arrDSN) To UBound(arrDSN)
List1.AddItem arrDSN(i)
Next
End Sub



------------------

Serge

Software Developer
Serge_Dymkov@vertexinc.com
Access8484@aol.com
ICQ#: 51055819 (http://www.icq.com/51055819)



[Edited by Serge on 10-30-2000 at 11:15 AM]

[Jamppa]

Thanks Serge

Ticks like a clock.

Jamppa