none
Retrieve SERVERNAME of ODBC connection in ms access in VBA RRS feed

  • Question

  • Greetings,

    I have many Access 2010 databases which have many ODBC linked SQL Server tables. Some table are linked to development server and some to production and I would like to get a report of which table is linked to which server by retrieving the name of the server from the user DSN through VBA code.I can get the database name from the connect property of the databases TableDefs collection but the SQL Server name is stored in the ODBC profile outside of the Access database.

    How can the DSN properties or its connection string be access through VBA?

    Thank You


    • Edited by pshirv Monday, September 21, 2015 10:20 PM
    Monday, September 21, 2015 9:45 PM

Answers

  • Hi Fei,

    Thank you for your reply.

    I checked my DSN and it does not have the server name in it. Here is the DSN:

    ODBC;DSN=MySQLSRVDB_DSN;Description=MYSQLSRVDB;UID=user;APP=2007 Microsoft Office system;DATABASE=MYSQLSRVDB;Trusted_Connection=Yes

    It is a user DSN and I found out that it is stored in the registry in the CURRENT_USER hive as show below:

    [HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\MySQLSRVDB_DSN]
    "Driver"="C:\\Windows\\SysWOW64\\sqlncli11.dll"
    "Description"="SQLSRVDB"
    "Server"="SQLDEV2"
    "Database"="MySQLSRVDB"
    "Trusted_Connection"="Yes"

    So what I ended up doing was to use a utility to read the registry keys and locate the DSN key and retrieved the Server value by doing a bunch of splits.

    Thursday, September 24, 2015 4:06 PM

All replies

  • Hi pshirv,

    What's the connection property that you got? Based on the test, the connection property contains the Sever Name. Here is the test connection property for your reference:

    ODBC;Description=SQLSERVERFei;DRIVER=SQL Server;SERVER=WXPFSER12-LAB1;Trusted_Connection=Yes;APP=Microsoft Office 2013;DATABASE=OfficeDevDB
    In addition, the DSN file is an text, to get the content of text file in VBA, we can use "Scripting.FileSystemObject" object. Here is an example that read the text by line:
    Sub ReadTextFile()
    Dim fs As Scripting.FileSystemObject
    Dim ts As TextStream
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set ts = fs.OpenTextFile("C:\Users\UserName\Documents\SQLSERVERFei.dsn", ForReading)
    While Not ts.AtEndOfLine
        Debug.Print ts.ReadLine
    Wend
    
    ts.Close
    End Sub
    To make the code above work, we need to add the reference for "Microsoft Scripting Runtime".

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, September 22, 2015 7:36 AM
    Moderator
  • Hi Fei,

    Thank you for your reply.

    I checked my DSN and it does not have the server name in it. Here is the DSN:

    ODBC;DSN=MySQLSRVDB_DSN;Description=MYSQLSRVDB;UID=user;APP=2007 Microsoft Office system;DATABASE=MYSQLSRVDB;Trusted_Connection=Yes

    It is a user DSN and I found out that it is stored in the registry in the CURRENT_USER hive as show below:

    [HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\MySQLSRVDB_DSN]
    "Driver"="C:\\Windows\\SysWOW64\\sqlncli11.dll"
    "Description"="SQLSRVDB"
    "Server"="SQLDEV2"
    "Database"="MySQLSRVDB"
    "Trusted_Connection"="Yes"

    So what I ended up doing was to use a utility to read the registry keys and locate the DSN key and retrieved the Server value by doing a bunch of splits.

    Thursday, September 24, 2015 4:06 PM