List ODBC Entries

[Huntedman]

Hi there,

Does anyone know how to list all existing ODBC entries?

Thanks in advance for reading this question.

Greetings & good luck on all your programmig efforts.

Hunted

[Serge]

If you mean to get all Data Source Names, then the next example will populate Listbox or Combobox with available ODBC Data Source Names:

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 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 Sub GetDataSources(pBox As Control) 'Could be Listbox or Combobox
    Dim strKey As String
    Dim i As Integer
    Dim strDSN As String
    Dim lRet As Long
    Dim lKeyHandle As Long
    Dim lSize As Long
    Dim strClass As String
    Dim lClassSize As Long
    Dim timeBuffer As FILETIME
    
    strKey = "Software\ODBC\ODBC.INI"
    
    lRet = RegOpenKeyEx(HKEY_CURRENT_USER, strKey, 0, KEY_READ, lKeyHandle)
    
    strDSN = Space(255)
    lSize = Len(strDSN)
    strClass = Space(255)
    lClassSize = Len(strClass)
    lRet = RegEnumKeyEx(lKeyHandle, i, strDSN, lSize, 0, strClass, lClassSize, timeBuffer)
    
    Do Until lRet <> ERROR_SUCCESS
        strDSN = Left(strDSN, InStr(strDSN, vbNullChar) - 1)
        If TypeOf pBox Is ListBox Or TypeOf pBox Is ComboBox Then
            pBox.AddItem strDSN
        End If
        i = i + 1
        strDSN = Space(255)
        lSize = Len(strDSN)
        strClass = Space(255)
        lClassSize = Len(strClass)
        lRet = RegEnumKeyEx(lKeyHandle, i, strDSN, lSize, 0, strClass, lClassSize, timeBuffer)
    Loop
    
    RegCloseKey lKeyHandle
End Sub

Then you can call this function like:

GetDataSources List1

Best regards,

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

Serge

Software Developer
[email protected]
[email protected]

[iaw1978]

Hi,

I get a few errors when pasting this code into VS2010:

1)

Private Type FILETIME
dwLowDateTime as Long
dwHighDateTime as Long
End Type

* I think I can just change this to Private Structure and declare the dates as Private ... correct?

2)

Do Until lRet <> ERROR_SUCCESS

* I have no idea what needs to be corrected ... the error says lt is not declared?

Help?

[daydemir]

Hello
Did you find any solution for your questions? I am suffering the problem of Do Until lRet <> ERROR_SUCCESS sentences and i tried almost everything but i could not fix it.
Coul you please help?

Thanks

Hi,

I get a few errors when pasting this code into VS2010:

1)

Private Type FILETIME
dwLowDateTime as Long
dwHighDateTime as Long
End Type

* I think I can just change this to Private Structure and declare the dates as Private ... correct?

2)

Do Until lRet <> ERROR_SUCCESS

* I have no idea what needs to be corrected ... the error says lt is not declared?

Help?

[smendoza]

I did this to get the odbc dsn names

Private Sub DsnLookup()
      'looks up ODBC DSNs from the registry, displays list in a drop drop box.
      Dim dsnNames As New List(Of String)
      Dim reg As Microsoft.Win32.RegistryKey = Registry.LocalMachine.OpenSubKey("Software")

      If reg IsNot Nothing Then
         reg = reg.OpenSubKey("ODBC")
         If reg IsNot Nothing Then
            reg = reg.OpenSubKey("ODBC.INI")
            If reg IsNot Nothing Then
               For Each dsn As String In reg.GetSubKeyNames
                  dsnNames.Add(dsn)
               Next
            End If
         End If
      End If

      'fill drop down box.
      For Each Name As String In dsnNames
         cboDsn.Items.Add(Name)
      Next Name
   End Sub

[daydemir]

Hello

Thanks for your quick response but unfortunately it did not work. It looks like system sees the ODBC.ini as empty and nothing is happening

Does it work in your system without any problem?

I did this to get the odbc dsn names

Private Sub DsnLookup()
      'looks up ODBC DSNs from the registry, displays list in a drop drop box.
      Dim dsnNames As New List(Of String)
      Dim reg As Microsoft.Win32.RegistryKey = Registry.LocalMachine.OpenSubKey("Software")

      If reg IsNot Nothing Then
         reg = reg.OpenSubKey("ODBC")
         If reg IsNot Nothing Then
            reg = reg.OpenSubKey("ODBC.INI")
            If reg IsNot Nothing Then
               For Each dsn As String In reg.GetSubKeyNames
                  dsnNames.Add(dsn)
               Next
            End If
         End If
      End If

      'fill drop down box.
      For Each Name As String In dsnNames
         cboDsn.Items.Add(Name)
      Next Name
   End Sub

[smendoza]

Yeah it worked on my systems, winxp sp2, win 7, win server 2008. You have ODBC's configured on your pc right?

[daydemir]

my OS is windows 7 ultimate and i have 2 odbc connection what are already set but it turns null at the below sentences:
reg = reg.OpenSubKey("ODBC.INI")
If reg IsNot Nothing Then
For Each dsn As String In reg.GetSubKeyNames
dsnNames.Add(dsn)
Next
End If

for each goes to null directly i checked registry and i saw my connection which stays under ODBC.ini

Really strange..I took the exe file and make it run on other PC but nothing changed.Something is missing but i dont know what

Yeah it worked on my systems, winxp sp2, win 7, win server 2008. You have ODBC's configured on your pc right?

[smendoza]

Are your odbc connection 64bit? That was looking at 32bit drivers.

[smendoza]

Try this and have you app compile as 64bit

      Dim dsnNames As New List(Of String)
      Dim reg As Microsoft.Win32.RegistryKey = Registry.LocalMachine.OpenSubKey("Software")

      If reg IsNot Nothing Then
         reg = reg.OpenSubKey("ODBC")
         If reg IsNot Nothing Then
            reg = reg.OpenSubKey("ODBC.INI")
            If reg IsNot Nothing Then
               reg = reg.OpenSubKey("ODBC Data Sources")
               If reg IsNot Nothing Then
                  For Each dsn As String In reg.GetValueNames
                     dsnNames.Add(dsn)
                  Next
               End If
            End If
         End If
      End If