Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303

    Unanswered: How to get the server from the DSN in the odbc?

    Good day,

    I'd like to get the name of the server that the backend of the current database is linking to.

    First attempt:
    Application.CurrentProject.Path
    Result:
    C:\_sandbox This is just the front end.

    Next attempt:
    DBEngine.Workspaces(0).Databases(0).TableDefs("Ord ers").Connect
    Result:
    ODBC;DSN=SMV8;DATABASE=SMV8 This tells me SMV8, but not the server that the SMV8 odbc dsn is using

    What I really want:
    some magic command
    Result:
    The name of the SQL server in my system DSN (SMV8)
    Last edited by jpshay; 12-03-07 at 10:56.
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    It's not a magic command, because it requires a little more work to get exactly what you want (string manipulation...) but hopefully it's the right one for you!
    Code:
    Application.CurrentProject.BaseConnectionString
    Let us know how you get on!
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    Thanks for the response. Looked good at first. Here is the actual output when I run your suggested code.

    PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:\_sandbox\SMv8.mdb;PERSIST SECURITY INFO=FALSE;Jet OLEDB:System database=\\Viking\ATL\SMV8Security\SMv8Security.MD W

    I don't know how to interpret this info.

    The DATA SOURCE is actually my front end.
    The System database is my MDW file.
    So neither of these really tell me where my back end database is at. Is that true?

    John
    Last edited by loquin; 12-04-07 at 13:54. Reason: disable smiles to properly display conenction string

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the double \\ usually indicates a computer name in a URL
    so Im guessing viking is your server
    you could try doing a split on \\ to retrieve the server name, or search for system database

  5. #5
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    I'm almost positive that the server information associated with a DSN does NOT exist anywhere in the MDB file. It resides within the DSN itself.

    Now, you have three types of DSN.
    • The first is the File DSN, where the ODBC administrator creates a portable, text-based format. (The file may be copied from one machine to another.) By default, on XP's, the File DSN ae stored in C:\Program Files\Common Files\ODBC\Data Sources. You may browse to a different location when creating one, though. Here is the text from a File DSN...
      Code:
      [ODBC]
      DRIVER=SQL Server
      UID=loquin
      Trusted_Connection=Yes
      DATABASE=Dictionary
      WSID=LOQUIN
      APP=Microsoft Data Access Components
      SERVER=SERVER-LAB
    • The second type of DSN is the User DSN. The user DSN data is stored in the Registry, at

      HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\

      (on XP...)
    • The third type is the System DSN. Like the User DSN, it too is stored in the registry, but at

      HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\

      (again, on XP)

      However, the only connection info I can find is in the last attachment - in the LastConnect folder. This appears to be an IP address, the server name, and port.


    I've added regedit screen clips of example user and system DSNs, below.

    So, the bottom line is that you'll need to add code to read the server (and database) information from the registry for user/system DSNs, or read/parse a text file for a file DSN.
    Attached Thumbnails Attached Thumbnails __USER_DSN.JPG   __SYSTEM_DSN.JPG   __SystemDSN_2.JPG  
    Last edited by loquin; 12-04-07 at 15:23.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  6. #6
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    Thanks healdem.
    That is a good suggestion as to how to parse the string and get the server where the MDW file is located.

    I believe loquin is addressing my real need. Many thanks loquin.

    Follow up Question. Can you please give me some VBA code that will report back the data for the registry thingamabobber?

    See attached picture.
    Attached Thumbnails Attached Thumbnails RegistryODBC.gif  
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ...follow up question should be in a separate thread, unless they are something that immediately follow on the original post........

    the reason is that some respondants keep track of what posts they looked at before.. they may have looked at that post and discounted (not interesting enough, no varied enough, don't think they can add to whats been said.. so they tend to ignore it.

    the bigger danger in this case is that you are now moving the goal posts and new viewers may not get to your problem, skimming through the first few posts.

    but the biggest danger is that a future user of the site may never find that this is to do with how to access registry stuff....

    so please new question new thread

  8. #8
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    Thanks healdem. Makes good sense.
    If I don't hear from loquin in a day or two, I will certainly follow your advice.

Posting Permissions

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