checking for a specific ODBC connection

Posted on 2008-11-14
Last Modified: 2012-05-05
I need a way to check for an existing ODBC connection in the odbc data source administrator.  All the research I have done indicates that the values are stored in either the INI file (not in XP) or the registry.  I have determined that the ini file does not exist and I do not have access to the registry on this computer so my search skills are limited here.  What I would like an easy way to see if the the connection named "tempconn" exists in the registry so that my code does not update the connection information when I use RegisterDatabase to establish the missing connection.  This is all being done on an xp system (soon to be vista) inside a ms access database.
Question by:masterjojobinks
    LVL 65

    Expert Comment

    What code are you currently using, r u using the sqlconfig... api? if so then could you not try to open as edit. If it failed then try to add on. If that fails then summat is knackered or it already exists!
    LVL 7

    Accepted Solution

    Seems like you would be able to read the registry if you can add the connection to the registry with the registerdatabase command.  The function will return true if connection name exists.
    Function ConnectionExists(sConnectionName As String) As Boolean
    Const HKEY_CURRENT_USER = &H80000001
    Dim objReg As Object
    Dim strComputer, strkeypath As String
    Dim arrValueNames As Variant
    Dim subkey As Variant
    strComputer = "."
    Set objReg = GetObject("winmgmts:\\" & strComputer & "\root\default:StdRegProv")
    strkeypath = "Software\ODBC\ODBC.ini"
    objReg.enumkey HKEY_CURRENT_USER, strkeypath, arrValueNames
    For Each subkey In arrValueNames
        If sConnectionName = subkey Then
            ConnectionExists = True
        End If
    End Function

    Open in new window

    LVL 48

    Expert Comment

    by:Gustav Brock
    > the ini file does not exist and I do not have access to the registry on this computer ..

    Then you are left with trial and error.
    Open a connection assuming your DSN exists. Catch an error.
    If no error is catched, the DSN exists.

    LVL 8

    Author Closing Comment

    That is just what I need.  Thanks

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now