API call to get List of ODBC DSNs - Page 2
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 20 of 20

Thread: API call to get List of ODBC DSNs

  1. #11
    New Member
    Join Date
    Mar 2002
    Location
    Forest, VA
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I'm sorry; we're still not on the same playing field. I will give this one more try and then I will just give up.
    Here goes: If you open the ODBC control panel (on Windows 2000 it is in "Administrative Tools" then "Data Sources (ODBC)") you will get a dialog box titled: ODBC Data Source Administrator with 7 tabs along the top. The first two are "User DSN" and "System DSN". On either of these tabs, there is a two column listbox called: "User Data Sources" and "System Data Sources" respectively. Each list has two columns labeled "Name" and "Driver". I want that same list. I don't need the driver column, just the name column. If I can get the User DSN list then, I can get the other. The only difference is the path and that has been amply spelled out. So, just tell me how to get that list and we'll be home free! Again, I'm sorry I've taken up so much of your time. I appreciate your help.

    Best regards,
    Vlip

  2. #12
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry a wild stab, can you not importthe same table twice ubder ODBC MS Query ruling this will work,, else i suggets asking the IT dept, i cant see the data from ghere somethings a drif..i agree..

    sorry of little help... i wish i could do more, i really do..

    thought...


    winy2k do you use Kopen3Live???
    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  3. #13
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    is trghe oais abou as usdefyk as jacks>

    > >???

    anywone oalsww have a duckidn ckluse?

  4. #14
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry Im English and poor English at that, German i guess sorry need translation...
    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  5. #15
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    This will load a string array with user and sys DSNs.
    As is, places on sheet1 A1:A?
    Edit to suit.
    If this is not what you need then I can not help you.
    Good Day.
    Tom

    Example VBA Code:
    Private Declare Function SQLDataSources Lib "odbc32.dll" _
    (ByVal hEnv As Long, _
    ByVal fDirection As Integer, _
    ByVal szDSN As String, _
    ByVal cbDSNMax As Integer, _
    pcbDSN As Integer, _
    ByVal szDescription As String, _
    ByVal cbDescriptionMax As Integer, _
    pcbDescription As Integer) As Long

    Private Declare Function SQLAllocHandle Lib "odbc32.dll" _
    (ByVal HandleType As Integer, _
    ByVal InputHandle As Long, _
    OutputHandlePtr As Long) As Long

    Private Declare Function SQLSetEnvAttr Lib "odbc32.dll" _
    (ByVal EnvironmentHandle As Long, _
    ByVal dwAttribute As Long, _
    ByVal ValuePtr As Long, _
    ByVal StringLen As Long) As Long

    Private Declare Function SQLFreeHandle Lib "odbc32.dll" _
    (ByVal HandleType As Integer, _
    ByVal Handle As Long) As Long

    Private Const DSN_LEN As Long = 32
    Private Const DESC_LENGTH As Long = 128
    Private Const FoundOk As Long = 0
    Private Const GetNext As Long = 1
    Private Const Null_HANDLE As Long = 0
    Private Const HANDLE_ENV As Long = 1
    Private Const ATTR_ODBC_VERSION As Long = 200
    Private Const OV_ODBC3 As Long = 3
    Private Const IS_INTEGER As Long = (-6)
    Private UserSysStringArray() As String

    Sub UserSysDSNs()

    Dim hEnv As Long
    Dim sServer As String
    Dim sDriver As String
    Dim nSvrLen As Integer
    Dim nDvrLen As Integer
    Dim ArrayCntr As Integer
    ArrayCntr = 0



    If SQLAllocHandle(HANDLE_ENV, _
    Null_HANDLE, hEnv) <> 0 Then

    If SQLSetEnvAttr(hEnv, _
    ATTR_ODBC_VERSION, _
    OV_ODBC3, _
    IS_INTEGER) <> 0 Then

    sServer = Space$(DSN_LEN)
    sDriver = Space$(DESC_LENGTH)

    Do While SQLDataSources(hEnv, _
    GetNext, _
    sServer, _
    DSN_LEN, _
    nSvrLen, _
    sDriver, _
    DESC_LENGTH, _
    nDvrLen) = FoundOk

    ArrayCntr = ArrayCntr + 1
    ReDim Preserve UserSysStringArray(1 To ArrayCntr)
    UserSysStringArray(ArrayCntr) = Left$(sServer, nSvrLen)
    sServer = Space$(DSN_LEN)
    Loop
    End If
    Call SQLFreeHandle(HANDLE_ENV, hEnv)
    End If

    ArrayCntr = 0

    Do Until ArrayCntr = UBound(UserSysStringArray)
    ArrayCntr = ArrayCntr + 1
    Sheet1.Range("A" & ArrayCntr).Value = UserSysStringArray(ArrayCntr)
    Loop

    End Sub


  6. #16
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    P.S.
    I'm assuming that your are fairly familiar with VBA. If not let me know. Seeing that you have went through so much trouble, I tested this code on two machines. Win ME and W2k. And then cross referenced the list produced with that in the registry. Both were correct. Hope it helps you.
    I did not have any problem with the API as part of the user group(Microsoft's Default settings for the user group) in Win2k.
    Tom

  7. #17
    New Member
    Join Date
    Mar 2002
    Location
    Forest, VA
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Finally! That's exactly what I wanted! Thank you very much!

    Vlip

  8. #18
    Board Regular
    Join Date
    Nov 2003
    Posts
    440
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Good Thread! Thanks!

  9. #19
    Board Regular
    Join Date
    Nov 2003
    Posts
    440
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Your code posted above was excellent and was just what I needed. Is there a way to tell whether a given DSN is a User, System or File DSN using API calls? Thanks!

  10. #20
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: API call to get List of ODBC DSNs

      
    Code:
    Option Explicit
    
    Private Declare Function SQLDataSources Lib "odbc32.dll" (ByVal hEnv As Long, ByVal fDirection As Integer, ByVal szDSN As String, ByVal cbDSNMax As Integer, pcbDSN As Integer, ByVal szDescription As String, ByVal cbDescriptionMax As Integer, pcbDescription As Integer) As Long
    Private Declare Function SQLAllocHandle Lib "odbc32.dll" (ByVal HandleType As Integer, ByVal InputHandle As Long, OutputHandlePtr As Long) As Long
    Private Declare Function SQLSetEnvAttr Lib "odbc32.dll" (ByVal EnvironmentHandle As Long, ByVal dwAttribute As Long, ByVal ValuePtr As Long, ByVal StringLen As Long) As Long
    Private Declare Function SQLFreeHandle Lib "odbc32.dll" (ByVal HandleType As Integer, ByVal Handle As Long) As Long
    
    Private Const DSN_LEN As Long = 32
    Private Const DESC_LENGTH As Long = 128
    Private Const FoundOk As Long = 0
    Private Const GetNext As Long = 1
    Private Const Null_HANDLE As Long = 0
    Private Const HANDLE_ENV As Long = 1
    Private Const ATTR_ODBC_VERSION As Long = 200
    Private Const OV_ODBC3 As Long = 3
    Private Const IS_INTEGER As Long = (-6)
    
    Sub Example()
        If DSNExists("MS Access Database") Then
            MsgBox "DSN Exists"
        Else
            MsgBox "DSN does not exist"
        End If
    End Sub
    
    Function DSNExists(DSN As String) As Boolean
    
     Dim hEnv As Long
     Dim sServer As String
     Dim sDriver As String
     Dim nSvrLen As Integer
     Dim nDvrLen As Integer
     
     If SQLAllocHandle(HANDLE_ENV, _
         Null_HANDLE, hEnv) <> 0 Then
        
         If SQLSetEnvAttr(hEnv, _
             ATTR_ODBC_VERSION, _
             OV_ODBC3, _
             IS_INTEGER) <> 0 Then
            
             sServer = Space$(DSN_LEN)
             sDriver = Space$(DESC_LENGTH)
            
             Do While SQLDataSources(hEnv, _
                 GetNext, _
                 sServer, _
                 DSN_LEN, _
                 nSvrLen, _
                 sDriver, _
                 DESC_LENGTH, _
                 nDvrLen) = FoundOk
                 If Trim(DSN) = Trim(Left$(sServer, nSvrLen)) Then
                    DSNExists = True
                    Exit Do
                 End If
                 sServer = Space$(DSN_LEN)
             Loop
         End If
         Call SQLFreeHandle(HANDLE_ENV, hEnv)
     End If
    End Function

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com